views:

50

answers:

1

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

+1  A: 

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

Raj More
Your absolutely correct. Thank you very much for pointing it out! Now that it is working, it seems to be the most obvious thing, but without your helpful answer, I would never ever came to this solution.
Uwe Keim
I posted a couple of days ago explaining why this is so with Autonumbers: http://stackoverflow.com/questions/3366086/how-to-disable-all-autonum-in-access-2003/3374775#3374775 . It's a frequent source of confusion that is actually pretty clear when you stop to think about it.
David-W-Fenton