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?
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