How to close a Front End database from another one

[Mihail]

Hello !

I have more than one Front End databases based on the same Back End database.

I know (but I think is not necessary) how to check from one of my Front End database if another one is running.
I wish to close, using VBA, all Front End databases except the current one.

Can you help me ?

Thank you !

[NeoPa]

Are you asking how to exit all copies of Access on the same PC other than the one just opened into this particular database, or is this more complicated than that?

[Mihail]

Thank you for reply, NeoPa.

You know very well this thread:

http://bytes.com/topic/access/answers/925508-link-tables-multiple-front-end-databases-same-time#post3693605

The routine to relink tables work, for each FE, even if that FE is open. But the forms are not refreshed (and is not possible to do that because the data are changed when I switch to different BE).
So I wish to automatically close all that FEs in order to force the user to reopen it (and see new data).

One of the approach can be to prompt the user to manually close the FEs before run code to relink the tables. But I think that is more elegant to inform the user that the program will close other FEs and prompt he to allow (or not) this task. If the user do not allow that, the program will not relink tables.

It is strongly necessary that all my FEs to point, any time, to the same BE.

And NO.
I don't wish to exit from all copies of Access. Only to close specified instances: my FEs.

[NeoPa]

In that case I'd take the approach of connecting to each open Application of Access and checking if it has an open database. If so, and the database is one from your list, then close it. I'm not sure how you can access multiple versions of Access I'm afraid. Once you have the Application object though, it's pretty straightforward to determine which database, if any, is open.

[Mihail]

This code check if a certain database is open:

Function IsDatabaseRunning(strDBName As String) As Boolean
'   Function to check if a database is already running
'   Accepts:
'       The path and name of an Access database
'   Returns:
'       True if the database can't be opened (because it is already open)
'       False if the database can be opened (because it is not already open)
    On Error GoTo E_Handle
    IsDatabaseRunning = True
    Dim db As Database
    Set db = DBEngine(0).OpenDatabase(strDBName, True)
    IsDatabaseRunning = False
fExit:
    On Error Resume Next
    db.Close
    Set db = Nothing
    Exit Function
E_Handle:
    Select Case Err.Number
        Case 3704 ' Database already opened
        Case Else
            MsgBox "E_Handle  " & Err.Description, vbOKOnly + vbCritical, "Error: " & Err.Number
    End Select
    Resume fExit
End Function

So I know if one of my FE is open.
But no way to say to Access: Close that FE !

As far as I know the Application object can be used only for that instance of Access that running at a certain time (the Active one).
Hope I am wrong. Or to be another way to do the task.

[NeoPa]

I've not done exactly this before, but if you can determine the names of the open databases then you should see if you can find a way of getting an Access.Applicat ion object for that particular Access session using Application Automation.

[NeoPa]

FYI: I opened another database when I already had my Bytes.Mdb database open, and ran this code which worked perfectly as expected :

Public Sub TestCtrl()
    Dim appBytes As Application

    Set appBytes = GetObject("H:\MyPath\Access\Bytes.Mdb", "Access.Application")
    Debug.Print appBytes.CurrentDb.Name
End Sub

The resultant string was :

H:\MyPath\Access\Bytes.Mdb

That should be enough to work with. The concept is proven.