Having a problem with unlocking an Access DB.

[Jaymond Flurrie]

I have problems with closing an Access DB. Here's the code I have.

Sub TestClosing()
    Dim acdb As DAO.Database
    
    Set acdb = DBEngine.OpenDatabase("C:\tempDB0.accdb")
    acdb.Close
End Sub

When I run it, the focus from Excel VBE disappears and when I try to open manually the database after running the program, I only get a new file, tempDB0.laccdb and the DB doesn't open. If I explicitly press stop from Excel VBE, I can open the file however.

I'm pretty sure I miss some command here, but what command?

 

[xenou]

You don't really close a database except in the sense that all users get out of it. There's some threads on this topic around already - i.e., kicking all users out of a database. It's not really clear what you mean. Are you just trying to shut down Access? Are you saying you can't open your database?

Edit: You might just need to open the database in shared mode rather than exclusive mode. As far as I can tell, your code above would only open a connection to the DB and then close the connection. Not necessarily close other connections.

 

[Jaymond Flurrie]

You don't really close a database except in the sense that all users get out of it. There's some threads on this topic around already - i.e., kicking all users out of a database. It's not really clear what you mean. Are you just trying to shut down Access? Are you saying you can't open your database?

The idea is that once I have ran my VBA code to create the DB, I should be able to tell Access that "Ok, writing done" and destroy all connections to the file I opened so that when I use file explorer from Windows, I wouldn't be able to even see that it ever was open. So yes, I'm trying to shut down Access and I'm not able to access the last DB I created - accessing the earlier ones works well.

I'll try to search about this subject.

 

[xenou]

Typically not a problem ... may need to examine how you end your code. If you use DAO to open a database you just close the database that you opened. If you actually opened an Access instance you have to close the DB and then quit Access.

 

[Jaymond Flurrie]

Typically not a problem ... may need to examine how you end your code. If you use DAO to open a database you just close the database that you opened. If you actually opened an Access instance you have to close the DB and then quit Access.

I found this "DoCmd.quit" to exit Access, but is there some easy way to just kill all Access processes by using Excel VBA?

 

[xenou]

Sub TestClosing()
    Dim acdb As DAO.Database
    
    Set acdb = DBEngine.OpenDatabase("C:\tempDB0.accdb")
    acdb.Close
End Sub

Okay I see the point of your code above - it should open and close a database. Actually, it works for me. As I step through your code, I see the database is opened (.laccdb file is created). Then it is closed (.laccdb file is deleted).

 

[Jaymond Flurrie]

Sub TestClosing()
    Dim acdb As DAO.Database
    
    Set acdb = DBEngine.OpenDatabase("C:\tempDB0.accdb")
    acdb.Close
End Sub

Okay I see the point of your code above - it should open and close a database. Actually, it works for me. As I step through your code, I see the database is opened (.laccdb file is created). Then it is closed (.laccdb file is deleted).

I think the problem is that I had an Access instance running on background as a result of my earlier tests and that screwed up the environment. So most likely the final code I had was pretty much correct, but having the extra Access instance on got me fooled.

Thanks for the help though!

 

[xenou]

I found this "DoCmd.quit" to exit Access, but is there some easy way to just kill all Access processes by using Excel VBA?

Not really (that I know of). You should just be sure to close all database connections you open, and close all Access applications you open. DoCmd.Quit is only needed if you open the Access program itself (it is in fact only available to you as part of the Access object model).

 

[SydneyGeek]

Try adding

Set acdb = Nothing

...after you close the db. I tend to close, then destroy the refs for any objects that I declare.
If you have any other Access instances still lurking (eg from earlier experiments), kill them in the Task Manager.

 

[xenou]

I think the problem is that I had an Access instance running on background as a result of my earlier tests and that screwed up the environment. So most likely the final code I had was pretty much correct, but having the extra Access instance on got me fooled.

Thanks for the help though!

Got it. I'm a thread behind you. Must learn to type. I find the button on the front of my computer useful for clearing up many such problems ...