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

    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)

    tboxSelected.Value = Null

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

    lboxChooseOptionSetList.List = OptionSetList


    'These last lines cause an error: 3420 Object invalid or no longer set.
    Last edited by paulzak; 08-28-07 at 11: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