Results 1 to 1 of 1
  1. #1
    Join Date
    May 2004
    Posts
    56

    Unanswered: Connecting to MS Access with DAO locks DB

    Running Access 2000 & Access 2003

    Why can't I open the backend mdb file after the following code executes in a sub in the .xls front-end? This code runs after a user enters the db's password on a form in the .xls front-end and is used to run a simple select distinct query in the back-end db file that populates a listbox with about 20 items.

    The code was originally written using ADO (we don't want that approach now) and with ADO I could open the mdb file after running this sub routine. The DAO connect/code works fine but now if I want to go into the db file while the .xls is open and the above code was run...after clicking to open the mdb file in Explorer, I see the .ldb file appear but the file never opens. Closing the form with the command button that runs this code doesn't break/end connection. I can only open the mdb file once I close the .xls file. (There is no .ldb file when just running the code from Excel. Only when I manually try to open DB via Explorer.) Again, I can open the db file without a problem after running this code via ADO connect, instead.

    I wonder if the .xls file code is still connecting to the mdb file in some exclusive way and if so, how would I change the code to not open exclusively. Or is this the only way DAO works? Or is some small setting or code missing???

    These code changes were tried without success:
    (1) Below the OpenDatabase's 2nd parameter is set as False which I thought indicated to open shared. Or is that wrong??? (I also tried it as True) I also tried setting 3rd parameter to both True and False (Read-only). Made no difference.
    (2) I made the OpenRecordSet's 2nd parameter to dbOpenSnapShot. Made no difference.

    Here's the code.
    Thanks in advance for you input.

    ======================
    Dim db As DAO.Database
    Dim ws As DAO.workspace
    Dim rsData As DAO.Recordset
    Dim i As Integer, Imax As Integer
    Dim OptionSetList() As Variant


    Set ws = DBEngine.Workspaces(0)
    Set db = ws.opendatabase(ETTdatabaseName, False, True, "MS Access;PWD=" & gPassword)
    'False #1 (above) = Open db in exclusive (vs.) shared mode
    'False #2 (above) = Read-only

    Set rsData = db.openrecordset("qryIWmodelOptionSetNames", dbopendynaset, dbReadOnly)

    lboxChooseOptionSetList.Clear
    tboxSelected.Value = Null

    With rsData
    Imax = .RecordCount - 1
    ReDim OptionSetList(Imax)
    If Imax > 0 Then
    .MoveFirst
    For i = 0 To Imax
    OptionSetList(i) = ![OptionSetName]
    .MoveNext
    Next i
    .Close
    Else
    MsgBox "No Option Sets To Get!"
    Exit Sub
    End If
    End With

    lboxChooseOptionSetList.List = OptionSetList

    ws.close

    'These last lines cause an error: 3420 Object invalid or no longer set.
    'rsdata.close
    'db.close
    Last edited by paulzak; 08-28-07 at 12:43.

Posting Permissions

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