smsizemore
asked on
Are there limitations on MS Access 2003 transaction isolation levels?
I am attempting to programatically import up to 8 different tables from one access database into another. The tables include lookup, "types" tables with established relationships to the other tables. I am importing the types tables before the other tables so that they will be onfile. However, I want to wrap the entire import in a transaction, so if something goes wrong, then there won't be any orphaned data on the systeem. I'm using ADODB connections and commands and have tried all the variou isolation levels, including adXactReadUncommitted. However, I consistantly receive the following error:
"Error # - 2147467259: You cannot add or change a record because a related record is required in table..."
However, I add the related records from the related table first, without error, but they are not visible when adding the dependent records. I though read uncommited would make the uncommited records from the same transaction available to subsequent records in the transaction. Is this a shortcoming of access? Should I attempt to use DAO instead of ADO?
"Error # - 2147467259: You cannot add or change a record because a related record is required in table..."
However, I add the related records from the related table first, without error, but they are not visible when adding the dependent records. I though read uncommited would make the uncommited records from the same transaction available to subsequent records in the transaction. Is this a shortcoming of access? Should I attempt to use DAO instead of ADO?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Scott McDaniel (EE MVE )
When importing, are you SURE that you're keeping the correct Primary Key values in the lookup tables? If you're using Autonumbers and you're not specifically writing those Autonumbers to the newly imported tables (to maintain the relationship) then your dependent tables won't be correctly related.
ASKER
Although the answer is not 100% on, it got me looking at the relationships. The table everything is imported into is soley as a repository ofr data for data mining. The base database that was used as the repository had "Enforce Referencial Integrity" checked, although there is no data entry as such. By unchecking that, my transaction worked. Thanks for seeding my mind.