ADO Connection to Access leaves .ldb file behind

[Paul Sullivan]

I'm writing an MS Outlook macro which uses an ADO Connection to an Access DB.
I am simply opening a connection, getting some records into a Recordset,
which I use to populate a grid (but not bind to). I then close the Recordset
and Connection and set both to Nothing.

Not rocket science is it? But I am getting an infuriating problem where the
above process actually creates an instance of MSACCESS.EXE, and a .ldb file
for the Access DB, both of which remain after I have closed the Connection,
Recordset, Macro and Outlook itself. One or both of these remnants is
preventing opening the Access DB until the MSACCESS.EXE process is manually
killed and the .ldb file is deleted. Everywhere I can find similar posts say
"close the connection" but that is not solving the problem.

Here's the VBA code:

Dim db As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sSQL As String

sSQL = "SELECT Customers.ContactFirstName As Name, Customers.ContactLastName
As Surname, Customers.EmailName AS Email, Customers.Address, Customers.Area,
Customers.Town FROM qryCustomersWithEmail ORDER BY Customers.ContactLastName
ASC"
db.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "C:\My
Documents\Tables.mdb;Persist Security Info=False"
rs.Open sSQL, db, adOpenStatic, adLockReadOnly
If rs.RecordCount > 0 Then
Call PopulateFlexGrid(grdCustomers, rs) 'N.B. this does not BIND the
recordset
rs.Close
db.Close
End If
Set rs = Nothing
Set db = Nothing

Would be great if someone can help. (P.S. it's on Vista)

As an aside, to explain why I'm doing this, I wanted to provide my customer
with an easy way to send bulk emails to everyone in his Access database. I
have tried this by accessing Outlook from Access but the equally infuriating
security "feature" of Outlook, which pops up a warning message for every
email created, scuppered this approach.

 

[Norman Yuan]

Opening ADO.Connection against *.mdb file does result in a *.ldb file, which
is created by Jet engine. When the connection is closed, the *.ldb file
should be gone. However, the code you showed SHOULD NOT create an Access.exe
instance. Are you sure the Access.exe instance is not created by other
process, or is created in other portion of your code? I think, the *.ldb
file being left behind is due to the Access.exe instance, not the code you
showed here.