Error: 3211, the database engine could not lock table... (1 Viewer)
- Thread starter daryll
- Start date
- Local time
- Today, 10:08
- Joined
- Sep 12, 2006
- Messages
- 15,638
I have been seeing the error you report in unexpected places. There should be no users with access settings other than "no record locks" - ie optimistic locking, and yet we see the error. I enquired about this a couple of weeks ago, without getting an explanation. So I can't help, but I would like to know the answer
- Local time
- Today, 04:08
- Joined
- Feb 28, 2001
- Messages
- 27,140
We can't answer that directly - but can tell you how to determine that answer. Among the properties of a report are two places to look.
On the property sheet for the report, "Other" tab, check the lock setting. "No locks" should be chosen for any report.
The next place to look is the .Recordsource, which involves an expressed or implied query. The query can also have a lock setting (again on ITS property sheet). Again, for reports and queries that drive reports, you should choose "No locks."
One possibility is if you have any VBA code behind the scenes of the report and that code attempts in ANY WAY to update, insert, or delete information in any table. It happens sometimes, like when people want to track report usage in a logging table. Be sure to use minimum locking on any such updates.
A question also comes to mind: Is this a shared database for which another person might be using the back end file? If so, the other person might be holding the lock.
If it is a standalone single-user DB, is there something else that is open at the time? (Since you are using VBA to do this, actually there has to be something open...) Could that something else be doing the locking?
If none of those help, come back.
On the property sheet for the report, "Other" tab, check the lock setting. "No locks" should be chosen for any report.
The next place to look is the .Recordsource, which involves an expressed or implied query. The query can also have a lock setting (again on ITS property sheet). Again, for reports and queries that drive reports, you should choose "No locks."
One possibility is if you have any VBA code behind the scenes of the report and that code attempts in ANY WAY to update, insert, or delete information in any table. It happens sometimes, like when people want to track report usage in a logging table. Be sure to use minimum locking on any such updates.
A question also comes to mind: Is this a shared database for which another person might be using the back end file? If so, the other person might be holding the lock.
If it is a standalone single-user DB, is there something else that is open at the time? (Since you are using VBA to do this, actually there has to be something open...) Could that something else be doing the locking?
If none of those help, come back.
The locking happens only when I am trying to execute DoCmd.OutputTo. Opening and closing the report doesn't raise this Error. It dropped the table successfully.
I am the only user of this table. It is still under develop.
"No locks" is the default setting.
- Local time
- Today, 04:08
- Joined
- Feb 28, 2001
- Messages
- 27,140
After doing a web search on this, I see the problem with DoCmd.OutputTo locking something as far back as 2015 (might be more, but I stopped searching after seeing a dozen such posts over the last 5+ years.)
The effect is extreme enough that it sometimes even prevents the app from exiting. That extreme case can ONLY occur if for some reason the system thinks the file is still open (and thus situationally locked) such that it cannot complete process rundown. Therefore, I have to think there is a serious timing bug in file locking following an OutputTo operation.
I don't see where anyone ever actually found a solution for this, but I'll suggest as an experiment that you insert a DoEvents after the report output and see if that lets the system move forward. This is a "shot in the dark" kind of solution. I make no claim to KNOW that it works. But it has a chance and is a simple experiment that is easily undone.
The effect is extreme enough that it sometimes even prevents the app from exiting. That extreme case can ONLY occur if for some reason the system thinks the file is still open (and thus situationally locked) such that it cannot complete process rundown. Therefore, I have to think there is a serious timing bug in file locking following an OutputTo operation.
I don't see where anyone ever actually found a solution for this, but I'll suggest as an experiment that you insert a DoEvents after the report output and see if that lets the system move forward. This is a "shot in the dark" kind of solution. I make no claim to KNOW that it works. But it has a chance and is a simple experiment that is easily undone.
I'm not sure how the DoEvents could unlock the table. I already tried inserting the DoEvents after the DoCmd.OutputTo line, nothing changed. DoCmd.OutputTo command still holding the table. I've seen some suggestion from the net as what you've suggested, neither it solved their problem.
- Local time
- Today, 04:08
- Joined
- Feb 28, 2001
- Messages
- 27,140
At the moment, I'm drawing a blank. Perhaps one of my colleagues has seen this before and can offer some advice.
The only thing that I remember seeing in the articles I searched was a reconfiguration of the code to somehow defer the action that ran into the lock.
As to how "DoEvents" could unlock a table... it can't. But if the problem was a timing issue, DoEvents would give the system time to finish whatever had been running into a timing issue. Not that DoEvents does anything to the table. But it would let something else do something to the table.
The only thing that I remember seeing in the articles I searched was a reconfiguration of the code to somehow defer the action that ran into the lock.
As to how "DoEvents" could unlock a table... it can't. But if the problem was a timing issue, DoEvents would give the system time to finish whatever had been running into a timing issue. Not that DoEvents does anything to the table. But it would let something else do something to the table.
I've attached a test database.
It contains 2 tables and 1 report.
Open and close the report, table1 should be dropped. Rename the 2nd table to "TABLE1" and open the report, this time click that button to export the report (to excel). Close the report and you will be greeted by ERROR3211.
It would appear that the problem is that the outputto is placing a hold on the table. If you raise then dismiss the error (end) and close the db, there is a warning that this action will empty the clipboard. I've never heard of the clipboard placing a lock on a table, but I'd say either the output file (which I can't find) or the clipboard is responsible. This happens even if I set db option to "no locks", close it reopen and try again. I also attempted to do all this from a form, thinking that if the report could close first, the table could be dropped but that didn't work, which reinforces my belief that the output process is responsible. One might think that the obvious answer is to not continually delete the table.\; rather, flush it and repopulate. However I bet you'd get a similar error. I'll continue to play around a bit, but so far no luck.
If it can't be determined what exactly is locking the table (clipboard or outputto process) and you must drop the table then my current thinking is
a) try basing the report on a recordset which is something that you can close and then attempt the drop at the end of the process
a) II) not sure if ado or dao. ADO would also allow you to create a connection to the table which can also be closed before attempting the drop
b) drop the table then recreate it. I presume you've just left out the part that creates the table. Not sure when the create will run next time as currently, the only thing that seems to cut the hold is closing the db, so maybe that won't work for you.
c) base the report on a query rather than a table. I presume the query will unload/close as the report unloads, perhaps thereby dropping the lock
I suspect none of that will work if the clipboard or outputto is placing the hold. Any post I've found that deals with this exact issue was never solved. If you solve it, please post your solution.
a) try basing the report on a recordset which is something that you can close and then attempt the drop at the end of the process
a) II) not sure if ado or dao. ADO would also allow you to create a connection to the table which can also be closed before attempting the drop
b) drop the table then recreate it. I presume you've just left out the part that creates the table. Not sure when the create will run next time as currently, the only thing that seems to cut the hold is closing the db, so maybe that won't work for you.
c) base the report on a query rather than a table. I presume the query will unload/close as the report unloads, perhaps thereby dropping the lock
I suspect none of that will work if the clipboard or outputto is placing the hold. Any post I've found that deals with this exact issue was never solved. If you solve it, please post your solution.
Move the entire process to a form click button and it works but the report won't remain open even if it's modal property is set to True, which I find surprising. If you need to see the report, then I guess you'd have to split the process up, possibly as in suggestion b) above
Note that whatever testing you do it seems that you must close the db when the error is generated before trying anything new, otherwise the lock will remain even if you would solve the problem. Here's what I tried that works (form command button click)
Note that whatever testing you do it seems that you must close the db when the error is generated before trying anything new, otherwise the lock will remain even if you would solve the problem. Here's what I tried that works (form command button click)
Code:
Private Sub Command0_Click()
DoCmd.OpenReport "report1", acViewReport
'DoEvents 'doesn't help
DoCmd.Close acReport, "report1"
DoCmd.RunSQL ("DROP TABLE TABLE1")
End SubI doubt it would work. I guess you forgot to mention where the DoCmd.OutputTo should took place.
Last edited:
I can't find any solution to release the locking. But somehow, I found an alternative which is equivalent to "DoCmd.OutputTo".
As of now, "DoCmd.TransferSpreadsheet" will do the job. I can drop the table now after exporting the report.
As of now, "DoCmd.TransferSpreadsheet" will do the job. I can drop the table now after exporting the report.
Last edited:
Sorry about that. And I was completely sober too. At least I learned something from this - we can add our names to the list of those who had the exact same issue and never found a solution for the outputto method.
