469,366 Members | 2,322 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,366 developers. It's quick & easy.

How to clear lock on table if error 3211?

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
4 38125
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
> 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
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
> 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.

Similar topics

1 post views Thread by leecho | last post: by
1 post views Thread by Sandy | last post: by
3 posts views Thread by Frederick Tant | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.