Hello. I was asked a question that I have never thought of before.

How can an Access application open another one? Please do not misunderstand me here. I am not talking about using the OpenDatabase method. It would be more like closing the current Access app and open another one.

My customer has two separate databases and they are 'kind of' related. So from one form there would be a command button launching the other one.

From the other one I could verify if the app is open then close it.

Would it be similar to opening Word or Excel with a specified document?

Set objAcc = CreateObject("Access.Something")
With objAcc
    .Application.Visible = True
End With

or with

Shell "Access.exe", vbHide

I am not sure where to start with this. Any idea anyone please?

Daniel


Daniel

__________________
Daniel

I was messing around with this and really couldn't find a solution. What I would suggest is creating a new front end that uses data from both databases, or alternatively, opening a connection to the other database into yours, using forms imported from the other database. Opening and closing instances can be confusing for the user.



mmcdonal

Here is some code that we use to open a form in another database that is called from a click event from a form in our database.

Private Sub Btn_MOTBL_RemailReq_Click()
'This allows the Survey Program to Open a Form in the Project Database
'to deal with Remail Requests

'(Dim) Declare variables; dq, strExe, strMdb and strRun as String Data Type
Dim dq As String
Dim strExe As String
Dim strMdb As String
Dim strRun As String

dq = """"

'Define var strExe; Miscosoft Access
strExe = "C:\Program Files\Microsoft Office\Office\MSACCESS.EXE"

'Define var strMdb; Project database location
strMdb = "\\groups\data\CTRHS\CHCR\Programming\Data\SurveyI nterface\EFLRemailRequests.mdb"

'Define the entire statement to implement at Shell
strRun = dq & strExe & dq & " " & dq & strMdb & dq

'Run the code to open the project database
Shell (strRun)

End Sub

Thank you mmcdonal and specially John Parker. I tried your sample and it works after as I intended after I added a return value and an extra parameter for the Shell command.

This is what I used:

Private Sub cmdNorthwind_Click()
 Dim dq As String
 Dim strExe As String
 Dim strMdb As String
 Dim strRun As String
 Dim dblReturnVal As Double

 dq = """"

 'Define var strExe; Miscosoft Access
 strExe = "C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE"

 'Define var strMdb; Project database location
 strMdb = "D:\Daniel\Databases\Northwind.mdb"

 'Define the entire statement to implement at Shell
 strRun = dq & strExe & dq & " " & dq & strMdb & dq

 'Run the code to open the project database
 dblReturnVal = Shell(strRun, vbMaximizedFocus)

End Sub

Now, I guess this is harder, can I close the previous Access application window. This would be wonderful (not to mention an innovation some of us could use)!

Thanks again John.


Daniel

Daniel,

We don't have anything hightech to close, we just select the "x" at the top right.

Glad I could help but this is code that someone else wrote and we have been using for some time. Just thought it would be applicable in your situation. I'm just learning Access vba myself. I have lots to learn!


Cheers!!

John

Well guys I am glad to let you know that I found the perfect answer to my question. From an open Access database I click on a button and it opens Northwind and closes the calling database noooo worries!

Here is the code I use. You will see there is no magic here.

Public Sub AccessExample()
    Dim objAccess As Access.Application

    'Check that this is the path on YOUR PC for
    'Northwind.mdb, which ships with Access.
    Const conPATH = "D:\Daniel\Databases\Northwind.mdb"

    'Create an instance of the Access application object.
    Set objAccess = CreateObject("Access.Application")

    'Open the sample Northwind database that ships with
    'Access and display the application window.
    objAccess.OpenCurrentDatabase conPATH
    objAccess.Visible = True

    'Open the Employees form.
    objAccess.DoCmd.OpenForm "Employees"

    ' Maximize other Access window and quit current application.
    objAccess.DoCmd.RunCommand acCmdAppMaximize
    Application.Quit
End Sub

After I thought of it I can savely close the calling application once the other is opened.

Hope this helps anyone interested and thanks again for your help John.


Daniel