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