Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2009
    Posts
    8

    Unanswered: CurrentDb puts database in exclusive lock

    Hi everyone, I'm new to this forum - I usually find solutions for my problems in the existing posts, but I really struggle this one:
    I have an MS Access form with a button that runs a code:

    Code:
    Private Sub btnFinish_Click()
    
    Dim strParentTBL As String
    Dim strThisPath As String
    Dim strDbCOMPILED As String
    Dim rs_PS1 As Recordset
    
    strThisPath = CurrentProject.Path & "\"
    strParentTBL = Me.cbxParentTbl.Value
    
    '<some more code that works fine>
    
    Set rs_PS1 = CurrentDb.OpenRecordset("SELECT * FROM [#tblSettings];")
    With rs_PS1
        .FindFirst ("[Setting]='dbCOMPILED'")
        If Not .NoMatch Then
            .Edit
            !SetValue = strDbCOMPILED
            .Update
        End If
        .Close
    End With
    '<more code>
    end sub

    when it gets to the line: Set rs_PS1 = CurrentDb.OpenRecordset is puts the database in exclusive mode and when I try to save changes I get a message:
    "Microsoft Office Access can't save design changes or save to a new database object because another user has the file open. To save your design changes or to save to a new object, you must have exclusive access to the file."

    Now, the database is saved locally on my hard drive and no other user has access to it. I noticed that when I opened the .ldb file using wordpad I can see just one user:

    PE11113629 Admin

    where PE11113629 is a number of my computer
    but after the abovementioned line of code is executed I can see this:

    PE11113629 Admin PE11113629 admin

    which to me looks like access opened another copy of my database and put it in exclusive mode, locking me from write mode.

    Strangely enough, the line above strThisPath = CurrentProject.Path & "\" is not causing any drama.

    Please anyone help - I've been trying to find the solution for it for weeks with no luck and it's so frustrating!!

    Thanks cheaps.
    Tom

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can always try:
    Dim dbs as DAO.Database
    Dim rs_PS1 As DAO.Recordset
    Set dbs = CurrentDb
    Set rs_PS1 = dbs.OpenRecordset("SELECT * FROM [#tblSettings];")
    Have a nice day!

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Is it possible that something has been opened in Design View? Do you happen to have another recordset that is not using CurrentDB?

  4. #4
    Join Date
    Apr 2009
    Posts
    8
    Quote Originally Posted by Sinndho View Post
    You can always try:
    Dim dbs as DAO.Database
    Dim rs_PS1 As DAO.Recordset
    Set dbs = CurrentDb
    Set rs_PS1 = dbs.OpenRecordset("SELECT * FROM [#tblSettings];")
    Nope: Set dbs = CurrentDb locks db as well.

  5. #5
    Join Date
    Apr 2009
    Posts
    8
    Quote Originally Posted by DCKunkle View Post
    Is it possible that something has been opened in Design View? Do you happen to have another recordset that is not using CurrentDB?
    I don't think so, I open the db, then go straight to the form and open it, form has got one textbox (tbxParentDB) and one combo (cbxParentTbl), here's the complete code:

    Code:
    Option Compare Database
    Private strPathFile As String
    
    Private Sub btnBrowse_Click()
    
    If CallDialog(strPathFile, 2) = False Then Exit Sub
    Me.tbxParentDB = strPathFile
    Call tbxParentDB_AfterUpdate
    End Sub
    
    Public Function CallDialog(strFileName As String, strFilterOpt As String)
    Dim dlgImport As FileDialog
        Dim blnReturn As Boolean
    
        Set dlgImport = Application.FileDialog(msoFileDialogFilePicker)
        With dlgImport
            .AllowMultiSelect = False
            .Filters.Clear
            Select Case strFilterOpt
            
            Case 1
            .Filters.Add "Import Files", "*.mdb; *.xls; *.csv; *.xml; *.wk1; *.wk2; *.wks", 1
            .Filters.Add "MS Access Tables and Queries", "*.mdb, *.mda, *.mde", 2
            .Filters.Add "Excel Spreadsheets", "*.xls", 3
            .Filters.Add "Comma Separated Files", "*.csv", 4
            .Filters.Add "XML Files", "*.xml", 5
            
            Case 2
            .Filters.Add "MS Access Tables", "*.mdb; *.mda; *.mde", 1
            
            Case Else
            On Error GoTo CallDialog_Error
            .Filters.Add "Import Spreadsheets", "*" & strFilterOpt, 1
            End Select
    skip01:
            .FilterIndex = 1
            .InitialFileName = ""
            .Title = "Select file..."
        End With
        
        If dlgImport.Show <> -1 Then
            blnReturn = False
            GoTo CallDialog_Exit
        Else
            blnReturn = True
        End If
        
        strFileName = dlgImport.SelectedItems(1)
        
        Set dlgImport = Nothing
        
    CallDialog_Exit:
        Set dlgImport = Nothing
        CallDialog = blnReturn
        Exit Function
        
    CallDialog_Error:
        MsgBox "Error in modCommon.ImportTable:" & vbNewLine & vbNewLine & _
            Err.Description, vbCritical, "Error"
        DoCmd.SetWarnings True
        blnReturn = False
        GoTo CallDialog_Exit
    
        End Function
    
    Private Sub tbxParentDB_AfterUpdate()
    Dim obj As TableDef
    Dim db As Database
    
    If Dir(Me.tbxParentDB) = "" Then
        MsgBox "Incorrect Path or Name of Database - try again."
        Exit Sub
    End If
    Set db = OpenDatabase(strPathFile)
    On Error Resume Next
    For i = 0 To Me.cbxParentTbl.ListCount - 1
      Me.cbxParentTbl.RemoveItem Index:=0
    Next i
    i = 0
    For Each obj In db.TableDefs
    If obj.Attributes = 0 Then 'zero = user table
    aa = obj.Name
    i = i + 1
    Me.cbxParentTbl.AddItem item:=aa
    End If
    Next obj
    db.Close
    Set db = Nothing
    End Sub
    
    Private Sub btnFinish_Click()
    
    Dim rst As Recordset
    Dim f As Field
    Dim strParentTBL As String
    Dim obj As TableDef
    Dim db As Database
    Dim strThisDB As String
    Dim strThisPath As String
    Dim strDbCOMPILED As String
    Dim strDbIMPORTS As String
    Dim blnReplace As Boolean
    Dim optCOMPILED As String
    Dim optIMPORTS As String
    Dim curdb As Database
    Dim ThisDb As PrivDBEngine
    
    strThisPath = CurrentProject.Path & "\"
    strParentTBL = Me.cbxParentTbl.Value
    
    If Me.cbxParentTbl.Value = Null Then
        MsgBox "Select table from the list and try again"
        Exit Sub
    End If
    
    ' create linked table in current db referencing to Parent database
    On Error Resume Next
    DoCmd.DeleteObject acTable, "P1_" & strParentTBL & "_PAR"
    DoCmd.TransferDatabase acLink, "Microsoft Access", strPathFile, acTable, strParentTBL, "P1_" & strParentTBL & "_PAR"
    
    strDbCOMPILED = strThisPath & "_db_" & strParentTBL & "\_dbCOMPILED.mdb"
    strDbIMPORTS = strThisPath & "_db_" & strParentTBL & "\_dbIMPORTS.mdb"
    
    Dim dbs As DAO.Database
    Dim rs_PS1 As DAO.Recordset
    Set dbs = CurrentDb '<<<<<<<<<<<<<here it locks the db
    
    Set rs_PS1 = dbs.OpenRecordset("SELECT * FROM [#StaticSettings];")
    With rs_PS1
        .FindFirst ("[Setting]='dbCOMPILED'")
        If Not .NoMatch Then
            .Edit
            !SetValue = strDbCOMPILED
            .Update
        End If
        .FindFirst ("[Setting]='dbIMPORTS'")
        If Not .NoMatch Then
            .Edit
            !SetValue = strDbIMPORTS
            .Update
        End If
        .Close
    End With
    '<<<<more code>>>
    End Sub
    Last edited by realspido; 08-26-10 at 22:52.

  6. #6
    Join Date
    Apr 2009
    Posts
    8
    I think I figured it out!!! I created a new form same as the original one and copied the code and it seems to work fine. I think the original form was corrupted or something?
    I'll let you know if further problems occur.
    Thanks for your efforts anyway!
    PS. I found an article about it on microsoft's website, it was basically saying that if the database is in some way corrupted then CurrentDb may put it in Exclusive lock. I tried and it worked (for now)!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •