A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Adding Automuber Field with code



 
 
Thread Tools Display Modes
  #1  
Old February 22nd, 2005, 09:06 AM
Roy Goldhammer
external usenet poster
 
Posts: n/a
Default Adding Automuber Field with code

Hello there

I have table.

Is there a way to add auto number field to table with vb code?


  #2  
Old February 22nd, 2005, 10:42 AM
onedaywhen
external usenet poster
 
Posts: n/a
Default


Roy Goldhammer wrote:
Is there a way to add auto number field to table with vb code?


CurrentProject.Connection.Execute _
"ALTER TABLE MyTable ADD MyNewCol INTEGER IDENTITY(1,1);"

Jamie.

--

  #3  
Old February 22nd, 2005, 01:51 PM
Roy Goldhammer
external usenet poster
 
Posts: n/a
Default

Thankes Jamie

It works.

The reason i need it is for importing data from excel. In the import from
excel wizart there is a way to add key field (an autonumber)

Is there a way to do this with code?


"onedaywhen" wrote in message
ups.com...

Roy Goldhammer wrote:
Is there a way to add auto number field to table with vb code?


CurrentProject.Connection.Execute _
"ALTER TABLE MyTable ADD MyNewCol INTEGER IDENTITY(1,1);"

Jamie.

--



  #4  
Old February 22nd, 2005, 02:32 PM
Roger Carlson
external usenet poster
 
Posts: n/a
Default

That IS code. It is VB running an SQL statement.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L

"Roy Goldhammer" wrote in message
...
Thankes Jamie

It works.

The reason i need it is for importing data from excel. In the import from
excel wizart there is a way to add key field (an autonumber)

Is there a way to do this with code?


"onedaywhen" wrote in message
ups.com...

Roy Goldhammer wrote:
Is there a way to add auto number field to table with vb code?


CurrentProject.Connection.Execute _
"ALTER TABLE MyTable ADD MyNewCol INTEGER IDENTITY(1,1);"

Jamie.

--





  #5  
Old February 22nd, 2005, 05:23 PM
onedaywhen
external usenet poster
 
Posts: n/a
Default

Roy Goldhammer wrote:
The reason i need it is for importing data from excel. In the import

from
excel wizart there is a way to add key field (an autonumber)

Is there a way to do this with code?


With CurrentProject.Connection
.Execute _
"SELECT *" & _
" INTO MyNewTable" & _
" FROM [Excel 8.0;HDR=YES;IMEX=1;Database=C:\db.xls].[Sheet1$];"
.Execute _
"ALTER TABLE MyNewTable ADD MyNewCol" & _
" INTEGER IDENTITY(1,1);"
End With

Jamie.

--

  #6  
Old February 25th, 2005, 12:17 PM
Terry
external usenet poster
 
Posts: n/a
Default

Interesting topic. Assuming that the Excel import already has a unique key
what would be the SQL to change that imported column to be the primary key
within the Access table?
Regards

"onedaywhen" wrote in message
oups.com...
Roy Goldhammer wrote:
The reason i need it is for importing data from excel. In the import

from
excel wizart there is a way to add key field (an autonumber)

Is there a way to do this with code?


With CurrentProject.Connection
.Execute _
"SELECT *" & _
" INTO MyNewTable" & _
" FROM [Excel 8.0;HDR=YES;IMEX=1;Database=C:\db.xls].[Sheet1$];"
.Execute _
"ALTER TABLE MyNewTable ADD MyNewCol" & _
" INTEGER IDENTITY(1,1);"
End With

Jamie.

--



  #7  
Old February 25th, 2005, 04:03 PM
Jamie Collins
external usenet poster
 
Posts: n/a
Default


Terry wrote:
Assuming that the Excel import already has a unique key
what would be the SQL to change that imported column to be the

primary key
within the Access table?


With CurrentProject.Connection
.Execute _
"SELECT key_col, data_col" & _
" INTO MyNewTable" & _
" FROM [Excel 8.0;HDR=YES;IMEX=1;Database=C:\db.xls].[Sheet1$];"
.Execute _
"ALTER TABLE MyNewTable ADD CONSTRAINT pk__mynewtable" & _
" PRIMARY KEY (key_col);"
End With

Jamie.

--

  #8  
Old February 28th, 2005, 11:06 AM
Terry
external usenet poster
 
Posts: n/a
Default

Great stuff, thanks Jamie.
Regards

"Jamie Collins" wrote in message
ups.com...

Terry wrote:
Assuming that the Excel import already has a unique key
what would be the SQL to change that imported column to be the

primary key
within the Access table?


With CurrentProject.Connection
.Execute _
"SELECT key_col, data_col" & _
" INTO MyNewTable" & _
" FROM [Excel 8.0;HDR=YES;IMEX=1;Database=C:\db.xls].[Sheet1$];"
.Execute _
"ALTER TABLE MyNewTable ADD CONSTRAINT pk__mynewtable" & _
" PRIMARY KEY (key_col);"
End With

Jamie.

--



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding field - error "More than one field...." Kevryl Setting Up & Running Reports 1 December 17th, 2004 03:19 AM
How do I use the PRINT field code to ... Dave General Discussion 3 December 13th, 2004 04:06 PM
How do you move/jump to the next field code in a template MelissaF General Discussion 4 November 17th, 2004 12:03 AM
How to phrase If/Then clauses in the control source field Database Design 13 August 14th, 2004 04:22 PM
Radio Field always assigns area code (Causes error with Synching to Blackberry) Jonathan Contacts 1 May 27th, 2004 11:50 PM


All times are GMT +1. The time now is 04:10 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.