Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

switch between two open database

ameedoo3000

ameedoo3000

IS-IT--Management
Sep 20, 2016
233
EG
hi every body

I already have two Access databases open at the same time. I want to switch to another database through the first one using the vba code.

thank you
 
Sort by date Sort by votes
I use this code to do that:
Function OpenMDB()
Dim Sti, hWnd As Long
If Not WindowIsOpen("YourMdbName÷", True) Then
Sti = GetDBPath & "MdbName.mdb"
If Dir(Sti) = "MdbName.mdb" Then Shell "MSAccess.EXE " & Sti & " /CMD:" & Forms!Main!ID, vbMaximizedFocus
Else
hWnd = DialogGetHwnd("MdbName÷")
If hWnd Then AppToForeground , hWnd, vbMaximizedFocus
End If
End Function

'**************************************************************************************************************************************************************
' Navn : GetDBPath
' Version : 1.0
' Dato : 03-06-2009
' Inparam : -
' Outparam : Path to currentDB / Frontend
' Beskrivelse : Returens path to the currentdb
' TODO : -
'**************************************************************************************************************************************************************
Function GetDBPath() 'Code courtesy of Michael Borries
GetDBPath = Left(CurrentDb.Name, Len(CurrentDb.Name) - Len(Dir(CurrentDb.Name)))
End Function
 
Upvote 0 Downvote
hermanlaksko said:
I use this code to do that:
Function OpenMDB()
Dim Sti, hWnd As Long
If Not WindowIsOpen("YourMdbName÷", True) Then
Sti = GetDBPath & "MdbName.mdb"
If Dir(Sti) = "MdbName.mdb" Then Shell "MSAccess.EXE " & Sti & " /CMD:" & Forms!Main!ID, vbMaximizedFocus
Else
hWnd = DialogGetHwnd("MdbName÷")
If hWnd Then AppToForeground , hWnd, vbMaximizedFocus
End If
End Function

'**************************************************************************************************************************************************************
' Navn : GetDBPath
' Version : 1.0
' Dato : 03-06-2009
' Inparam : -
' Outparam : Path to currentDB / Frontend
' Beskrivelse : Returens path to the currentdb
' TODO : -
'**************************************************************************************************************************************************************
Function GetDBPath() 'Code courtesy of Michael Borries
GetDBPath = Left(CurrentDb.Name, Len(CurrentDb.Name) - Len(Dir(CurrentDb.Name)))
End Function
Click to expand...
Unfortunately this code did not achieve what I asked for and there is an error in the code. Is there another instead of using the Alt + Tab button?
 
Upvote 0 Downvote
Not 100% sure when or how you want to "switch" between the two databases? Do you want a Shortcut key sequence? Do you want a trigger in the code (if x happens, then toggle to second db)..

At any rate, I assume you want to pass data from one database to the other? (Code below will transfer records).


Code: 
Dim db1 As Object
Dim db2 As Object
Dim appAccess As Object

' Set the first database (this current database)
Set db1 = CurrentDb

' Open the second database in a new Access application instance
Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase "C:\path_to_your_second_database\secondDatabase.accdb"

' Set the second database
Set db2 = appAccess.CurrentDb

' Example of transferring data from db1 to db2
' You can interact with the second database (db2) like this
appAccess.DoCmd.TransferDatabase acImport, "Microsoft Access", db1.Name, acTable, "SourceTable", "DestinationTable"

' Optionally, make the second database visible to the user
appAccess.Visible = True

' Close the second database when done
'appAccess.CloseCurrentDatabase

' Clean up
Set db1 = Nothing
Set db2 = Nothing
Set appAccess = Nothing
 
Upvote 0 Downvote
SoftwareRT said:
Not 100% sure when or how you want to "switch" between the two databases? Do you want a Shortcut key sequence? Do you want a trigger in the code (if x happens, then toggle to second db)..

At any rate, I assume you want to pass data from one database to the other? (Code below will transfer records).


Code: 
Dim db1 As Object
Dim db2 As Object
Dim appAccess As Object

' Set the first database (this current database)
Set db1 = CurrentDb

' Open the second database in a new Access application instance
Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase "C:\path_to_your_second_database\secondDatabase.accdb"

' Set the second database
Set db2 = appAccess.CurrentDb

' Example of transferring data from db1 to db2
' You can interact with the second database (db2) like this
appAccess.DoCmd.TransferDatabase acImport, "Microsoft Access", db1.Name, acTable, "SourceTable", "DestinationTable"

' Optionally, make the second database visible to the user
appAccess.Visible = True

' Close the second database when done
'appAccess.CloseCurrentDatabase

' Clean up
Set db1 = Nothing
Set db2 = Nothing
Set appAccess = Nothing
Click to expand...
Sorry for the inconvenience

All I want is that I have database A and database B and both databases are open and I am currently on database A and I want to go to database B when I press a certain button inside the form without using the interesting button and tab

I hope my goal is clear .... and I am very sorry for the inconvenience again
 
Upvote 0 Downvote

Create the Button:​

  1. Open your form in Design View.
  2. Add a Command Button to the form (this will be the button that opens the second database).
  3. Right-click the button and go to Properties.
  4. In the Event tab, find the On Click event.
  5. Click the ellipsis (...) next to On Click and choose Code Builder.
Now, replace the code in the code builder with the following:

Modified VBA Code for Button Click:​

Code: 
' ...Open another database based on file path...'

