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