ADO Connection to Access leaves .ldb file behind

Question

I'm writing an MS Outlook (2003) macro which uses an ADO Connection to an Access DB (2003). 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:

Screen.MousePointer = vbHourglass
Set db = New ADODB.Connection 'Declared at module level
Set rs = New ADODB.Recordset 'Declared at module level
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   
    'actions performed on recordset removed 
End If
rs.Close  
db.Close
Set rs = Nothing
Set db = Nothing
Screen.MousePointer = vbDefault

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.

UPDATE I'm updating this with my findings of the cause of this which is truly weird.

• I removed PopulateFlexGrid and the problem still occurred just for opening and closing the recordset. The code I did omit from this sample, as I considered it irrelevant is that I wrapped the code with Screen.MousePointer = vbHourglass, at the start and Screen.MousePointer = vbDefault, at the end. I removed this and the problem no longer occurs. Stepping through I see MSACCESS.EXE start up in TM when I call Screen.MousePointer = vbHourglass. I can't quite believe my eyes when I see this happening.

• I also tried a version where I used DAO instead of ADO, no other difference, and it works without creating .ldb or starting up an MSACCESS.exe. This works with the Screen.Mousepointer code in there.

Can anybody explain this?

Answer 1

What is the code of the function PopulateFlexGrid? Is it possible this is not closing the recordset passed to it?

Try removing this line and instead just looping through the code without doing anything and seeing if it still leaves the LDB. If it still leaves the LDB then the problem is not with the PopulateFlexGrid function

Answer 2

I agree with david saying there is something unclear here. Such a code cannot create an instance of Access or an .ldb file. This code could even run without Access being installed on the machine.

Could you open your recordset with a clientSide cursor? Using a serverSide cursor might cause the access file to be modified (not sure ...) in a way or another. To make sure that your next command does not interfeer in a way or another with access, you could even copy your record locally (to an xml file), close both records and connection, reopen the recordset with the xml file as datasource, and then populate your flexgrid.

By the way, and though it has nothing to do with your problem, it is usually preferable to split the object declaration to:

dim myObect as ADODB....
set myObject = New ADODB....

Answer 3

The answer to this is that the reference to

Screen.MousePointer

which i'd omitted as irrelevant, is actually a member of Access. Hence when i call it MSACCESS starts up.

I should have used

Me.MousePointer = fmMousePointerHourGlass

So it's my fault for copying some code from Access VBA to Outlook VBA and expecting it to work the same. Apologies to all of you who spent time looking at this!