Different behavior of Identity Field inserts on different SQL instances.

[vbajock]

I am writing a VBA conversion program that is intended to convert legacy Access data to SQL tables. I have run into a problem with the Identity columns on the SQL target tables - when I want to refresh the new SQL tables with additional records entered from the live Access tables, SQL will not accept the Access-side autonumber fields as inserts into the new SQL tables. I have found the possible solution to this and will try it shortly, which is executing SET IDENTITY INSERT on/off commands, but in the process of playing with this I have discovered that my test server, which has instances of SQL 2000 and SQL 2008 running, will actually run the code and insert any number I wish into table Identity fields using the Access query interface, on either version. But then on the other hand, running the same exact query, the production server, which is running 2008 R2, rejects the inserts. Why the difference? Is there some sort of server wide or database wide property setting I can set to direct that all identity columns accept non-incremental values into the field? It would save me hours of coding if I could, any help appreciated.

 

[Qik3Coder]

Are you sure the test table still has the identity field as a PRIMARY Key with an IDENTITY attribute?
You can "roll open" the table and right click the properties of the ID column. Ensure the "Identity" is TRUE.

I only ask because we would sometimes remove the identities from the test environment to ease loading data from prod.

Lodlaiden

You've got questions and source code. We want both!
Here at tek tips, we provide a hand up, not a hand out.

 

[vbajock]

Yes, I've checked all that twice.

 

[Qik3Coder]

Are you using the "Access Interface" against the production machine, or just executing the query that you think it's running.

Lodlaiden

You've got questions and source code. We want both!
Here at tek tips, we provide a hand up, not a hand out.

 

[vbajock]

I'm executing everything thru Access.

 

[Qik3Coder]

Have you tried to refresh your prod db to your test 2k8 server to see if you still get prod like behavior. This will tell you if it's a server, or a db issue.
Are you using the same Access project, or a copy of it, configured for each environment?

Lodlaiden

You've got questions and source code. We want both!
Here at tek tips, we provide a hand up, not a hand out.