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.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Different behavior of Identity Field inserts on different SQL instances.
- Thread starter vbajock
- Start date
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.
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.
- Thread starter
- #3
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.
Lodlaiden
You've got questions and source code. We want both!
Here at tek tips, we provide a hand up, not a hand out.
- Thread starter
- #5
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.
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.
- Status
Similar threads
- Locked
- Question
- Question
