Table Lock Issue

Question

Table Lock Issue

Tekla

SSC Rookie

Points: 36
More actions
March 20, 2006 at 1:54 pm

#68775
Hi folks,
We have been developing a reports interface in Access 2000. We are having a table lock issue when a user runs a report that is created by temporary tables (Make Table) and another user has that report open.  We can't seem to find a way to resolve this. "Record locking" is set to No on the form and the report properties.  The Options are set to "No locks" for default record locking as well as the Default open mode is set to "Shared".
Here's the error:
The database engine could not lock table 'Read_Function_Assessments_Make_Table' because it is already in use by another person or process
Here's the code:
'=====================================================
Private Sub cmdRunReport_Click()
On Error GoTo Err_cmdRunReport_Click

Dim stDocName, stDocName1, stDocName2, stDocName3, stDocName4, stDocName5, stDocName6, stDocName7 As String

    stDocName = "Read_Function_Assessments_Make_Table_qry"
    stDocName1 = "Read_Function_Work_Items_Make_Table_qry"
    stDocName2 = "Read_Function_rpt"
    stDocName3 = "Read_Function_Change_Pkg_Make_Table_qry"
    stDocName4 = "Read_Function_Form"
    stDocName5 = "Read_Function_Boards_Make_Table_qry"
    stDocName6 = "Read_Function_References_Make_Table_qry"
    stDocName7 = "Read_Function_Assessments_MnHrs_Non-Labor_Make_Table_qry"

    DoCmd.SetWarnings False
If IsNull(Me.cboDocType) Then
    MsgBox "Document Type is required!"
    Me.cboDocType.SetFocus
ElseIf IsNull(Me.cboDocNumber) Then
    MsgBox "Document Number is required!"
    Me.cboDocNumber.SetFocus
Else
    DoCmd.OpenQuery stDocName, acViewNormal
    DoCmd.OpenQuery stDocName1, acViewNormal
    DoCmd.OpenQuery stDocName3, acViewNormal
    DoCmd.OpenQuery stDocName5, acViewNormal
    DoCmd.OpenQuery stDocName6, acViewNormal
    DoCmd.OpenQuery stDocName7, acViewNormal
    DoCmd.OpenReport stDocName2, acViewPreview
    DoCmd.SetWarnings True
End If
Exit_cmdRunReport_Click:
    Exit Sub
Err_cmdRunReport_Click:
    MsgBox Err.Description
    Resume Exit_cmdRunReport_Click
End Sub
'=====================================================
We've looked at:
- DoCmd.Close acTable
- Drop Table
- DAO, etc.
Nothing seems to work. Got any ideas?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Metra Christofferson Ten Centuries Points: 1153 More actions · Answer 1

Have you thought about adding a user ID to the table name and passing the tablename to the report as a parameter? So Joe would have a table named 'Read_Function_Assessments_Make_Table_Joe' and Marie would have 'Read_Function_Assessments_Make_Table_Marie' and then deleting the table when you're done with it?

Brian Ford Newbie Points: 9 More actions · Answer 2

The issue isn't one of record locking but of table locking. If one user has the table open and a second user is trying to run a make table query, the query will attempt to delete the existing table and replace it with one of the same name. Because the table to be deleted is open the operation will fail. You can't delete an open table. Metro's solution of creating customized table names will work, especially if your database is a single file that everyone is running from a server.

An alternative is to split the database into front-end back-end. Each user has a copy of the front end on their PC linked to the back end on a server. The report table then gets created in the front end so there is no conflict. Aside from resolving this problem, there are other advantages to having the data separate from your presentation layer.

Good Luck.

Tekla SSC Rookie Points: 36 More actions · Answer 3

Brian,

Thanks for the response. Believe it or not, we did split the database and still got this problem.

John Laska SSC-Addicted Points: 454 More actions · Answer 4

Ok, I'm a rather weak on Access, but since we seem to just be throwing out ideas:

What is the text of the "Read_Function_Assessments_Make_Table_qry". At a guess, this is probably opening a table to read data from to populate the new temp table. Assuming that this is true, and not being 100% sure how the transactions are happening, it might be that you are actually throwing the error on the table you are reading to get the data from. e.g.:

