How to clear lock on table if error 3211?

[deko]

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.

[Salad]

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.

[deko]

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

[Salad]

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.

[deko]

> 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