Closing an Access Database connection.

[strangeryet]

Hello,
I have a VB App that opens and closes an Access database in different sub procedures.

When the sub procedure that use the ADO method to read the database closes the database the .ldb goes away.

However when I run an Update statement like this in another procedure:

Set dbase2 = CreateObject("access.application")
dbase2.OpenCurrentDatabase "database path"

SQL = "Update Statement"
DoCmd.RunSQL SQL

dbase2.CloseCurrentDatabase

After the update runs the .ldb does not go away.
Is'nt the CloseCurrentDatabase statement all I need to close the connection? The connection does not go away until I end the VB application. ..However...
This app will be set up on a timer and will be open always. So how do i end this connection?

Thanks for you help!

 

[raminriahi]

Hi ,

Try this statement after closing the connection:

set dbase2=Nothing

Thanks,

Ramin

 

[strangeryet]

Ramin,
Thank you for the reply. I tried your suggestion, unfortunately the .ldb remained.
Thanks
Mike

 

[abaldwin]

Are you sure that you have not opened a previous connection somewhere else. It sounds like you have opened a connection, then in another procedure you have opened another connection, executed your update and closed that connection leaving the original open.


Andy Baldwin

"Testing is the most overlooked programming language on the books!"

Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

 

[JerryKlmns]

How about

....
dbase2.Quit
set dbase2=Nothing

 

[webturner]

Jerry's suggestion should work. What is happening is CreateObject("access.application") opens a new automation instance of MSAccess.exe rather than a connection to a database. Access doesn't normally clear .ldb files till it quits so doing dbase2.Quit should delete the file.

But...

Using automation will be quite slow especially if all you are doing is a simple SQL statement. Try one of these instead:

'With DAO
Dim db As New DAO.database
Dim ws As DAO.Workspace

Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase("c:\test.mdb")

db.Execute strSQL

'With ADO
Dim cnn As New ADODB.Connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data" & _
"Source=c:\test.mdb;User Id=admin;Password=;"
cnn.Execute strSQL

 

[strangeryet]

Thanks for you help.
There is something strange going on in the code.
There are three procedures.

Sub 1
Creates the database, opens the database, updates the database and (Closes the database...see note **)

Sub 2
Uses ADO to read the database, and closes the connection.

Sub 3
Creates the database, opens the database, updates the database and closes the database.

**Two interesting facts. (A) If I 'do not' close the database in Sub 1, Sub 3 runs successfully. (B) If I do close the database in Sub 1 then Sub 3 gets an error when it tries to update the database stating that the database needs to be open, yet there is an open statement in the sub and when I step through the code I can see in explorer the database opening because the .ldb pops up.

I even have the database defined as different names in the sub procedures. Also if I run the two sub procedures independently (ie. comment out one and run the other), they will both run successfully on their own.

Bottomline!
The application will work, if I don't close the database in Sub 1, however the .ldb still remains even after Sub 3 runs. Even though with the same code the .ldb goes away after Sub1 runs.

???

 

[JerryKlmns]

Our Crystal ball doesn 't expose your subs, would you just post them here for some1 to take a look, that is, if you do want more help