Database could not lock table - error

Question

Hi community,

I have a make table query and I need to run that query every time I click a button on a form. But when I try to run the query, Access throws me a "Database could not lock table.. " error. I'm pretty sure that table is present. But what I tried was I closed VBA, and tried to delete the table when the form was open. It threw me same error. If i close form, then i'm able to delete. I don't want to close form as I want to run query everytime button is clicked in the form. Please help me solve this!

Answer 1

Hey Scottgem, the problem is resolved. But the solution was a little weird though. So the table name had a hiphen - in it and access was searching for table name preceding the hiphen and not fully. I replaced that with an underscore and the problem got resolved. Is that a naming convention we ought to follow for Access?

Answer 2

Thanks for the clue. One of the form elements was indirectly referring to that table and I cleared that relationship. But when I try DoCmd.RunSQL "DELETE FROM tableName;" I get an error Runtime error 3078: Database cannot find input table or query. I'm pretty sure tableName is spelled properly. Any workaround for this?

Answer 3

Thanks for the clue. One of the form elements was indirectly referring to that table and I cleared that relationship. But when I try DoCmd.RunSQL "DELETE FROM tableName;" I get an error Runtime error 3078: Database cannot find input table or query. I'm pretty sure tableName is spelled properly. Any workaround for this?

Answer 4

Is the form bound to the table in any way? That would explain it.

Instead of a make table, why not delete all the records and then run an Append query?

Answer 5

You cannot delete a table which is referenced by a form's RecordSource property, either by name or as a component  of a query, while the form is open.  You will need to unshackle the table from the form by setting the form's RecordSource property to a zero-length string in the code, before executing a DELETE statement.

This is not uncommon where a table definition needs to be changed before running some routine, e.g. one which requires the dropping of an index, in which case the usual approach is to first assign the RecordSource property to a string variable, then drop the index.  Having executed the routine, the index is reinstated and the value of the variable is assigned to the form's RecordSource property.

It is unusual to delete a table in this context, however.  What is the rationale behind doing so?