ADO Connection to Access leaves .ldb file behind
Sullivan@discussions.microsoft.com Paul Sullivan
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.
Bob Barrows
> 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.
All users of an mdb file require Modify permissions on the _folder_
containing the mdb file. They require the ability to create, modify _and
delete_ the ldb file.
If another process has the database file open, the ldb file cannot be
deleted, of course. If the user in that process did not have permissions
to delete the file when the process ended, the file will remain and
might have a lock on it.
--
HTH,
Bob Barrows
Paul Sullivan
So, the problem could be that, because this is running on Vista, if the user
running Outlook is not an admin the disconnect won't work properly. I will
try changing the permissions on the folder, or running Outlook "As
Administrator"
"Bob Barrows" wrote:
> .
>
Ralph
"Paul Sullivan" <Paul Sull...@discussions.microsoft.com> wrote in message
news:DEACA7AD-D117-47D8...@microsoft.com...
There is nothing in the code above that would require MSAccess.Exe to be
loaded, or an ldb file to be created.
I suspect something else is going on.
-ralph
Bob Barrows
the user be granted Modify permissions on the folder. I've never used
Vista so I cannot help with the specifics. In NT/XP, I would right-click
the folder in Windows Explorer and select "Sharing and Security", and
then use the dialog to add the user to the folder security list with the
Modify permissions. If it is different in Vista, you will need to ask in
a Vista newsgroup how to do it.
I've just seen Ralph's reply and he has a point: using ADO does _not_
involve running msaccess.exe - it uses the Jet database engine to
connect to an mdb file. That msaccess.exe process is associated with
running Access itself. Do you have any code that automates Access?
Something like:
dim acc as Access.Application
set acc=new Access.Application
If so, that could be the likely culprit.
--
HTH,
Bob Barrows
Bob Barrows
>
> There is nothing in the code above that would require MSAccess.Exe to
> be loaded, or an ldb file to be created.
>
but I do agree that MSAccess.Exe will not be involved.
--
HTH,
Bob Barrows
Ralph
"Bob Barrows" <reb0...@NOyahoo.SPAMcom> wrote in message
news:eslXxUec...@TK2MSFTNGP05.phx.gbl...
Yeah, I blew it. Was thinking one thing, typing another. <g>
Paul Sullivan
the exact same thing but with DAO and it seems not to cause the same problem.
Maybe anybody who paid for Vista should get a free copy of Windows 7 (as
well as getting their head checked!)
"Ralph" wrote:
> .
>
Bob Barrows
use for Jet databases. If it's solved your problem, then there is no need
beyond simple curiosity to investigate the ADO failure any further.
I have to admit to being really curious about that msaccess.exe process ...
:-)
--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Ralph
"Paul Sullivan" <PaulSu...@discussions.microsoft.com> wrote in message
news:67FFBB2E-9A25-4B21...@microsoft.com...
> Thanks guys. I promise you, there is no other code! I've just quickly
tried
> the exact same thing but with DAO and it seems not to cause the same
problem.
>
I agree with Bob, whenever working with Jet in a desktop environment DAO is
the way to go.
And I too would dearly like to know how MSAccess got in the middle of all
this. If you ever discover the reason please come back and let us know. <g>
[This probably has nothing to do with it, but what version of JetMSAccess
are you using, and are you using any User-level security within the
database?]
-ralph
Paul Sullivan
creating any MSACCESS.EXE or .ldb. If anyone reads this and understands why
ADO does that there are 3 of us who would be most interested.
Paul Sullivan
Paul Sullivan
I seem to have found the cause
My code was wrapped in
Screen.MousePointer = vbHourglass
...
Screen.MousePointer = vbDefault
Which I omitted as i thought it irrelevant to the problem. Turns out that
MSACCCESS.EXE starts up as soon as I make the first MousePoiner call. I can't
quite believe my eyes. What the hell is this all about?
"Ralph" wrote:
> .
>
Paul Sullivan
Screen.MousePointer
which I'd originally 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 you for time spent time looking
at this!
Bob Barrows
Access.Application library. But still ... without a call to set an object to
that class, I don't understand why that line of code didn't simply raise an
error ... unless Outlook is giving you some "help" behind the scenes ...
--
Ralph
> The answer to this is that the reference to
> Screen.MousePointer
> which I'd originally 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 you for time spent time
looking
> at this!
>
lol
That is classic. Thanks for reporting back.
But don't beat yourself up too much - I too, more times that I would like to
publicly admit, have accidentally imported 'extended' objects from one
Office VBA environment into another, with pretty much the same amount of
head scratching until I finally realized what was going on.
You now have the advantage of knowing what to watch out for and will be
careful and diligent to insure it doesn't happen again - that is until it
does. <g>
-ralph