none
Open multiple Access 2010 databases RRS feed

  • Question

  • I want my users to be able to open a second access database from a menu choice in the first. I did some research and came up with the following code:

    Option Compare Database
    Option Explicit
    Private appAccess As Access.Application
    Private strDB As String

    Private Sub RMUsers_Click()
    Dim appAccess As Access.Application
    strDB = "c:\Local My Documents (LR8N1KD4)\RMUsers\RM_Users.accdb"
     Set appAccess = New Access.Application
    'Set appAccess = CreateObject("Access.Application")

    appAccess.Visible = False

    appAccess.OpenCurrentDatabase strDB

    appAccess.Visible = True

    End Sub

    This, however, generates the following error:

    Run-time error 7866

    "Microsoft Access can't open the database because it is missing, or opened exclusively by another user, or it is not an ADP file.

    I'm not sure why this is not allowing me to launch the second access database. Any help would be greatly appreciated.


    Dean J. Waring

    Thursday, August 18, 2016 8:29 PM

Answers

  • Thank you....I will try this

    Dean J. Waring

    • Marked as answer by DeanJW2006 Monday, August 22, 2016 3:50 PM
    Monday, August 22, 2016 3:36 PM
  • I finally got to try this method and you are right. It is simple and works well, thank you.  Any draw backs to launching additional Access programs this way?

    Dean J. Waring

    • Marked as answer by DeanJW2006 Thursday, August 25, 2016 6:31 PM
    Thursday, August 25, 2016 6:31 PM

All replies

  • Hi Dean. Have you tried a simpler approach?

    Application.FollowHyperlink strDB

    Hope it helps...

    Thursday, August 18, 2016 8:32 PM
  • >>>I'm not sure why this is not allowing me to launch the second access database. Any help would be greatly appreciated.

    According to your description, I have made a sample to try to reproduce this issue, unfortunately, I am not able to get this error, but I find that there are some mistake in your sample code:
    Private appAccess As Access.Application
    
    Dim appAccess As Access.Application
    You declare appAccess twice, so I suggest that you could remove "Dim appAccess As Access.Application" this statement, or refer to below code:
    Function OpenAnotherDb(FileName As String, Optional PWD As String)
          Dim objAcc As Object
          Set objAcc = CreateObject("Access.Application")
          If Len(PWD) > 0 Then
              objAcc.OpenCurrentDatabase FileName, , PWD
          Else
              objAcc.OpenCurrentDatabase FileName
          End If
          objAcc.Visible = True
          objAcc.UserControl = True
         Set objAcc = Nothing
    End Function
    In addition could you open your Access Database file manually?
    Friday, August 19, 2016 2:21 AM
  • Dean -

    I get that error if the database named by strDB doesn't exist, but not if it does -- maybe you misspelled something in the name or path assigned to strDB.

    However, once you've corrected that, you'll find that you also need to set the new Access application's UserControl property to True of you want the database to remain open independently after the appAccess variable goes out of scope.  This code works for me:

    Private Sub RMUsers_Click()
    
        Dim appAccess As Access.Application
        Dim strDB As String
        
    '    strDB = "c:\Local My Documents (LR8N1KD4)\RMUsers\RM_Users.accdb"
        strDB = "C:\Users\Dirk\Documents\club contacts.mdb"
        
        Set appAccess = New Access.Application
     
        appAccess.OpenCurrentDatabase strDB
        appAccess.Visible = True
        appAccess.UserControl = True
    
        Set appAccess = Nothing
     
    End Sub
    

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Friday, August 19, 2016 6:43 PM
  • Thank you....I will try this

    Dean J. Waring

    • Marked as answer by DeanJW2006 Monday, August 22, 2016 3:50 PM
    Monday, August 22, 2016 3:36 PM
  • Hi Dean. Good luck!
    Monday, August 22, 2016 4:00 PM
  • I finally got to try this method and you are right. It is simple and works well, thank you.  Any draw backs to launching additional Access programs this way?

    Dean J. Waring

    • Marked as answer by DeanJW2006 Thursday, August 25, 2016 6:31 PM
    Thursday, August 25, 2016 6:31 PM
  • Hi Dean. Glad to hear you got it working. Which solution did you end up using? I think opening other databases in Access at the same time, for the most part, is okay. Cheers!
    Thursday, August 25, 2016 6:36 PM