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?
smsizemoreAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JimFiveCommented:
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.
--
JimFive
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
0
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.