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)
Imax = .RecordCount - 1
If Imax > 0 Then
For i = 0 To Imax
OptionSetList(i) = ![OptionSetName]
MsgBox "No Option Sets To Get!"
lboxChooseOptionSetList.List = OptionSetList
'These last lines cause an error: 3420 Object invalid or no longer set.