1

There are three separate shared Access databases (tampa1, tampa2, tampa3), multiple users access them. Each user needs to be able to switch databases (using a dropdown list/combo) at any given time, when the user does so. Goal: the program should be able to open the new database in the same instance and close the exiting database (in either order) if it was opened by the same user. I don't want the database to close another instance of any of the three databases opened by different user.

I was able to create the first part of the code, opening the database (although I am not sure if it has been opened in same or different instance). I created a code to close the exiting db (Applicaton.quit), however when I run the code, both databases are closed.

Public Sub Opendb21()
Static acc As Access.Application
Dim db As DAO.Database
Dim strDbName As String
Set acc = New Access.Application
strDbName = "C:\Users\YOURNAME\Documents\Opening multiple db Test\tampa2.accdb"
Set db = acc.DBEngine.OpenDatabase(strDbName, False, False)
acc.OpenCurrentDatabase strDbName
acc.Visible = True
'db.Close

'Set dbRemote = acc.CurrentDb
Set db = Nothing

'Closing the current db tampa1

Forms!Form1.cmd_CloseAccess_Click

End Sub


'Second sub
'Closing cmd -macro converted to VBA code
'This is on Form1, tampa1 database
Public Sub cmd_CloseAccess_Click()

    DoCmd.Quit acQuitSaveAll

End Sub

I am expecting the program to close the current db (tampa1); open the second db (tampa2), but the program closes both instead. I need a way to make sure the code doesn't close an instance currently open by different user.

Andre
  • 26,751
  • 7
  • 36
  • 80
Vladimir
  • 11
  • 3
  • 3
    Of course the curious question: why are users sharing three different databases which have suffix differences suggesting same exact structure? Ideally, users use their own Access apps that ALL connect to ONE backend. This is known as the [split design](https://support.office.com/en-us/article/split-an-access-database-3015ad18-a3a1-4e9c-a7f3-51b1d73498cc). – Parfait Aug 03 '19 at 18:09
  • I think need the Windows handle of each application instance. Not easy to code. https://learn.microsoft.com/en-us/office/vba/api/Access.Application.hWndAccessApp – June7 Aug 03 '19 at 18:41
  • Hi, I forgot to say that each database performs different tasks, I just want to facilitate the navigation process from one db to the next. The alternative would be consolidating the three databases into a single one, which would take more time to do. – Vladimir Aug 03 '19 at 19:51
  • 2
    Curious again, what would take more time? Development time by you or processing time on the user? What are these tasks? Consider normalizing your process to save headaches down the road. – Parfait Aug 03 '19 at 21:44
  • 1
    Use `Shell` to call a batch file that starts the instance you want. Then quit the current instance. But I agree with Parfait, that's a strange design. – Andre Aug 03 '19 at 21:58
  • Hi, yes, I agree with you both. I inherited the databases and was trying to connect them to facilitate user navigation before moving on to consolidate the three databases into one database, then split it and distribute the front end. I like the idea of using the Window handle API, will work around it. I guess that somehow the opening application should get the handler id to find the first database instance and close it. Another way could be using the network user id and do something similar, could be possible? – Vladimir Aug 04 '19 at 15:17
  • Andre, I will try the Shell approach as well, seems very clean and simple solution. Thank you! – Vladimir Aug 04 '19 at 16:23

1 Answers1

0

thank you for your hints! I found the solution to the issue by reading another Stack overflow posting, here is the link to that posting:

Access: Shell cmd Open MDB

I was missing the command "Application.UserControl=True" , which makes the application persistent and visible. I created a form which has a combo box that lists the databases I want to switch to. The variable ("OpenWhat")captures the name of the specific database and passes the parameter to a Module Sub, where it then completes the string by adding the rest of the information. After the new database is open, the Form in the first database closes the calling database. Here is my particular piece of code:

Private Sub cbb_open_otherdb_AfterUpdate()
Dim OpenWhat As String
OpenWhat = Forms!Form1.cbb_open_otherdb.Value

Call Opendb(OpenWhat)

Forms!Form1.cmd_CloseAccess_Click

End Sub


Public Sub cmd_CloseAccess_Click()
On Error GoTo cmd_CloseAccess_Click_Err

    DoCmd.Quit acQuitSaveAll


cmd_CloseAccess_Click_Exit:
    Exit Sub

cmd_CloseAccess_Click_Err:
    MsgBox Error$
    Resume cmd_CloseAccess_Click_Exit

End Sub

Option Compare Database
Option Explicit

Public Sub Opendb(ByVal OpenWhat)

Dim objApp As Access.Application
Set objApp = New Access.Application
objApp.UserControl = True
objApp.OpenCurrentDatabase "C:\Users\YOURNAME\Documents\Opening multiple db Test\" + OpenWhat & ".accdb"
MsgBox ("Opening  ") & OpenWhat
Debug.Print OpenWhat
Set objApp = Nothing

End Sub

I copied similar code to the other two databases to create a navigation ring. Thank you for your help!

Vladimir
  • 11
  • 3