I have a DB with multiple import processes to take in information and populate various tables. This has been in place for some time and works with no issue.
The import process involves the creation of a local staging table, to which I import the received data, normalise it according to my own table structure, remove any duplication (i.e. same data imported repeatedly) and append the cleansed data to the main tables.
I've just added a validation step to check for data integrity. In other words, for duplicate data, rather than simply purge it as I was doing previously, I now check to see if the data has changed in any way and prompt the user if necessary (with the option to either ignore the change or update the existing record)
However, since I added this extra step, I am getting the following error message during my import process :
Error 3211 :
The database engine could not lock table 'tblStaging' because it is already in use by another person or process
It only appears a) when a data change is detected and b) when the option to update is chosen, and seems to occur after the update has taken place (i.e. I can see the records suitably updated)
But I can't replicate the error while debugging so it seems to be a run-time issue. It's definitely not another user as the staging table is deliberately local (specific to that user's front-end)
I suspect I can probably solve the problem by adding some kind of DoEvents call or brief pause somewhere but the problem is, I don't know where the problem is occuring, so I don't know where to add the line? Or if that would even solve the problem at all?
Is there an easy function I can call to check if the staging table is locked? Which I could then use to debug and identify exactly where the lock is causing a problem? Or even apply the DoEvents to release the lock? (It must eventually get released as if I step through the code, I don't get the error?)
Any and all suggestions appreciated!