ansaurus

Question

table locked or in use when calling RunSQL

Answer 1

A:

I have seen this when you re-open a database after Access has crashed. Typically for me a reboot has fixed this.

Nate Bross 2010-10-27 15:29:39

I've tried restarting, but the problem hasn't gone away :(

Tim 2010-10-27 15:46:05

Answer 2

A:

What version of MSAccess? Not sure about newer ones, but for Access 2003 and previous, if you were sure nobody was in the database, you could clear up locks after a crash by deleting the .ldb file.

Bill 2010-10-27 15:31:32

It's access 2007. I've tried deleted the .ldb but as soon as I open the original .accdb again I get the same problem.

Tim 2010-10-27 15:45:38

Answer 3

+4 A:

Is MasterTable included in your form's Record Source? If so, you can't replace it, or modify its structure, while the form is open.

Apart from the table lock issue, there is a logic error in the SELECT statement.

Where 'ClassName' = '7A'

The string, ClassName, will never be equal to the string, 7A. Therefore your SELECT can never return any records. If ClassName is the name of a field in your Year07 table, discard the quotes which surround the field name.

Where ClassName = '7A'

HansUp 2010-10-27 15:54:14

Yes the MasterTable is in the form's record source. Do you know how I can get around this without having to close the form, perform the action and then re-open the form? I've also changed the 'ClassName' issue, although in an SQL datasheet view it didn't make a different. Thanks again :)

Tim 2010-10-27 16:36:59

My best guess is you should substitute `Select * From Year07 Where ClassName = '7A'` wherever MasterTable appears in your form's Record Source. IOW, you may not even need MasterTable as an actual table. But that is only my guess. You may get a better guess if you edit your question to show us the Record Source.

HansUp 2010-10-27 17:54:37

The idea is to have many tables containing different data but to only display one at a time (chosen by a combo box). When the combo box is changed, the values in the MasterTable change to show only what is needed. I wasn't sure in access how to do this properly, but know now. Thanks! :)

Tim 2010-10-27 19:15:46

Huh. I misread this answer yesterday -- Hans says the same thing I do in his first paragraph and then quickly moves onto something else. In reading, the first part didn't register for me, or I never would have posted my own answer. +1

David-W-Fenton 2010-10-28 22:43:44

You said it better, David ... especially the point that Make Table queries don't belong in production applications.

HansUp 2010-10-28 23:12:22

Answer 4

+3 A:

I'm guessing, but if you're using a form that is bound to MasterTable, you can't run a query to replace it with a new MasterTable while you've got it open in the form.

I would suggest that you get rid of the MakeTable query (SELECT INTO) and instead use a plain append query (INSERT). You'll want to clean out the old data before appending the new, though.

Basically, a MakeTable query is, in my opinion, something that does not belong in a production app, and any process that you've automated with a MakeTable query should be replaced instead with a persistent temp table that is cleared before the new data is appended to it.

David-W-Fenton 2010-10-27 21:18:20

+1 "a MakeTable query is, in my opinion, something that does not belong in a production app"

onedaywhen 2010-10-28 08:13:15

ansaurus

tags:

views:

answers:

table locked or in use when calling RunSQL

related questions