Error 3211 Object cannot be locked

[TheShabz]

Hi all,

On my main form I have a subform that is bound to a query that SELECT * from a table. On the main form, I have a button which triggers a series of queries, one of which reconstructs the table in aforementioned query. This is where the issue occurs. There is something in my setup that is locking it.

Things that I believe are locking it:
The subform - so I closed it before running that query


The main form - same as above
some recordset I opened - I do have subs that create a recordset clone of the subform in order to do some calculations but they are not part of this sub. And even then, I close all recordsets and set rs = nothing.

I attempted to run the query outside of the sub to verify it's not something wrong with my code. Same error there.

Any hints would be helpful.

[ajetrumpet]

Queries that are not being *used* do not affect table reconstruction. Scenarios that constitute a query that is being *used*:

--The query is needed for a form object (subform)
--Query is open

What do you mean by "reconstructs it"? You can do just about everything to table data possible when the table is opened by another object, but you cannot manipulate the definition of the table. That means stuff like:

--Add/Del Columns
--Del Table
--etc...

try compacting it first.

[TheShabz]

By reconstruct I mean run a make table query to update the table with new info from a weekly data refresh.

Let me try to rephrase. I have a Form. This form has a subform which is bound to a query that displays the data from TableA. On the form are many textboxes which run calculations based on a recordset clone of the subform. On the form is a button which updates everything after the weekly refresh. One of the queries this button triggers is the query that creates TableA.

With the subform being bound to a query that displays the contents of TableA, I understand that I cannot run the query to remake ("reconstruct" per my OP). However, I close the form before that query runs. I am stumped as to why Access still errors out saying it cannot lock the table.

Hope that makes more sense.

[ajetrumpet]

However, I close the form before that query runs. I am stumped as to why Access still errors out saying it cannot lock the table.

Then you have one of 2 issues, most likely:

1) The code that closes the form is not running fast enough. e.g. - it's not complete and doesn't release the bound table fast enough before you get to the "make table" code. That's very possible. I've run into it many times. The immediate workaround is to add the DoEvents code after the ''close form'' code. That takes time, but not much. It'll give extra milliseconds for you though. Similar to Pause() in PHP and Wait() in other languages.

2) There is something else locking it, which makes closing the form irrelevant.

[TheShabz]

1) There is a good 2 mins worth of queries that happen before the query in question triggers.

2) After closing the form, the only thing open is a progress form which is in no way tied to any table.

[ajetrumpet]

then I would say the issue is #1.

[TheShabz]

So it takes over 2 mins to release the table? That cant be right.

[ajetrumpet]

So it takes over 2 mins to release the table? That cant be right.

NO DUDE. Recall that you said this:

However, I close the form before that query runs.

tried the "DoEvents" solution yet? Like I've been preaching for 5 posts now?

[TheShabz]

i would have had i still been at work. It'll wait til tomorrow.

[TheShabz]

Did not work.