Launching 1 Access app from another

[Lysander]

I did this, many years ago, using Access 2.0 but have forgotten how I did it.

What I want is for my user to click on their access icon which will launch the main application, say, ProjectCode, connected to ProjectData at the backend.

I want a button on the menu that, when they click on it, closes ProjectCode and opens, say TimesheetCode, connected to TimesheetData on the back end.

So far all I have been able to do is use Shell to start another instance of Access with the new code, which is not what I want. I need the use to be able to switch between Project and Timesheet (in fact several databases) without having multiple copies of Access running on the PC.

I know it can be done, or at least, it could be done, but like I said, I forget how I did it.

I am using Access 2003 for this.

TIA for any help.

[Lysander]

Finally found out how I used to do it. (Thank god I found an unsecured version of 10 year old code, as no way could I remember the secure password)

You need sendkeys, but you can't run it from the menu form, it needs to be in a global function so that you can close the form first. And it needs a delay. Can't remember why, but it works with a delay, it crashes without one

Anyway, here is the simple code

Function LoadNewDBS()
 
    On Error GoTo Err_LoadNewDBS
    Dim lngTime As Long
    DoCmd.Close acForm, "fmnuMainMenu"
    DoCmd.Hourglass True
    lngTime = Timer: While Timer < lngTime + 1: DoEvents: Wend
    SendKeys "%FOc:\Education\Translate.mdb~", False
 
Exit_LoadNewDBS:
    Exit Function
Err_LoadNewDBS:
   MsgBox Err.Description & " in LoadNewDBS"
    Resume Exit_LoadNewDBS
End Function
 

Obviously file name and location can be passed as a parameter.

[ADezii]

@Lysander
A much better Option may be using Automation via the OpenCurrentDatabase() Method to open an existing Microsoft Access Database (.mdb) as the current database.

[Lysander]

Thanks ADezii.

That looks a lot cleaner, if only I could get it to work. If I use OpenCurrentDatabase it says database is already open. If I use CloseCurrentDatabase first, nothing else runs.

Searching the web on this, it looks like I have to run this through Excel or something other than Access.

I'll keep looking, because it is a tidier idea. (Especially as sendkeys would only work on an English version of access. I'm sure in Portugues the File/Open option would be different words.

[ADezii]

@Lysander

1. You can definately Open another Access Database from within Access.
2. Create a Public/Global Object Variable to represent an Access Application:
   

   Public appAccess As Access.Application

3. Execute the following code to Open the External Database within the Current Database. In this case, the code will Open the Employees Table in the Northwind Database residing in the C:\Test Folder:
   

   'Substitute your External DB Path here
   Const conPATH_TO_EXTERNAL_DB As String = "C:\Test\Northwind.mdb"
    
   'Create a New Instance of Microsoft Access
   Set appAccess = CreateObject("Access.Application")
    
   appAccess.OpenCurrentDatabase conPATH_TO_EXTERNAL_DB
    
   appAccess.DoCmd.OpenTable "Employees"

4. Don't forget to Close the External DB, and do your Claen-Up chores when finished:
   

   'Close the  Opened Database (Northwind)
   appAccess.CloseCurrentDatabase
   Set appAccess = Nothing

5. What you cannot do is 'Quit' the Current Database, then 'Open' the External Database in the same Code Context. You can, however, 'Close' a Form in the Current DB, then 'Open' the External DB.
6. Have you considered the use of Hyperlinks to Open the other DBs?

[Lysander]

Thanks.

What the above does is open a new instance of access with the new database.

To get rid of the calling database I added a docmd.quit which shut it down and looks ok, but not sure if I tieded up properly.

I can't use appAccss.closecurrentdatabase as the user would be using the new database for several hours, no idea when he would want to close it.

[ADezii]

@Lysander
Hope it all works out for you.

[Lysander]

@ADezii
Sendkeys method worked fine and they are all off to Chile and Argentia as happy bunnies:)