Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

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

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

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

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

  14. Open a Form and create two Command Buttons on it.
  15. Select the first command button and display it's 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 the middle line.

    Private Sub cmdReport_Click()
    End Sub
  19. Copy the center line 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 active database.


  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.



Comments are subject to moderation before publishing.


Subscribe in a reader
Your email address:

Delivered by FeedBurner

Blog Directory Visit blogadda.com to discover Indian blogs


Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email


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

Featured Post

Call Function From MouseMove Event Property

Introduction. This is about running a User-Defined Function (say myFunction(Parameter) ) on the Mouse Move Event Property of Text Boxes.  W...


Blog Archive

Recent Posts