By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,241 Members | 760 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,241 IT Pros & Developers. It's quick & easy.

How to clear lock on table if error 3211?

P: n/a
I'm trying to import an Excel Spreadsheet into an Access Table but sometimes
things get buttered up if the spreadsheet does not have the right columns.
So I want to drop the Access table and recreate it with a DDL query when
that happens. The problem is I get an error when trying to drop the table -
Error 3211: The database engine could not lock table 'tblExcelTx' because it
is already in use by another person or process.

Is there any way to clear the lock? All I want to do is blow away the
table...
Thanks in advance.
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
deko wrote:
I'm trying to import an Excel Spreadsheet into an Access Table but sometimes
things get buttered up if the spreadsheet does not have the right columns.
So I want to drop the Access table and recreate it with a DDL query when
that happens. The problem is I get an error when trying to drop the table -
Error 3211: The database engine could not lock table 'tblExcelTx' because it
is already in use by another person or process.

Is there any way to clear the lock? All I want to do is blow away the
table...
Thanks in advance.


CurrentDb.TableDefs.Delete "tablename"

But that still doesn't handle the problem since if the table is in use
somewhere it can't be deleted. Have you opened a recordset on the table
somewhere? If so, close the recordset first.

Nov 13 '05 #2

P: n/a
> CurrentDb.TableDefs.Delete "tablename"

But that still doesn't handle the problem since if the table is in use
somewhere it can't be deleted. Have you opened a recordset on the table
somewhere? If so, close the recordset first.


Thanks - I'll give it a shot. The reason the table is locked is because it
gets assigned as the recordsource for a subform. I try resetting with
recordsource = "" before dropping the table but no luck...
Nov 13 '05 #3

P: n/a
deko wrote:
CurrentDb.TableDefs.Delete "tablename"

But that still doesn't handle the problem since if the table is in use
somewhere it can't be deleted. Have you opened a recordset on the table
somewhere? If so, close the recordset first.

Thanks - I'll give it a shot. The reason the table is locked is because it
gets assigned as the recordsource for a subform. I try resetting with
recordsource = "" before dropping the table but no luck...


I'm not sure you can change the record source of a subform when it's
open. Good luck in your quest.
Nov 13 '05 #4

P: n/a
> I'm not sure you can change the record source of a subform when it's
open. Good luck in your quest.


I ended up closing and reopening the form in error handling code:

Exit_Here:
Exit Function
HandleErr:
Select Case Err.Number
Case 3211
DoCmd.Close acForm, "frmTools"
DoCmd.OpenForm "frmTools"
End Select
Resume Exit_Here
End Function

kind of a hack but it works
Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.