Open another database from within Access

E

ECEK

Registered User.
Local time
Today, 05:13
Joined
Dec 19, 2012
Messages
717
Press a command button on my form and my other database opens.

What on earth could be simpler than that!!!

errr...........Any ideas?
 
depends on whether you want to open the db within the current db or as a separate iteration

google/bing 'vba open access database' for plenty of suggestions
 
a separate iteration
 
Untested but I think

docmd.RunCommand acCmdOpenDatabase "yourdatabasefilepathgoesinhere"

will possibly work.
 
Sample Code to open a second database in different Access Application Window.
Code: 
' Include the following in Declarations section of module.
Dim appAccess As Access.Application

Public Function PrintReport()
'---------------------------------------------------------
'Original Code Source: Microsoft Access Help Document
'---------------------------------------------------------

    Dim strDB As String

' Initialize string to database path.
    Const strConPathToSamples = "C:\Program Files\Microsoft Office\Office11\Samples\"
    strDB = strConPathToSamples & "Northwind.mdb"

' Create new instance of Microsoft Access Application.
    Set appAccess = CreateObject("Access.Application")
' Make Application Window Visible
    appAccess.Visible = True

' Open database in Microsoft Access window.
    appAccess.OpenCurrentDatabase strDB

' Open Catalog Report in Print Preview
    appAccess.DoCmd.OpenReport "Catalog", acViewPreview
    
' Enable next line of code to Print the Report
    'appAccess.DoCmd.OpenReport "Catalog", acNormal

    'appAccess.DoCmd.Close acReport, "Catalog", acSaveNo
    'appAccess.CloseCurrentDatabase
    'appAccess.Quit
    
End Function

Copy Paste the code in a Standard Module of your database. Make changes to the code to open a database with a Report. After making changes click somewhere in the middle of the Code and press F5 to run the code.

For details visit the Page: Printing MS-Access Report from Excel
 
Last edited:
Nearly there Its crashing saying it cant find the database and highlighting this line
appAccess.OpenCurrentDatabase strDB

This is my code

Code: 
Public Function OPENSWITCHBOARD()
'---------------------------------------------------------
'Original Code Source: Microsoft Access Help Document
'---------------------------------------------------------

    Dim strDB As String
    Dim appAccess As Access.Application
' Initialize string to database path.
    Const strConPathToSamples = ("H:\Access\Switchboard.accdb")
    strDB = strConPathToSamples & ("H:\Access\Switchboard.accdb")

' Create new instance of Microsoft Access Application.
    Set appAccess = CreateObject("Access.Application")
' Make Application Window Visible
    appAccess.Visible = True

' Open database in Microsoft Access window.
    appAccess.OpenCurrentDatabase strDB

' Open Catalog Report in Print Preview
    appAccess.DoCmd.OpenReport "Catalog", acViewPreview
    
' Enable next line of code to Print the Report
    'appAccess.DoCmd.OpenReport "Catalog", acNormal

    'appAccess.DoCmd.Close acReport, "Catalog", acSaveNo
    'appAccess.CloseCurrentDatabase
    'appAccess.Quit
    
End Function
 
Const strConPathToSamples = ("H:\Access\Switchboard.accdb")
strDB = strConPathToSamples & ("H:\Access\Switchboard.accdb")
Click to expand...

Code in the second line have problems. Correct it as follows and run again:
Code: 
Const strConPathToSamples = ("H:\Access\Switchboard.accdb")
 strDB = strConPathToSamples

Or to match the code I have provided change it as:

