M
Michael Abraham
I have a number of linked tables in my MDB, pointing at a SQL 2005 DB. For
each of these tables, I also have an Access table with similar structure.
Each of the SQL 2005 tables has an identitiy column as its primary key.
I'm trying to load data from the Access tables into the SQL 2005 tables by
running an append query for each linked table, which appends the rows in
the corresponding Access table to the linked table. Since, the SQL 2005
tables have identity columns, Access needs to run the TSQL command SET
IDENTITY_INSERT ON <table_name> prior to running the inserts to append the
rows. This is being done correctly. My problem is that Access is not
running SET IDENTITY_INSERT OFF <table_name> after each append. Since SQL
2005 won't allow SET _IDENTITY_INSERT ON for more than one table at a time,
this means that only the first append query works. All subsequent append
queries fail with a key violation for every row.
I have worked around it by running a pass-thru query to SET_IDENTITY_INSERT
OFF between each append query. But it seems to me that there must be a
better way to do this (or else there is a bug/mal-design) in Access.
Has anyone seen this before? And is there a way to run multiple append
queries to load data into SQL 2005 tables with identity columns.
Thanks,
Mike
each of these tables, I also have an Access table with similar structure.
Each of the SQL 2005 tables has an identitiy column as its primary key.
I'm trying to load data from the Access tables into the SQL 2005 tables by
running an append query for each linked table, which appends the rows in
the corresponding Access table to the linked table. Since, the SQL 2005
tables have identity columns, Access needs to run the TSQL command SET
IDENTITY_INSERT ON <table_name> prior to running the inserts to append the
rows. This is being done correctly. My problem is that Access is not
running SET IDENTITY_INSERT OFF <table_name> after each append. Since SQL
2005 won't allow SET _IDENTITY_INSERT ON for more than one table at a time,
this means that only the first append query works. All subsequent append
queries fail with a key violation for every row.
I have worked around it by running a pass-thru query to SET_IDENTITY_INSERT
OFF between each append query. But it seems to me that there must be a
better way to do this (or else there is a bug/mal-design) in Access.
Has anyone seen this before? And is there a way to run multiple append
queries to load data into SQL 2005 tables with identity columns.
Thanks,
Mike