Another common request every now and then is how to launch another database from within the current database. As always, there are a number of ways of handling this, below are a few:

• FollowHyperlink Method
• Shell
• Access Automation

FollowHyperlink Method

FollowHyperlink is native to Access and can be used to open any type of file in its default associated program. Note that since it is native command, you can’t use it in another program (Word, Excel, …).

'---------------------------------------------------------------------------------------
' Procedure : OpenDb3
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Open another database
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sDb       : Fully qualified path and file name with extension of the database to
'             open
'
' Usage:
' ~~~~~~
'Call OpenDb3("C:\Users\Daniel\Documents\Database25.accdb")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2018-01-21              Initial Release - Website Demo
' 2         2022-08-21              Added file protocol
'---------------------------------------------------------------------------------------
Public Function OpenDb3(sDb As String)
    On Error GoTo Error_Handler
    
    'Application.FollowHyperlink sDb
    Application.FollowHyperlink "file://" & sDb 'Safer version

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: OpenDb3" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

Shell

Shell is one of those universal commands that allows one to do so much. Once again, it can be used to launch programs and open files. Typically, you need to supply the exe file and then whatever command line switches each individual exe recognizes. However, since office is registered and part of the path variable, we don’t even need to supply the path to the msaccess.exe file and need only provide the database path/file to open.

The only time you want to include the path/file of the executable for Access (and other Office apps) would be in the case that you have multiple versions installed and you want to use a specific one.

'---------------------------------------------------------------------------------------
' Procedure : OpenDb2
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Open another database
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sDb       : Fully qualified path and file name with extension of the database to
'             open
'
' Usage:
' ~~~~~~
'Call OpenDb2("C:\Users\Daniel\Documents\Database25.accdb")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2018-01-21              Initial Release - Website Demo
'---------------------------------------------------------------------------------------
Public Function OpenDb2(sDb As String)
    On Error GoTo Error_Handler
    
    Shell "cmd /c " & Chr(34) & sDb & Chr(34), vbHide

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: OpenDb2" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

Access Automation

Automation! Over the years I’ve come to truly appreciate the power of automation. With automation, you can basically do anything you want, so obviously, you can open a database.

Below are 2 very similar functions.

Simply Open And Maybe Perform A Task

'---------------------------------------------------------------------------------------
' Procedure : OpenDb
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Open another database
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Uses Late Binding, so none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sDb       : Fully qualified path and file name with extension of the database to
'             open
'
' Usage:
' ~~~~~~
' If OpenDb("C:\Users\Daniel\Documents\Database25.accdb") Then ...
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2014-11-07              Initial Release
' 2         2018-01-21              Error handling updated for posting on website
' 3         2023-03-03              Fixed issue with handling dbs opened excl
'---------------------------------------------------------------------------------------
Public Function OpenDb(sDb As String) As Boolean
    On Error GoTo Error_Handler
    'Early binding
    'Use the following line if being used in Access or using Access reference
    '   provides intellisense!
    Dim oAccess               As Access.Application
    'Late binding
    'Use the following line if being used outside of Access without an Access reference
'    Dim oAccess               As Object

    Set oAccess = CreateObject("Access.Application")    'Create a new Access instance
    With oAccess
        .OpenCurrentDatabase sDb    'Open the specified db
        Debug.Print oAccess.CurrentDb.Name 'This will trigger an error in the instance
                                            ' The db isn't opened, already open excl by
                                            ' another user, ...
        .Visible = True             'Ensure it is visible to the end-user
        .UserControl = True
'        .DoCmd.OpenForm "YourFormName"    'Open a form?
'        .DoCmd.RunMacro "YourMacroName"    'Run a Macro?
    End With
    
    OpenDb = True

Error_Handler_Exit:
    On Error Resume Next
    Set oAccess = Nothing
    Exit Function

Error_Handler:
    'Occurs when the db has checks (AutoExec) UserControl to block automation, doesn't stop the connection if it is already open though.
    '-2147417848   Automation error - The object invoked has disconnected from its clients.
    If Err.Number <> 91 Then
        MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: OpenDb" & vbCrLf & _
               "Error Description: " & Err.Description & _
               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
               , vbOKOnly + vbCritical, "An Error has Occurred!"
    End If
    Resume Error_Handler_Exit
End Function

Open And Pass Back The Instance To Keep Working With

'---------------------------------------------------------------------------------------
' Procedure : OpenDb
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Open another database
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Uses Late Binding, so none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sDb       : Fully qualified path and file name with extension of the database to
'             open
'
' Usage:
' ~~~~~~
' Set oDb = OpenDb("C:\Users\Daniel\Documents\Database25.accdb")
' If Not oDb Is Nothing Then oDb.DoCmd.OpenForm "YourFormName"
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2014-11-07              Initial Release
' 2         2018-01-21              Error handling updated for posting on website
' 3         2023-03-03              Fixed issue with handling dbs opened excl
'---------------------------------------------------------------------------------------
Public Function OpenDb(sDb As String) As Access.Application
    On Error GoTo Error_Handler
    'Early binding
    'Use the following line if being used in Access or using Access reference
    '   provides intellisense!
    Dim oAccess               As Access.Application
    'Late binding
    'Use the following line if being used outside of Access without an Access reference
'    Dim oAccess               As Object

    Set oAccess = CreateObject("Access.Application")    'Create a new Access instance
    With oAccess
        .OpenCurrentDatabase sDb    'Open the specified db
        Debug.Print oAccess.CurrentDb.Name 'This will trigger an error in the instance
                                            ' The db isn't opened, already open excl by
                                            ' another user, ...
        .Visible = True             'Ensure it is visible to the end-user
        .UserControl = True
    End With
    
    Set OpenDb = oAccess

Error_Handler_Exit:
    On Error Resume Next
    Set oAccess = Nothing
    Exit Function

Error_Handler:
    'Occurs when the db has checks (AutoExec) UserControl to block automation, doesn't stop the connection if it is already open though.
    '-2147417848   Automation error - The object invoked has disconnected from its clients.
    If Err.Number <> 91 Then
        MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: OpenDb" & vbCrLf & _
               "Error Description: " & Err.Description & _
               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
               , vbOKOnly + vbCritical, "An Error has Occurred!"
    End If
    Resume Error_Handler_Exit
End Function

Note. the OpenCurrentDatabase method also allow passing the a password as the 3rd input argument should the database be secured.

Also, to my great surprise OpenCurrentDatabase does not report any error back in VBA when it fails to open a database (think of a database already in exclusive mode by another user/process)! This is why I have a Debug.Print line in the code, to trigger an error, so I know the database wasn’t opened.

Another, perhaps better approach might be to first check if the file is open, before trying to open it, using the File_IsOpen function from:

Which One To Use?

Simple is typically best so Shell and FollowHyperlink are about as simple as can be BUT, the automation approach offer you the ability to interact with the database after it is launched. So it enables you to open forms, reports, run macros, VBA code and the likes with great ease. So which one should be used depends on what you need to accomplish (like always)!