Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rating: Thread Rating: 4 votes, 5.00 average. Display Modes
Old 10-20-2014, 03:04 AM   #1
AOB
Newly Registered User
 
AOB's Avatar
 
Join Date: Sep 2012
Location: Dublin, Ireland
Posts: 551
Thanks: 132
Thanked 38 Times in 37 Posts
AOB is on a distinguished road
Error 3211 : The database engine could not lock table XXXX (AC2007)

Hi guys,

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 :

Quote:
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!

Thanks

Al

__________________
There are 10 types of people in this world
Those who understand binary and those who don't
AOB is offline   Reply With Quote
Old 10-20-2014, 03:38 AM   #2
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,460
Thanks: 51
Thanked 948 Times in 917 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Error 3211 : The database engine could not lock table XXXX (AC2007)

I presume it is what it says

the active user cannot undertake the process, because another user is doing something with the requested table


record locking is very hard to get right. Most of the time it is not really needed. Any locks should be retained for an absolute minimum time, and you need to guard against deadlock and similar issues.

I have to ask, though? why WOULD another user be using the staging table for a process? Is there in fact another user, or could it be yourself, with another process. That is what it sounds like it could be, actually, the way you describe the issue.

Are multiple users using a network copy of a database, or do they all have an individual copy.
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the top right of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 10-20-2014, 04:49 AM   #3
AOB
Newly Registered User
 
AOB's Avatar
 
Join Date: Sep 2012
Location: Dublin, Ireland
Posts: 551
Thanks: 132
Thanked 38 Times in 37 Posts
AOB is on a distinguished road
Re: Error 3211 : The database engine could not lock table XXXX (AC2007)

Hi Dave,

That's precisely what's confusing me!

It is a split DB with multiple users each having their own local FE and a single network BE. The staging table is local; it is created within the front end at runtime and is only available to that particular user. They can't actually see it, it is in the background. It is subsequently dropped once the import process has completed.

So it's definitely not another user; it has to be a process within the same FE. It's the same staging table that I've always used; the only difference now is that I've added an extra step to check the integrity of the new data, before deleting any duplicate information. (Previously, I just deleted the duplicates in the staging table and assumed the information in those duplicates was the same as before)

But with all processes running sequentially, there should be no reason for the table to be locked when one piece finishes and the next starts. I'm guessing that there is a brief overlap from one to another which causes the problem. Hence my thought that adding a DoEvents 'somewhere' could prevent any such brief lock from causing the whole process to halt?

__________________
There are 10 types of people in this world
Those who understand binary and those who don't
AOB is offline   Reply With Quote
Old 10-20-2014, 06:47 AM   #4
AOB
Newly Registered User
 
AOB's Avatar
 
Join Date: Sep 2012
Location: Dublin, Ireland
Posts: 551
Thanks: 132
Thanked 38 Times in 37 Posts
AOB is on a distinguished road
Re: Error 3211 : The database engine could not lock table XXXX (AC2007)

Solved, albeit in the loosest sense of the word...

I stuck in a DoEvents just after the dialog popup form advising the user of a data integrity issue returns to the module. To basically give the DB a chance to catch up on itself. The code now works at runtime without any error.

It must be imposing a lock on the temporary staging table and not quite relinquishing it before it attempts to delete the duplicates. Hence the problem only occurs at runtime and not during a debug.

Will mark as 'solved' but only because there's no option to mark it as 'damned if I know but I got it to stop'...
__________________
There are 10 types of people in this world
Those who understand binary and those who don't
AOB is offline   Reply With Quote
Old 10-20-2014, 08:23 AM   #5
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,460
Thanks: 51
Thanked 948 Times in 917 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Error 3211 : The database engine could not lock table XXXX (AC2007)

I would remove the locks. I am sure you don't need them.

The problem might be that locking the table prevents your other process reading the data from the same table. In practice, you don't need to worry about "readers". All you need to worry about is "writers".

Having no locks does not mean the data is unsafe -access by default uses "optimistic locking" which is adequate for 99.9% of situations.
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the top right of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 10-20-2014, 08:25 AM   #6
AOB
Newly Registered User
 
AOB's Avatar
 
Join Date: Sep 2012
Location: Dublin, Ireland
Posts: 551
Thanks: 132
Thanked 38 Times in 37 Posts
AOB is on a distinguished road
Re: Error 3211 : The database engine could not lock table XXXX (AC2007)

I don't impose the locks though?

I create linked tables (linked to locally saved text files) and use a SELECT INTO to create the staging table. I write the SQL in VBA and use a dbs.Execute to run the command. At no stage do I specify that the table should be locked?

__________________
There are 10 types of people in this world
Those who understand binary and those who don't
AOB is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
The database engine could not lock table ... mato61 Access Web 0 05-19-2014 05:29 AM
Opening a report: Error 3211: Database Engine could not lock table mor Forms 3 07-26-2013 10:43 PM
database engine could not lock table? darksniper Reports 2 05-30-2008 04:06 PM
Database engine could not lock table .... etc Error message bb99 Tables 1 02-15-2006 03:02 AM
Table lock - Run-time error 3211 ???? Pan Forms 9 08-03-2005 07:38 AM




All times are GMT -8. The time now is 05:31 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World