Transaction begins
  Read_Function_Assessments_Make_Table_qry starts
    Sub transaction begins
      Data is read (locking the primary table)
  Next step here
  Step3
    User sits here reading report for a while
  Step4
All transactions closed (realeasing all locks)

Make sure that in your sub queries that each explicitly starts and commits each transaction, as this should release all locks.

Dick Schroth Right there with Babe Points: 725 More actions · Answer 5

Did you make sure that when you split the database all the temporary tables were in the front end rather than the back end (where data would normally be)?

Dick

Tekla SSC Rookie Points: 36 More actions · Answer 6

We did several scenarios with this. The database has always been split. Initially, all the temporary tables were created in the front end. The table lock problem occurrs when the second user tries to run the same report. So we decided to try putting permanent tables in the backend that were populated with the appropriate data for running the report. We still got the table lock problem.

It just doesn't seem logical.

I even tried transactional processing:

BeginTrans
If IsNull(Me.cboDocType) Then
    MsgBox "Document Type is required!"
    Me.cboDocType.SetFocus
ElseIf IsNull(Me.cboDocNumber) Then
    MsgBox "Document Number is required!"
    Me.cboDocNumber.SetFocus
Else
    DoCmd.OpenQuery stDocName, acViewNormal
    DoCmd.OpenQuery stDocName1, acViewNormal
    DoCmd.OpenQuery stDocName3, acViewNormal
    DoCmd.OpenQuery stDocName5, acViewNormal
    DoCmd.OpenQuery stDocName6, acViewNormal
    DoCmd.OpenQuery stDocName7, acViewNormal
    DoCmd.OpenReport stDocName2, acViewPreview
    DoCmd.SetWarnings True
CommitTrans
End
End If

Still get table lock.

Charles Wannall Ten Centuries Points: 1003 More actions · Answer 7

If you're using Make Table to gather your report data, your only solution is to be sure that each user has a copy of the front end and that the temp table is being created in the local copy.

There is another way, that lets you keep the data table anywhere you want it, and still run reports for any number of users at the same time.

Get your table made once, manually.

Add a UserName column to the result table(s).

Perform all inserts or updates using qualifier "Where UserName = '" & CurrentUser & "'" (YOu may have to supply a replacement for CurrentUser if you are not using secured database. Have your own login form, for example, and keep the username available in a global variable or on a control on a hidden form, etc)

Change the reports to select, also using "Where UserName = '" & CurrentUser & "'"

We have big work tables constantly in flux by any number of users and this approach accommodates everything, while allowing us to keep a single copy of the application.

Metra Christofferson Ten Centuries Points: 1153 More actions · Answer 8

On the structure of your report, are ALL required tables temporary, or are you using some permanent ones?

If you are using some permanent ones, have you tried making temporary tables populated with the permanent data needed by the report, so ALL tables required by the report are temporary? And put all temp tables back on the front end? That way, the report is completely based in the front end and NOTHING is in use from the back end. Then, as long as each user is using a completely different front end (no simultaneous use of the same front end mdb file), you shouldn't have the locking problem.

You are using one copy of the front end per user, right?

Tekla SSC Rookie Points: 36 More actions · Answer 9

I like the username solution. That should fit our multiuser environment better (300 users). Thanks for the code example for a non-secured database. Not sure if we'll use it for this, but in case I ever do a small scale one, that sure comes in handy.

Charles Wannall Ten Centuries Points: 1003 More actions · Answer 10

A word of followup on the username solution: Expect the MDB or MDE that contains the work table to bulk up. It's not a problem, just the cost of doing business. Plan to compact the application regularly.

This may not apply if you regularly replace the single copy of the app with a new version, as we do.

Here are our numbers: The released app is a 35 MB MDE file. After a week or so it could get above 250 or 300 MB. We see no performance hit, even if it gets bigger, so for us compacting is more a matter of doing the right thing to prevent having a problem than of dealing with a problem.

Practically speaking, though, we rarely let the app sit long enough to reach even 300 MB. We release at least one or two updates a week. (Lots of users, lots of ideas, and even a few bugs, sad to say.) As a result, the size is regularly set back to the size of the release (about 35 MB) and the file rarely has a long enough service life to get to a problematic size.

Thought you might want to see this info since you may go with the username approach.