Adding Automuber Field with code

[Roy Goldhammer]

Hello there

I have table.

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

 

[onedaywhen]

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.

--

 

[Roy Goldhammer]

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?

 

[Roger Carlson]

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/wa.exe?SUBED1=ACCESS-L

 

[onedaywhen]

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.

--

 

[Terry]

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

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.

 

[Jamie Collins]

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.

--

 

[Terry]

Great stuff, thanks Jamie.
Regards

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.