- database solutions and downloads for microsoft access

Microsoft Access OleDB Connection

How to create a Microsoft Access macro that opens/runs a query:

When creating databases for end users, the goal is to make the database user-friendly and intuitive. This can be done with the use of macros which are used to automate tasks within the database. This is handy for both the administrator(s) and the end-user(s). It allows the administrator the ability to "hand-off" the day-to-day operations of the database to others. The end-users do not need to be programmatically familiar with the database in order to use it. Macros and other features within Access allow this type of flexibility.

This article provides step-by-step details regarding how to automate the opening and running of a query through the creation of a macro. By opening a query, it also runs it. At the conclusion of the "how-to" will be a macro that will open a query based upon the administrator’s desired outcome. Each time the macro is run, the query will run also. For example, if the macro opens the query in design view, each time the macro is run, the query will open in the "Design" view. So, anyone that runs this macro will be able to change any design aspect of the query such as the field names, the fields that appear, etc.


  1. Select the "Macros" option of the "Objects" menu to open the macros view.
  2. Double-click "New" in the icon menu to create a new macro.
  3. Go to the "Action" field and click the drop-down arrow.
  4. Select "OpenQuery" or type "OpenQuery" into the field.
  5. Press the "F6" key to access the "Action Arguments" panel or click in the "Query Name" field in the "Action Arguments" panel.
  6. Type the name of the query or use the drop-down arrow to access the desired query. Remember, by right-clicking, the "Zoom…" option will appear. This will allow full viewing of the name of the query.
  7. Click in the "View" field. This field gives the option of opening the query using several views. Keep the end-user in mind when selecting how the query will appear when the macro is run. The available views are:
    • Datasheet: when the query opens it looks like a spreadsheet
    • Design: the query will open in the query design view. Changes can be made to the query such as field names, etc.
    • Print Preview: the query opens as it will appear if it is to be printed, similar to the "Print Preview" mode in other Microsoft Office applications.
    • PivotTable: the query will open as a pivot table, similar to the pivot tables that are created in Excel.
    • PivotChart: the query will open as a graphical pivot chart, again, similar to the pivot charts that are created in Excel.
  8. Click in the "Data Mode" field. Keep the end-user and the purpose of the database in mind when selecting what will be done with the data. There are three options available in the "Data Mode" field:
    • Add: the end-user will be able to add records to the query.
    • Edit: the end-user will be able to edit the records returned by the query and/or filter the data that is returned by the query.
    • Read Only: the end-user will be able to only view the returned records.
  9. Select the "Data Mode" type depending on how the data should be displayed to the end-user.
  10. Name and save the macro by clicking the disk icon in the upper left-hand corner or by going to "File", "Save" in the window’s menu.

In order to test the macro, press the exclamation point in the icon menu. To step through each action of the macro, select the icon to the right of the exclamation point (indented block paragraph with the arrow).

Any type of query can be embedded into a macro. The "OpenQuery" option will activate any query including action queries which are primarily append, delete, make table, and update queries. It is also possible to use the "OpenQuery" option as many times as needed within a macro, to get to the desired outcome.