Linking Databases

[bceo]

I have a number of databases in my system and I created a database that only has a switchboard form in it. This switchboard form has links to all of the various databases I have created and opens their default input form. This works fine but now I wish to place a button on the switchboard to run a report that is located in one of the databases.

I tried to use the hyperlink command in the switchboard database but that will not show me the information in the other databases only the switchboard database. I assume I have to link the databases and create a connection to the other database and its report, but I don’t know how to do this.

Note: the Link Tables Manager is greyed out and therefore I could not try it.

 

[Trevor G]

You would have to use some VBA to do that. which would have to open the database and run the report.

 

[bceo]

I thought as much. I am not a programmer and I have no idea how I would do it, is it possible you can give me a clue on how I would write the code.

 

[Trevor G]

In the first database use Alt + F11 to go into VBA then click the Insert Menu and select Module. Add the following code:

Sub openAnotherDB()
Dim accApp As Access.Application
Set accApp = CreateObject("Access.Application")
accApp.OpenCurrentDatabase ("c:\Access\Overtime Tracker Database.accdb")
accApp.DoCmd.OpenReport "rptEmployeelog2", acViewPreview
accApp.Visible = True
End Sub

Change the path and database name, change the report Name (both highlighted in Red). Click the Run Menu and Run to see that it works OK.

Then use Alt + F11 to go back to the Access Normal screen, add a button to a form that uses the same code. Design View of the Form and Add Command Button and then right click the properties and click the Event Tab then Click the On Click Event and click the Ellipse button and select Code Builder add the code in there. Save and use Alt + F11 again change the View of the Form to Form View Click your button and its done.

 

[bceo]

Thanks very much, I will give it a try and let you how it comes out. Once again thanks for your help.

 

[bceo]

I am sorry for taking so long to reply. I tried your script and the first part worked just fine. When I tried to set up the command button using the same script I got an error I did notice that when I set up the button on the switchboard and went to the properties and than set up the same code I received an error "Compile Error - Expected End Sub" When I check the script the following line was highlighted in yellow "Private Sub Command24_Click()"
The whole scripted looks like this:

Option Compare Database
Private Sub Command24_Click()
Sub openAnotherDB()
Dim accApp As Access.Application
Set accApp = CreateObject("Access.Application")
accApp.OpenCurrentDatabase ("c:\Test\New HQ Staff List.accdb")
accApp.DoCmd.OpenReport "Lic #Report", acViewPreview
accApp.Visible = True
End Sub

I hope this helps and once again sorry for the delay.

 

[Trevor G]

Remove this line.

Sub openAnotherDB()

 

[bceo]

Thanks Trevor, it works great now.

 

[Trevor G]

Happy to help and thanks for letting me know you know have a working solution.