Keep value of autonumber column when importing into Microsoft Access database

Question

What I try is to import several tables programmatically from Microsoft SQL Server to Microsoft Access.

Each SQL Server table has an identity column, and the corresponding Access tables, an autonumber column, too.

Now I want to generate SQL scripts to copy the data from SQL Server to Access and have the autonumber colum the same value as in SQL server.

Is this possible?

When doing it the other way from Access to SQL Server, it is rather easy by using SET IDENTITY_INSERT [MyTable] ON and later SET IDENTITY_INSERT [MyTable] OFF.

I found out that there is no such statement for Microsoft Access.

In addition I tried to create the Access tables to import into first with the identity field as type LONG and later use the ALTER TABLE ... ALTER COLUMN statement to switch to autonumber. I failed in doing so.

So my question: Is there any way to achieve my goal?

Answer 1

If you use Insert Into and specify all column names in MS Access, it should work.

I just created a table with the following structure

Id (autonumber)
Firstname (text)
Secondname (text)
Lastname (text)

I ran this statement

docmd.RunSQL "insert into table2 (id, firstname, secondname, lastname) values (27, 'a', 'b', 'c')"

It worked and inserted 27 into the autonumber column