ACC: Conditionally Run AutoExec Macro in Automation Database

• Microsoft Access versions 7.0, 97

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

When you open a database in another instance of Microsoft Access using Automation code, if the database you are opening contains an AutoExec macro, the macro runs. If the macro contains a MsgBox action or opens a modal dialog box, code execution is suspended until you respond by closing the message box or the dialog box.

This article demonstrates a technique to conditionally turn off any or all AutoExec macro actions when you open a database with Automation.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

MORE INFORMATION

If there are actions in the AutoExec macro that you do not want to execute when you open the database with Automation, you can use the UserControl property in the Condition column of a macro action. The UserControl property lets you determine whether Microsoft Access is started by a user or through Automation.

Conditionally Excluding AutoExec Macro Actions

The following example shows you how to use the UserControl property of the Application object to conditionally run an action in the AutoExec macro:

1. Open the sample database Northwind.mdb.

2. In the Database window, click the Macros tab and then click New.

3. Create a new macro and save it as AutoExec:

   NOTE: On the View menu, click Conditions to display the Condition column in the macro.

          Condition                        Action
          ---------------------------------------
          Application.UserControl = True   MsgBox
   
          AutoExec Actions
          ------------------------------
          MsgBox
             Message: Opening Northwind
   
   

4. Close the Northwind database.

5. Create a new blank database called Db1.mdb.

6. Create a module and type the following procedure:

   Function TestAuto()

             Static AccObj as Object, x as String
             x = _
               "C:\Program Files\Microsoft Office\" _
                   & "Office\Samples\Northwind.mdb"
             Set AccObj = GetObject(x)
          End Function
   
   

7. To test this function, type the following line in the Debug window, and then press ENTER:

   ?TestAuto()

   Note that the Northwind database opens in a separate instance of Microsoft Access, but does not display the message box.

8. Close the Northwind database in the second instance of Microsoft Access.

9. Close the module in Db1.mdb without saving it, and then close the Db1.mdb database.

10. Open the Northwind sample database. Note that the message box appears.

Suspending Code Execution by Opening a Message Box

The following example demonstrates how code execution stops until you respond to a message box in a database opened with Automation:

1. Repeat steps 1-4 in the previous example, but do not add the Condition in the AutoExec macro.

2. Create a module in Db1.mdb and type the following procedure:

         Function TestAuto2()
            Static AccObj as Object, x as String
            x = _
              "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
            Set AccObj = GetObject(x)
            MsgBox "All Done!"
         End Function
   
   

3. To test this function, type the following line in the Debug window, and then press ENTER.

         ?TestAuto2()
   

   Note that the Northwind database opens in a separate instance of Microsoft Access, but the "All Done!" message does not display until you switch to the Northwind instance of Microsoft Access, click the OK button in the message box, and then switch back to the Db1.mdb instance of Microsoft Access.

REFERENCES

For more information about using Microsoft Access as an Automation Server, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q147816
   TITLE     : ACC: Using Microsoft Access as an Automation Server