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?
Who is Participating?
JimFiveConnect With a Mentor Commented:
I would expect that uncommitted data within your transaction should be visible regardless of the read uncommited

Are you sure that the lookup tables are the issue?  Do your other tables have relationships among themselves that require importation in a specific order?

I would suggest
Either:  Don't establish the relationships until all the data is imported
Or: Import the Lookup tables in one transaction (it doesn't matter if they are "orphaned", they are lookup tables) and then the rest in another transaction.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
smsizemoreAuthor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.