Code: 
Const strConPathToSamples = ("H:\Access\")
 strDB = strConPathToSamples & ("Switchboard.accdb")

When you encounter an error in the code that you have modifed, you should compare your code line by line with the original code to track whether you have made any mistakes during modification, before reporting erros.
 
Hi there Apr pillai

I have corrected my code and all seemed well, however.

If I remove this line

Code: 
' Open Catalog Report in Print Preview
    appAccess.DoCmd.OpenReport "Catalog", acViewPreview

Then it opens the switchboard database and closes it immediately.

I created a report in switchboard called catalog and it works fine.

The switchboard database opens to Form1 automatically and this is what I need.

I tried adding:

Code: 
  appAccess.DoCmd.OpenForm "Form1", acNormal, "", "", , acNormal
But once again , switchboard opens and closes immediately.

Could you advise further?
 
ECEK said:
But once again , switchboard opens and closes immediately.

Could you advise further?
Click to expand...
It could be because you've declared appAccess inside the procedure/Fuction and when you reach the Exit Function the object reference to the Access application (appAccess) shut down.
Declare appAccess in the top of the module.
Put "Form1" in the property "Display Form" for the database.
 
Thanks JHB, I'm afraid that I am unable to understand your solution. I was hoping that you might be able to correct the code.

This is what I have.

Code: 
Public Function OPENSWITCHBOARD()

    Dim strDB As String
    Dim appAccess As Access.Application

    Const strConPathToSamples = ("H:\Access\")
    strDB = strConPathToSamples & ("Switchboard.accdb")

    Set appAccess = CreateObject("Access.Application")

    appAccess.Visible = True

    appAccess.OpenCurrentDatabase strDB

    appAccess.DoCmd.OpenForm "Form1", acNormal, "", "", , acNormal

End Function
 
Found it !! Yay

Command button with this:
Code: 
Private Sub Command1_Click()
Dim accessApp
Set accessApp = CreateObject("Access.Application")
accessApp.Visible = True

accessApp.UserControl = True

accessApp.OpenCurrentDatabase ("H:\Access\Switchboard.accdb")

End Sub

It doesn't work in Runtime though for some reason !!
Bugger

Thanks to all participants
 
ECEK said:
Thanks JHB, I'm afraid that I am unable to understand your solution. I was hoping that you might be able to correct the code.

This is what I have.

Code: 
Public Function OPENSWITCHBOARD()

    Dim strDB As String
    [B][COLOR=Red]Dim appAccess As Access.Application[/COLOR][/B]

    Const strConPathToSamples = ("H:\Access\")
    strDB = strConPathToSamples & ("Switchboard.accdb")

    Set appAccess = CreateObject("Access.Application")

    appAccess.Visible = True

    appAccess.OpenCurrentDatabase strDB

    appAccess.DoCmd.OpenForm "Form1", acNormal, "", "", , acNormal

[B][COLOR=Red]End Function[/COLOR][/B]
Click to expand...
Only for clarifying:
You've declared appAccess inside the the procedure/Function, (marked red).
When you reach the End Function (marked red), the object reference to the Access application (appAccess) is getting destroyed, (and the database you open with your code close).
My solution was to declare the object reference to the Access application (appAccess) in the top of the form module.
Code: 
  Option Compare Database
  Option Explicit

 [B][COLOR=Red] Dim appAccess As Access.Application[/COLOR][/B]

Public Function OPENSWITCHBOARD()
  Dim strDB As String

  Const strConPathToSamples = ("H:\Access\")
  strDB = strConPathToSamples & ("Switchboard.accdb")

  Set appAccess = CreateObject("Access.Application")

  appAccess.Visible = True

  appAccess.OpenCurrentDatabase strDB
  ...
  ...  
End Function
 
You must log in or register to reply here.

Similar threads

M
Ribbon Backstage View Access 365
Replies
3
Views
259
Mister-B
M
P
No Options in File - Access Runtime 2021
Replies
10
Views
631
Pat Hartman
P
A
Solved Access Database Locking Issue
2
Replies
31
Views
1,921
Ajay289
A
P
  • Locked
Solved Ugly new Access bug
2 3
Replies
53
Views
1,298
gemma-the-husky
gemma-the-husky
E
Emmanuel Katto : How to Set Outlook to Open in HTML Format from Access Email
Replies
2
Views
328
Trevor G
Trevor G

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom