Opening External Access Report inside Active Database

Introduction.

Last week, we explored how to print a Report from another MS Access database. Using a VBA program, we created a separate MS Access Application Window, opened the external database within that window, and printed a report from it. To accomplish this, we used the Application.DoCmd.OpenReport command to open the report in Print Preview. The same code was also used within Excel (in a macro-enabled workbook) to preview the same MS Access report.

However, the procedure was somewhat complicated and not easy to follow—especially if you are not very familiar with VBA.

Fortunately, if you only need to print a report or open a Form from an external MS Access database within your current database, there is a much simpler way.

By following the procedure explained below, you can open a report from another database directly in your current database window, either in Print Preview or Print mode, depending on how you configured it. With the same approach, you can also open forms just as easily.

Simple Preparations.

The procedure goes something like the following:

First, let us define the names of databases, reports, and Forms involved in this procedure, for reference.

• 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.

1. Open DatabaseB.mdb (you may select any database you have, with at least one Report and one Form).

2. Open the VBA Editing Window (ALT+F11).

3. Insert a new Standard Module (Insert -> Module).

4. 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.

5. 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 ensured that DatabaseA does not have functions with the same name.

6. Save the VBA Module with the functions and close DatabaseB.mdb.

7. Open DatabaseA (any database you would like to see the Report/Form open from DatabaseB.mdb).

8. Open VBA Editing Window (ALT+F11).

   The Reference Library Usage.

9. 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 checkmarks, on top of the list.  You must browse and find DatabaseB.mdb from its parent folder, select it, and click Open to attach it to the current database as a Library File.

10. Use the Browse... button on the References Dialog Box, find DatabaseB.mdb from its 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 (must be different from your database name) will appear at the bottom of the Library Files list.

11. 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 display them in DatabaseA Window.

    How It Works.

    Note: When the Function myReportOpen() is called from DatabaseA.mdb, it will first check for myReport in DatabaseB (the parent database of the function). If the report exists there, it will open from 'DatabaseB' and display in the current database. If not, the function will then search for the report with the same name in DatabaseA.mdb and open it from there.

    Keep this behavior in mind when working with library functions. By design, they look for referenced objects in the library file first. This feature can be useful when you are creating custom wizards or designing shared forms that need to work seamlessly across multiple databases.

    At this point, you can test the programs by running them from the Debug Window.

12. Press CTRL+G to display the Debug Window (Immediate Window) if it is not already visible.

13. Type the following command in the Debug Window and press Enter Key:

    myReportOpen

    The report will open in Print Preview mode behind the VBA window. To view it, simply minimize the VBA window. You can also test the myFormOpen() function to confirm that it works in the same way. For convenience, consider creating two command buttons on a form and assigning these functions to their Click event procedures, so you can run them directly from the form without opening the VBA editor each time.

    Opening Form/Report of DatabaseB from DatabaseA

14. Open a form and create two Command Buttons on it.

15. Select the first command button and display its Property Sheet (F4).

16. Change the Name Property value to cmdReport.

17. Find the On Click Event property and select [Event Procedure] from the drop-down list.

18. 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 Subroutine, similar to the program lines given below, except for the middle line.

    Private Sub cmdReport_Click()
       myReportOpen
    End Sub

19. Copy the centerline of the above procedure and paste it into the middle of the VBA subroutine.

20. Similarly, name the second Command Button to 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

21. Save the Form.

22. 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 the active database.

Earlier Post Link References:

• DOS Commands in VBA
• Opening a specific Page of a PDF File
• Calculating Time Difference
• Centralized Error-handler & Error-log
• Duplicating fields with Conditional Formatting