Info in Linked Tables doesn't refresh?

[DataMiner]

In Access 2002, I am running an append query in VB and getting the dreaded "runtime error 3022: The changes you requested were not successfull..." yada yada yada. When I try to run the same query outside of VB, it tells me I have Key Violation errors.

If I wait awhile and try running the same query, or just let my code sit in debug mode for awhile and come back and re-start it,
the query will run. Just now tried running the query outside of VB, and when it asked for confirmation I said no, went back into VB and it ran fine.

This query uses linked tables and my only guess is somehow the in the information in the linked tables is not refreshing properly. I have tried adding "requery" on all the pertinent tables just prior to running the query; no help.

Any suggestions????

Code looks something like this:

'add new and update existing records in Yields table:
DB.execute "yieldsupdate",dbfailonerror

'add new and update existing records in Repairs table:
DB.execute "repairsupdate",dbfailonerror

'Delete all existing records from RepairsNRD table
DB.execute "delete * from RepairsNRD;", dbfailonerror

'Repopulate RepairsNRD with new data from Repairs table

DB.execute "Insert into RepairsNRD select Repairs.* from Repairs",dbfailonerror

'append some records from Repairs and Yields to RepairsNRD
'This is where I get the Key Violation Error:

Db.Execute "RepairsAddNRD", dbfailonerror

 

[ritchieroo]

The insert that raised the error in the first place is probably not rolled back until you come out of debug mode (you may even have to close the application).

Try re-writing your code within a transaction, include error handling that rolls back the transaction appropriately.