Opening External Access Report inside Active Database

Introduction.

Last week we 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.

Simple Preparations.

The procedure goes something like the following:

First, let us define some names of databases, reports, and Forms 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.

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 we have to ensure that DatabaseA is not having any function 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 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 (which can 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 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 its ability to search for objects within the library file first, if references of objects appear 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 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 is open in Print Preview mode behind the VBA Window, minimize the VBA Window to see the Report. You may run the 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.

    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 Sub-routine, 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 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

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 specific Page of PDF File
• Calculating Time Difference
• Centralized Error-handler & Error- log
• Duplicating fields with Conditional Formatting