Solved

The database engine could not lock table 'xxxxx' because it is alreadyin use by another person or process

Posted on 2004-09-10
11
361 Views
Last Modified: 2008-02-01
I have a button on a form that tries to open a report.  This is the error message I get.  They are both using the same Table.

How can I open my report?

thanks
0
Comment
Question by:galneweinhaw
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 5

Expert Comment

by:Krys_Wilson
ID: 12031289
Have you tried basing the report and the form from a query?

Chris
0
 

Author Comment

by:galneweinhaw
ID: 12031540
The Report is based on a query, the form is based on the tables as it edits data....
0
 
LVL 5

Expert Comment

by:Krys_Wilson
ID: 12031710
A form can still be based on a query and be updateable as long as its based on either 1 table or tables that are hooked together and one of the tables is a subform.

Still strange that you would be getting this error.  I just tested this in a testing database and I can have a form based on the table and the report based on the table and it still works correctly.

Try creating a new basic report based on the table and see if that works.  Use one of the wizards to create another one.

Chris
0
 
LVL 26

Accepted Solution

by:
dannywareham earned 300 total points
ID: 12033504
You tend to get this error if you are linking a query or table to a form, and then using a control to append or delete data from the table.

I've also seen it happen with Make-Table queries too.

From what I gather, its caused by the database trying to save data to the table, which is in a read-only due to the form pulling the information from it.

In this instance, you cannot write to the table until the query has run, but you can't run the query whilst the table is being editted - you end in a catch22 situation.

What is the code/SQL that you're using to update your table?
If you can post that, it may be something as simple as setting ALLOW ADDITIONS/EDITs etc status again.
0
 
LVL 39

Expert Comment

by:stevbe
ID: 12044145
I would commit any changes before opening the report ...

Private Sub cmdPreview_Click()
    'save any uncommitted changes
    If Me.Dirty = True Then Me.Dirty = False

    'open report in preview mode
    DoCmd.OpenReport ReportName:="rptMyReport", View:=acPreview

End Sub

Steve
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:galneweinhaw
ID: 12045873
Thanks for the help, here is all the code for the form including the code that opens the report.....

I tried not allowing additions/deletions etc. and tried the Me.Dirty stuff but still get the error.

----------------------------------------------------------------------------------------
Option Compare Database

Private Sub btnCancel_Click()
On Error GoTo Err_btnCancel_Click

    DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70 'Undo new record
    DoCmd.Close

Exit_btnCancel_Click:
    Exit Sub

Err_btnCancel_Click:
    If Err.Number = 2046 Then  'Error 2046 nothing to undo... don't care.
        Resume Next
    End If
    MsgBox Err.Description & " " & Err.Number
    Resume Exit_btnCancel_Click

End Sub


Private Sub Form_Current()

    'If this is a new record insert PlateSeriesID
    If (IsNull(Me.PlateSeriesID)) Then
            Me!PlateSeriesID = DMax("PlateSeriesID", "SpinCoatingSeries") + 1
            Me![Spin Date].SetFocus
    End If

End Sub


Private Sub btn_Save_Click()
On Error GoTo Err_btn_Save_Click

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    DoCmd.Close

Exit_btn_Save_Click:
    Exit Sub
   
Err_btn_Save_Click:
    Select Case Err
    Case 3314       'Missing required info field
        Call RequiredFieldMissing(Err.Description, True)
        Resume Exit_btn_Save_Click
       
    Case Else
        MsgBox Err.Description
        Resume Exit_btn_Save_Click
        Resume
    End Select
   
End Sub


Private Sub Number_of_Plates_AfterUpdate()
On Error GoTo Err_Plates_AfterUpdate

    Dim I As Integer 'counter dummy
    Dim ThisSeries As Integer
    ThisSeries = Me!PlateSeriesID.Value
    Dim NewNumber As Integer
    NewNumber = Me![Number Of Plates].Value
    Dim sqlSeriesTable As String
    sqlSeriesTable = "PlateDetails WHERE PlateSeriesID = " & ThisSeries

    'Open the Spin Plate Notes table (filtered for PlateSeriesID) for editing
    Dim Rs As New ADODB.Recordset
    'Set Rs = CurrentDb.OpenRecordset(sqlSeriesTable, dbOpenDynaset)
    Rs.Open sqlSeriesTable, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

    Dim NumberOfRecords As Integer
    NumberOfRecords = 0
   
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
   
    If (Rs.EOF = True) Then   'There is no plate data yet (no records)
        Rs.AddNew             'Then add one?
        Rs("PlateSeriesID") = ThisSeries
        Rs("PlateID") = 1
        Rs.Update
    End If
   
    Rs.MoveFirst
    Do While (Rs.EOF = False) 'Count how many records there are
        NumberOfRecords = NumberOfRecords + 1
        Rs.MoveNext
    Loop
   
    If (NewNumber < NumberOfRecords) Then
        Rs.MoveLast
        For I = (NumberOfRecords - NewNumber) To 1 Step -1
            Rs.Delete
            Rs.Update
            Rs.MovePrevious
        Next I
    End If

    If (NewNumber > NumberOfRecords) Then
        For I = (NewNumber - NumberOfRecords) To 1 Step -1
            Rs.AddNew
            Rs("PlateSeriesID") = ThisSeries
            Rs("PlateID") = DMax("[PlateID]", "PlateDetails", "[PlateSeriesID]=" & ThisSeries) + 1
            Rs.Update
        Next I
    End If
   
    Me![Plate Spin Notes].Requery
   
