Open another database from within Access

[ECEK]

Press a command button on my form and my other database opens.

What on earth could be simpler than that!!!

errr...........Any ideas?

 

[CJ_London]

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

 

[ECEK]

a separate iteration

 

[Minty]

Untested but I think

docmd.RunCommand acCmdOpenDatabase "yourdatabasefilepathgoesinhere"

will possibly work.

 

[apr pillai]

Sample Code to open a second database in different Access Application Window.

' 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

 

[ECEK]

Nearly there Its crashing saying it cant find the database and highlighting this line
appAccess.OpenCurrentDatabase strDB

This is my 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

 

[apr pillai]

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

Code in the second line have problems. Correct it as follows and run again:

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

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

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.

 

[ECEK]

Hi there Apr pillai

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

If I remove this line

' 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:

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

But once again , switchboard opens and closes immediately.

Could you advise further?

 

[JHB]

But once again , switchboard opens and closes immediately.

Could you advise further?

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.

 

[ECEK]

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.

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

 

[ECEK]

Found it !! Yay

Command button with this:

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

 

[JHB]

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.

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]

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.

  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