Way to verify my Access db is closed?

[mrgreen]

Howdy,

Currently, I'm using an XL file (2003) that copies my Access BE file (2003) to the users Desktop and another Access.mdb with Queries and linked tables to the BE.
I then execute a macro inside the db with the queries to relink the tables to the users Desktop. Then using ADO I pull data from the Queries and populate several spreadsheets. Confused?
It all works great but when I establish my ADO connection I get a File Already In Use Error. If I put a Wait command in it works but I hate using Wait commands as a work around. Just looking for some suggestions or perhaps a light slap in the face

Shell strBatFile 'This executes my Batch file
Application.StatusBar = "Verifying Local Tables..."
'Making sure Files are copied to the Desktop
Do While FileThere(strWorkHorsemdb) = False
Loop
Do While FileThere(strBEmdb) = False
Loop
Application.StatusBar = "Local Tables verified..."
Call LinkTables
End Sub

Function FileThere(FileName As String) As Boolean
FileThere = (Dir(FileName) > "")
End Function

Private Sub LinkTables()
Dim struser As String
Dim strpath As String
Dim db As Object
Application.StatusBar = "Linking Local Tables..."
struser = LCase(Environ("USERNAME"))
strpath = "C:\Documents and Settings\" & struser & "\Desktop\Work Horse.mdb"
' Get a reference to Access
Set db = CreateObject("Access.Application")
'Open database...
db.OpenCurrentDatabase strpath
'Hide Access app...
'db.Visible = False
db.DoCmd.RunMacro "M_LinkTables"
' Close the database.
db.CloseCurrentDatabase
'Quit Access.
db.Quit
'Free up memory
Set db = Nothing
Application.StatusBar = "Local Tables Linked and refreshed..."
End Sub

'Then when I use ADO I get a File Already In Use Error
[struser = LCase(Environ("USERNAME"))
strpath = "C:\Documents and Settings\" & struser & "\Desktop\Work Horse.mdb"
strconn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strpath & ";"
'Application.Wait (Now + TimeValue("0:00:10"))
cnt.Open strconn Here is where I get the error

 

[mrgreen]

Not sure if this is the best way but I just put an Error Trap in

On Error GoTo ErrExit
cnt.Open strconn
.
.
.
.

Exit Sub
ErrExit:
Err.Clear
Resume
End Sub

 

[ghudson]

Instead of Application.Wait did you try using DoEvents

 

[mrgreen]

Hi ghudson, Sorry for the ignorance but I'm not sure where I'd put the DoEvents command? I kind of understand what it does but not sure what the context would be. How will the program know when the db is closed? Thanks again for the reply.