Excel code to close an already openend access database

[fernando.rosales]

My goal is to close an already opened access database when Excel Opens. Before anyone asks why I am doing this, its because my excel file has a connection tied to this specific db that is opened.
The connection data that my excel file contains does not refresh if the db that is trying to connect is openend. Please help I am not too good with vba.

This one seemed to work but this actually opens the database (a copy) and then closes the copy, not the already opened db:

Dim accessApp As Object
Set accessApp = GetObject("c:path", "Access.Application")
accessApp.docmd.Quit
Set accessApp = Nothing

This one does not work and it gives me an error Object required:

 Dim accessApp As Object
 Dim s As String
 s = "c:path"
 Set accessApp = GetObject(s)
 accessApp = docmd.Quit

 

[The_Doc_Man]

You cannot do what you are trying to do. It is not possible (to my understanding).

Let me explain.

Opening a database creates a .LDB file (older versions of Access) or another kind of lock file (newer versions.) But that isn't why the file is open. It is open because somewhere, somehow, at some terminal on your network, a person running MSACCESS.EXE in their system's physical memory has a data structure that controls access to the database file in question. The .LDB is a manifestation of the database linkage, not the mechanism of it.

Here's the big "gotcha." That user's session CANNOT be affected by your attempt to close the database directly unless (a) you are on the same physical machine running in some type of time-sharing mode and (b) your session has the Windows permissions, rights, and privileges required to control external processes that aren't yours. Without (a) you cannot even SEE the user in question and without (b) you cannot AFFECT that user in any way.

Your observation that the code opens and closes a separate session (from the one you wanted to terminate) is simply a manifestation of this behavior. Except for very rare cases, you can ONLY close things YOU opened. You cannot touch anybody else's sessions at any time. If they are on separate workstations, you cannot even touch the workstation, much less the user's session.

In essence, if you are on machine A and want to kill a session on machine B, you have to find a way to "persuade" machine B to go away peacefully. This means several things including a flag that can be set in a database table indicating that you need to take over exclusive use of the file. But if you are doing this with Excel, you would have to first open Access as an application using VBA, which you say is not your strong suit.

You would then update a table (or insert into a table) to show the need for exclusive access. To do this, EVERY person running that database must be using common code underlying a form (in the form's Class Module) that reliably checks for the flag on a regular basis, which usually means (a) no user sees anything but forms and (b) the forms have OnTimer events that can, in the context of each active user, force the database to close ON THEIR PRIVATE WORKSTATIONS. Without (a) there would exist users you could not control. Without (b) there would be no implied communication between you and the other DB users.

I am sorry to have to tell you that your designed interaction is not going to work.

 

[fernando.rosales]

That's a pretty good explanation, I guess I still have a lot to learn.

thanks Doc