Created workspace not seeming to close 1

[paradiso]

Friends,

From one database I need to scan the tables another to pick out a certain one and grab its name into a local variable. To do this, I create a workspace and open the database. When I've got what I want, I close the database and the workspace, and set the variables to nothing.

Trouble is, when I proceed further in my code, subsequent routines produce errors, and I get messages telling me I am do not have exclusive access to my database. Is that other workspace still open somehow?

Here is a bit of the code.

Dim wrkJet As Workspace
Dim RawDataDB As DAO.Database
Dim varTbldef As DAO.TableDef


Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)

' Open Database object from saved Microsoft Jet database
' for exclusive use.

Set RawDataDB = wrkJet.OpenDatabase ("L:\eCommShared\EMarketingDB\ISRawData.mdb")

For Each varTbldef In RawDataDB.TableDefs

‘here I loop through the tables, looking for a specific one that I’ll use later
‘ as the table name in a query

Next varTbldef

'then I close out

RawDataDB.Close
Set RawDataDB = Nothing
wrkJet.Close
Set wrkJet = Nothing
Exit Sub


I am opening a workspace within a workspace, and I feel this is the root of the problem. Your suggestions are welcome.

best,
p

 

[lameid]

First thing I would do is use LDBview or notepad to open the ldb file and figure out who as the database open. If it is you then your probably right about the code.

Also you may have better luck with the default workspace...

Set wrkJet = Workspaces(0)

Or if this works it would be better...

Set wrkJet = Currentdb().parent 'I have doubts

You may also have better luck with ADO over DAO. Unfortunately I'm not quite that familiar with ADO to give you an example.

 

[easyit]

OK,

I 'm not 100% familiar with workspaces in Access because I never used them. but, I believe that when you create a new workspace, you basically start up another access.exe and open a DB, which is probably already opened. And if you have not implemented security, you are opening the DB as the user Admin, which opens the DB exclusively. This can be only once....

Wouldn't it be simpler to create a ADO connection to the DB and loop through the tables?

Private Function fGetTableNames() As Boolean
   Dim cat As ADOX.Catalog
   Dim localTbl As ADOX.Table
   Dim cn As ADODB.Connection
   
On Error GoTo Error_Handler
    fGetTableNames = True
   Set cn = New ADODB.Connection
   cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\MyRemoteDB.mdb"

   Set cat = New ADOX.Catalog
   Set localTbl = New ADOX.Table

   ' Open the catalog.
   cat.ActiveConnection = cn
   
   For Each localTbl In cat.Tables
      If localTbl.Type = "TABLE" Then
         Debug.Print localTbl.Name
      End If
   Next
   cn.Close
   Set cat = Nothing
   Set cn = Nothing

Exit_Handler:
    Exit Function

Error_Handler:
    fGetTableNames = False
    MsgBox "Error in procedure fGetTableNames. " & CurrentUser() & " " & (Err.Number & " " & Err.Description & " ")
    GoTo Exit_Handler
End Function

EasyIT

 

[paradiso]

Perfect. The ADO solution is superior to the DAO one in this instance.

Many thanks to EasyIT.