CODEWITHSUNDEEP
ms-accessvbalockingado
Sheed
Sheed

Reputation: 113

ADO Connection to Access leaves .ldb file behind with Screen.MousePointer = vbHourglass

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

The process 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.

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

It's on Vista.

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

UPDATE.

Can anybody explain this?

Upvotes: 1

Views: 2419

Answers (3)

Sheed
Sheed

Reputation: 113

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!

Upvotes: 3

Philippe Grondier
Philippe Grondier

Reputation: 11148

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....

Upvotes: 0

Kevin Ross
Kevin Ross

Reputation: 7215

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

Upvotes: 0

Related Questions