Exit_Plates_AfterUpdate:
    Rs.Close
    Set Rs = Nothing
    Exit Sub

Err_Plates_AfterUpdate:
    Select Case Err
    Case 3314       'Missing required info field
        MsgBox "The required Spin Coating Series data must be entered before plates can be added."
        Me![Number Of Plates] = Empty
        Call RequiredFieldMissing(Err.Description, True)
        Resume Exit_Plates_AfterUpdate
    Case Else
        MsgBox Err.Description & " " & Err.Number
        Resume Exit_Plates_AfterUpdate
    End Select
   
End Sub


Private Sub Number_of_Plates_BeforeUpdate(Cancel As Integer)

    If (Me![Number Of Plates] < Me![Number Of Plates].OldValue) Then
        Dim Response As Integer
        Response = MsgBox("There is currently data for " & Me![Number Of Plates].OldValue & " plates. Do you want the extra plates deleted?", vbYesNo + vbExclamation)
        If (Response = vbNo) Then
            Cancel = True
            Me!PlateSeriesID.Undo
        End If
    End If
   
    If (Me![Number Of Plates] = Me![Number Of Plates].OldValue) Then
        Cancel = True
        Me!PlateSeriesID.Undo
    End If
       
End Sub


Private Sub PlateSeriesID_BeforeUpdate(Cancel As Integer)
   
    If (Not IsNull(DLookup("[PlateSeriesID]", "SpinCoatingSeries", "[PlateSeriesID] =" & Me!PlateSeriesID))) Then
        MsgBox "Plate Series " & Me!PlateSeriesID & " already exists.", vbOKOnly + vbExclamation
        Cancel = True
        Me!PlateSeriesID.Undo
    End If

End Sub
Private Sub BakeDetails_Click()
On Error GoTo Err_BakeDetails_Click

    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim stArgs As String
   
    stArgs = Me![PlateSeriesID] & "|" & Me![Plate Spin Notes].Form![PlateID]
    stDocName = "Plate Bake Details"
   
    stLinkCriteria = "[PlateSeriesID]=" & Me![PlateSeriesID] & " AND [PlateID]=" & Me![Plate Spin Notes].Form![PlateID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria, , , stArgs

Exit_BakeDetails_Click:
    Exit Sub

Err_BakeDetails_Click:
    MsgBox Err.Description
    Resume Exit_BakeDetails_Click
   
End Sub
Private Sub AddNew_Click()
On Error GoTo Err_AddNew_Click
    DoCmd.GoToRecord , , acNewRec

Exit_AddNew_Click:
    Exit Sub

Err_AddNew_Click:
    MsgBox Err.Description
    Resume Exit_AddNew_Click
   
End Sub


Private Sub Report_Click()
On Error GoTo Err_Report_Click

    Dim stDocName As String
    stDocName = "Spin Coating Data Form"
   
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
   
    MsgBox "almost there"
    DoCmd.OpenReport stDocName, acPreview   '<------ DANGER! DANGER!  :)

Exit_Report_Click:
    Exit Sub

Err_Report_Click:
    MsgBox Err.Description
    Resume Exit_Report_Click
   
End Sub
0
 

Author Comment

by:galneweinhaw
ID: 12057735
I tried to close the form and open the report in a seperate sub.... didn't work, same problem:

Sub PrintSpinCoatingReport(SpinSeriesID As Integer)

    Dim stDocName As String
   
    stDocName = "Spin Series And Plate Details"
    DoCmd.Close acForm, stDocName, acSaveYes
       
    stDocName = "Spin Coating Data Form"
       
    MsgBox "almost there"
    DoCmd.OpenReport stDocName, acViewPreview, "PlateSeriesID = SpinSeriesID"   ' <------- Error happens here
               
End Sub
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 12061849
Where are the values "PlateSeriesID" and "SpinSeriesID" coming from?

The syntax on your OpenReport statement looks incorrect.
0
 

Author Comment

by:galneweinhaw
ID: 12066773
The code for the button to open the report:

Private Sub Report_Click()
On Error GoTo Err_Report_Click

    Call PrintSpinCoatingReport(Me!PlateSeriesID)   ' send ID of current record being viewed

Exit_Report_Click:
    Exit Sub

PlateSeriesID is an integer value.  It's also a field in the report...... but that's all kind of irrelevent cus the error and problem of opening the report doesn't care about the WHERE condition.
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 12073304
In your code:

DoCmd.OpenReport stDocName, acViewPreview, "PlateSeriesID = SpinSeriesID"

is the   "PlateSeriesID = SpinSeriesID"    meant to be a filter or condition?

It looks like a condition to me.
In which case, try changing to:

DoCmd.OpenReport stDocName, acViewPreview, , "PlateSeriesID = SpinSeriesID"  

(Note the extra comma)
0
 

Author Comment

by:galneweinhaw
ID: 12078471
Thanks,

but unfortunately this is not related to my problem of not being able to open my report and recieving the error message in the question title...
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now