Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Opening External Access Report inside Active Database


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:


    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()
    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()
    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:



  1. A very interesting approach to external library.
    Assuming that I want to open the external form in a subform of my active database, how could I do this? a direct sourceobject instruction would not work.
    Any idea?

  2. I am not sure about this aspect. But, I would like to try this out.

    I am on a short visit to USA now and will be returning soon. I will check this out.


  3. Instead of creating the reference to the DatabaseB within Access, how can the reference be created at runtime for the case that we might be passing the database(s) on to someone else?


Comments subject to moderation before publishing.




Post Feed


Popular Posts

Blog Archive

Powered by Blogger.


Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code