Access Query. Connection remaining open after macro exits...?

[PerlIsGood]

I have a macro I'm working on in OfficeXP {in Word, querying Access database} and it has been working fine. But, on occasion, the database 'locks up' and the Word macro spits out an error: Could not use ''; file already in use.

The database has multiple users accessing it at any given time and is set up as 'sharable'. Each time I've encountered this error, I've verified that no one else was physically in the database {in design mode}.

The problem has gotta be in my code somewhere:

' References Needed for this Project to function:
' -> VB for Applications
' -> OLE Automation
' -> MS Access 8.0 Library
' -> MS ActiveX Data Objects 2.0 Library
' -> MS Forms 2.0 Object Library
' -> MS Word (8.0 or 10.0) Object Library

Dim rst As New ADODB.Recordset
Dim fld As ADODB.Field

' Access tblClients Table in Client Database and get the Full Client Name

  rst.Open _
    "SELECT * FROM tblClients WHERE [Billing Code] LIKE '" & sClientCode & "'", _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & FileLoc & ";"
  sClientName = rst.Fields(0)
  sClientCode = rst.Fields(2)
  rst.Close
            
' Access tblClientTeams Table in Client Database and populate
' sClientTeam with the listed Practice Members

  rst.Open _
    "SELECT * FROM tblClientTeams WHERE Client = '" & sClientName & "'", _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & FileLoc & ";"
  sClientTeam = sClientCode & "-" & TextBox2.Value & "-" & TextBox3.Value & Chr(11) & _
    "RET/" & sC_Code & Chr(11)
  sClientList = sClientCode & "-" & TextBox2.Value & "-" & TextBox3.Value & Chr(11) & _
    "RET/" & sC_Code & Chr(11)
            
  For Each fld In rst.Fields
    If fld.Value <> &quot;&quot; And fld.Value <> sClientName Then
      sClientList = sClientList & fld.Value & &quot;; &quot;
      If opt3.Value = True Then
        sClientTeam = sClientTeam & fld.Value & &quot;; &quot;
      Else
        sClientTeam = sClientTeam & fld.Value & Chr(11)
      End If
    End If
  Next
  rst.Close

Is there something I need to add or change to make sure that the connection is closed every time the macro exits? Notorious P.I.G.

 

[asjeff]

Two things I can think of -

Firstly you could try closing the Database at the same time you close the recordset and reopen as necessary, and secondly set the recordset object to nothing to ensure there is no persistance there:

rst.close
set rst = nothing


Can't give you any more than that but I hope it helps....

Asjeff