Opening External Access Report inside Active Database
Last week we have learned how to print a Report from another Ms-Access Database. With the help of a VBA Program we have created a separate MS-Access Application Window, opened a database within that Application Window and printed a Report from it. We have used the Application.Docmd.OpenReport command to open the Report in Print Preview. The same code we have used in an Excel Application (in a Macro enabled Workbook) to Print Preview the same MS-Access Report.
The procedure was somewhat complicated and difficult to understand, if you are not that familiar with VBA. If you need to print a Report or open a Form from an external MS-Access database, inside the current database then we can do it in a much simpler way.
Once we go through the following simple procedure the external database's Report can be opened within the current database window in Print Preview or Print mode, depending on how you set up the procedure. With the same ease of operation we can open Forms as well.
The procedure goes something like the following:
First let us define some names of databases, Report and Form involved in this procedure, for references.
- Current Database Name: DatabaseA.mdb
- Second Database Name: DatabaseB.mdb
- Report to Print Preview from DatabaseB.mdb: myReport.
- Form to open from DatabaseB.mdb: myForm.
- Open DatabaseB.mdb (you may select any database you have, with at least one Report and One Form).
- Open the VBA Editing Window (ALT+F11).
- Insert a new Standard Module (Insert – – > Module).
- Copy and paste the following VBA Code into the Module:
Public Function myReportOpen() 'Replace myReport with your own Report name in quotes. DoCmd.OpenReport "myReport", acViewPreview End Function
Calling the above VBA Function from DatabaseA.mdb will open myReport from DatabaseB.mdb and will appear in DatabaseA Application Window, in Print Preview.
- Copy and Paste the following VBA Code below the earlier Function myReportOpen():
Public Function myFormOpen() 'Replace myForm with your own Form Name DoCmd.OpenForm "myForm", acViewNormal End Function
We have created two simple functions in DatabaseB and we have to ensure that DatabaseA is not having any function with the same name.
- Save the VBA Module with the functions and close DatabaseB.mdb.
- Open DatabaseA (any database you would like to see the Report/Form open from DatabaseB.mdb).
- Open VBA Editing Window (ALT+F11).
- Select Tools – – > References.
You will see the VBA References Dialog Box as shown below, with a lengthy list of available Library Files and the files already selected for the current project, with check marks, on top of the list. You must browse and find DatabaseB.mdb from it’s parent folder, select it and click Open to attach it to the current database as a Library File.
- Use Browse… button on the References Dialog Box, find DatabaseB.mdb from it’s folder and open it. See that you have selected ‘Microsoft Access Databases’ in the Files of Type control, otherwise Database file names will not appear.
The selected Database’s Project Name (can be different from your database name) will appear at the bottom of the Library Files list.
- Click OK to close the Dialog Box.
Now, the functions myReportOpen() and myFormOpen() of DatabaseB are visible in DatabaseA. It means that you can call those functions from DatabaseA.mdb to open myReport or myForm from DatabaseB.mdb and to display them in DatabaseA Window.
Note: When Function myReportOpen() is called from DatabaseA.mdb, first the function will look for myReport in DatabaseB (the function’s parent database), if found it opens from there and displays in the current database, otherwise it will search for the Report, with the same name, in DatabaseA.mdb and opens from there.
Keep this particular aspect in mind about library functions and it’s ability of searching for objects within the library file first, if reference of objects appears in the library functions, when you design Custom Wizards or some common Form useful in several Databases.
At this point you can test the programs by running it from the Debug Window.
- Press CTRL+G to display the Debug window (Immediate Window), if it is not already visible.
- Type the following Command in the Debug Window and press Enter Key:
The Report is open in Print Preview mode behind the VBA Window, minimize the VBA Window to see the Report. You may run myFormOpen() function also to see that it works too. You may create two Command Buttons on a Form so that you can run the programs from the Command Button Click Event Procedure.
- Open a Form and create two Command Buttons on it.
- Select the first command button and display it’s Property Sheet (F4).
- Change the Name Property value to cmdReport.
- Find the On Click Event property and select [Event Procedure] from the drop-down list.
- Click on the build button (. . .) at the right end of the property to open the VBA Window.
You will find the opening and closing lines of a Sub-routine, similar to the program lines given below, except the middle line.
Private Sub cmdReport_Click() myReportOpen End Sub
- Copy the center line of the above procedure and paste it into the middle of the VBA subroutine.
- Similarly, name the second Command Button as cmdForm and follow the same steps (16 to 19) to create the following Sub-Routine in the VBA Window.
Private Sub cmdForm_Click() myFormOpen End Sub
- Save the Form.
- Open the Form in normal view, click on the Command Button(s) to open myReport/myForm from DatabaseB.mdb. The user of your database will not know whether the Report is opening from the library database or from active database.