Private Sub CommandButton_Click()



    Dim db1 As Object

    Dim db2 As Object

    Dim appAccess As Object



    ' Set the first database (this current database)

    Set db1 = CurrentDb



    ' Open the second database in a new Access application instance

    Set appAccess = CreateObject("Access.Application")

    appAccess.OpenCurrentDatabase "C:\path_to_your_second_database\secondDatabase.accdb" ' Update the path to your second database



    ' Set the second database

    Set db2 = appAccess.CurrentDb



    ' Optional: You can perform actions between the two databases here

    ' Example: Transferring data from db1 to db2

    ' appAccess.DoCmd.TransferDatabase acImport, "Microsoft Access", db1.Name, acTable, "SourceTable", "DestinationTable"



    ' Optionally, make the second database visible to the user

    appAccess.Visible = True



    ' Clean up

    Set db1 = Nothing

    Set db2 = Nothing

    Set appAccess = Nothing



End Sub

​

Or maybe you'd like to switch focus between the two opened database:​

Code: 
'...Toggle Databases...'
Private Sub CommandButton_Click()

    Dim appAccess As Object
    Dim dbName As String

    ' Get the current application instance
    Set appAccess = Application

    ' Loop through all open Access windows
    For i = 1 To appAccess.hWndAccessApp

        ' Get the name of the current window
        dbName = appAccess.CurrentDb.Name

        ' Check if it's the second database by name (example logic)
        If dbName <> "C:\path_to_current_database\currentDatabase.accdb" Then

            ' Switch to the other database window
            appAccess.RunCommand acCmdAppMaximize
            Exit For
        End If
    Next i

    ' Clean up
    Set appAccess = Nothing

End Sub

​

​


​

 
Upvote 0 Downvote
I want to go to database B when I press a certain button inside the form
Click to expand...
By 'to go' - do you mean 'connect to B and get (Select) some records from some tables in B'?
Or 'run some code in B'? Or 'transfer records between A and B'? Or... what do you want to do?
 
Upvote 0 Downvote
Andrzejek said:
By 'to go' - do you mean 'connect to B and get (Select) some records from some tables in B'?
Or 'run some code in B'? Or 'transfer records between A and B'? Or... what do you want to do?
Click to expand...
I have two Access databases (A) and (B). I open (A) and open the second database (B) with password 123 by pressing a button in a form in database (A). Database (B) opens but does not appear on the front of the screen and is behind the first database (A). In order to show it I have to press the Alt and Tab keys. I want to know the vba code to show it on the front. This is all I want.
I tried the previous solutions but they are not working as expected.
 
Upvote 0 Downvote
This video demonstrates how to use a button to switch between access files using Follow Hyperlink.

In the video, he is using a switchboard (3 files rather than 2). Since you want to go directly between the two rather than a switchboard, I tried putting a button "Switch to A" in the Second Database and a button "Switch to B" in the first database. The code goes in the on click event and please change the button names and file names to match your files.

Code: 
Private Sub btnSwitchToB()
    FollowHyperlink "C:\Users\Documents\AccessFiles\SecondFile.accdb"
End Sub


And did the same in the other database

Code: 
Private Sub btnSwitchToA()
    FollowHyperlink "C:\Users\Documents\AccessFiles\FirstFile.accdb"
End Sub
 
Upvote 0 Downvote
... by pressing a button in a form in database (A). Database (B) opens but does not appear on the front of the screen and is behind the first database (A).
Click to expand...

If you share your code (to open database B) I am sure there will be a simple addition to it to 'show B in front' of A
 
Upvote 0 Downvote
Andrzejek said:
If you share your code (to open database B) I am sure there will be a simple addition to it to 'show B in front' of A
Click to expand...
Private Sub Command1_Click()
Dim acc As Access.Application
Dim db As DAO.Database
Dim strDbName As String
DoCmd.Minimize
strDbName = "C:\Users\Ahmed\Desktop\b.accdb"
Set acc = New Access.Application
acc.Visible = True
Set db = acc.DBEngine.OpenDatabase(strDbName, False, False, ";PWD=123")
acc.OpenCurrentDatabase (strDbName)
DoCmd.Maximize
End Sub
 
Upvote 0 Downvote
sxschech said:
This video demonstrates how to use a button to switch between access files using Follow Hyperlink.

In the video, he is using a switchboard (3 files rather than 2). Since you want to go directly between the two rather than a switchboard, I tried putting a button "Switch to A" in the Second Database and a button "Switch to B" in the first database. The code goes in the on click event and please change the button names and file names to match your files.

Code: 
Private Sub btnSwitchToB()
    FollowHyperlink "C:\Users\Documents\AccessFiles\SecondFile.accdb"
End Sub


And did the same in the other database

Code: 
Private Sub btnSwitchToA()
    FollowHyperlink "C:\Users\Documents\AccessFiles\FirstFile.accdb"
End Sub
Click to expand...
Thank you very very much
Finally the code succeeded in achieving my goal
Greetings to all
 
Upvote 0 Downvote
You must log in or register to reply here.

Similar threads

grnzbra
  • Locked
  • Question
Using Access Database Without Having MS Access
Replies
9
Views
800
grnzbra
grnzbra
TheresAlwaysAWay
  • Locked
  • Question
Long-established database suddenly giving error Cannot open macro [Name] because it is not valid
Replies
6
Views
301
dhookom
dhookom
lmcate
  • Locked
  • Question
How to Open ACCESS (MDB) from an HTML Page 1
Replies
2
Views
514
lmcate
lmcate
PizMac
  • Locked
  • Question
error 3027 database or object is read-only
Replies
5
Views
260
PizMac
PizMac
LondonJohn
  • Locked
  • Question
Problem using Access 2003 and 2007 for the same database
Replies
4
Views
249
LondonJohn
LondonJohn

Part and Inventory Search

Sponsor

Back
Top