Upgrading to SQL Server generates an IDENTITY col. causing trouble @ uploading.

[JGT]

Hi,

After upgrading the Access backend to a SQL Server backend, running an upload of the data from Access to SQL Server, 2 tables of the total of 72 tables reject the load because of an IDENTITY column and stay empty. Comparing with other tables, I cannot find any reason why this 2 tables were upgraded needing the IDENTITY column.
I am new to SQL, so anybody could give me a tip what could be the reason for this, so I can bypass it with some other solution?

Thank you for any help.

 

[SQL_Hell]

Indentity fields are created by the upsizing wizard because the access tables have integer /autonumber primary key on them.

A sql server integer identity field is the equivalent of the access autonumber, if you dont want the column to be an autonumber then remove the identity from the column in sql server.

 

[JGT]

Hi Hell,

Thank you for replying.
As a matter of fact, I normally use access tables with integer /auto number primary key on them, at least 50% of the tables have auto number PK. Therefore it still puzzles me why out of 72 upgraded tables only 2 of them come up with runtime error #3622 when uploading the tables. I do not find differences comparing tables.
Any idea?

When accessing the SQL tables in my upload routine, 2 of them create the error message:
"You must use the dbSeeChanges option with OpenRecordSet when accessing a SQL Server table that has an IDENTITY column."
The error message (miss)focused my attention to the Identity column. Reading your e-mail I started searching for dbSeeChanges on the internet, and found people that had exactly the same problem after upgrading, all over the place. It seems to be an oldie (2002). – The MS article that explains the way around the problem is: http://support.microsoft.com/kb/190620/en-us.

Thanks again,