If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Excel > Connecting to MS Access with DAO locks DB

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 08-28-07, 11:20
paulzak paulzak is offline
Registered User
 
Join Date: May 2004
Posts: 54
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 11:43.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On