Tags: access, buttered, clear, database, error, excel, import, lock, microsoft, mysql, oracle, sometimesthings, spreadsheet, sql, table

How to clear lock on table if error 3211?

On Database » Microsoft Access

3,036 words with 4 Comments; publish: Mon, 31 Dec 2007 00:25:00 GMT; (25078.13, « »)

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.

All Comments

Leave a comment...

  • 4 Comments
    • 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.

      #1; Mon, 31 Dec 2007 00:26:00 GMT
    • > 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...

      #2; Mon, 31 Dec 2007 00:27:00 GMT
    • 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.

      #3; Mon, 31 Dec 2007 00:28:00 GMT
    • > 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

      #4; Mon, 31 Dec 2007 00:29:00 GMT