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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 |