Microsoft Access Programmer Roanoke, Virginia

Macros aren't always the best tools for creating code that controls industrial-strength applications because they're limited in some functionality. Access macros are limited in the following ways:

• You can't create user-defined functions by using macros.
• Access macros don't allow you to pass parameters.
• Access macros provide no method of processing table records one at a time.
• When using Access macros, you can't use object linking and embedding automation to communicate with other applications.
• Debugging Access macros is more difficult than debugging VBA code.
• Transaction processing can't be done with Access macros.
• You can't call Windows API functions by using Access macros.
• Access macros don't allow you to create database objects at runtime.

Converting a Macro to VBA Code

Sometimes you will create a macro, later to discover that you want to convert it to VBA code. Fortunately, Access 2007 comes to the rescue. You can easily convert an Access macro to VBA code; after the macro has been converted to VBA code, the code can be modified just like any VBA module. Follow these six steps to convert an Access macro to VBA code:

1. Open the macro you want to convert in Design view.
2. Click the Microsoft Office Access button and select Save As, Save Object As.
3. Click the As drop-down and select Module.
4. Click OK; this opens the Convert Macro dialog box.
5. Indicate whether you want to add error handling and comments to the generated code; then click Convert.
6. After you get an indication that the conversion is finished, click OK. Access places you in the Visual Basic Editor (VBE).
7. The converted macro appears under the list of modules with Converted Macro: followed by the name of the macro. Click Design to view the results of the conversion.

CAUTION

When you convert a macro to a Visual Basic module, the original macro remains untouched. Furthermore, all the objects in your application will still call the macro. To effectively use the macro conversion options, you must find all the places where the macro was called and replace the macro references with calls to the VBA function.

Creating An AutoExec Macro

With Access 2007, you can use either an AutoExec macro or Startup options to determine what occurs when a database is opened. Using an AutoExec macro to launch the processing of your application is certainly a viable option.

Creating an AutoExec macro is quite simple; it's just a normal macro saved with the name AutoExec. An AutoExec macro usually performs tasks such as hiding or minimizing the Navigation Pane and opening a Startup form or switchboard. The macro shown in Figure 7.28 hides the Navigation Pane, displays a welcome message, and opens the frmClients form.

TIP

When you're opening your own database to make changes or additions to the application, you probably won't want the AutoExec macro to execute. To prevent it from executing, hold down your Shift key as you open the database.

Author Attribution

The preceding information originally appeared in Alison Balter's book Mastering Microsoft Office Access 2007 Development and is reprinted here with the author's permission.

Expert Microsoft Access Programmer In Roanoke, VA

When you need a Microsoft Access programmer for your Roanoke, Virginia, business, call MS Access Solutions at (323) 285-0939. We have over 25 years experience in Microsoft Access programmer solutions. We create Access database applications for all business types, consisting of hospitals and medical practices, local, state, and federal government agencies, the U.S. military, universities, junior colleges, and school districts, agriculture, employment agencies and human resources departments, oil and gas companies, and insurance companies and insurance agencies.

Read the next section: "Microsoft Access Programming Solutions" for a more discussion about our services.

Get more information about our programmer services on the Microsoft Access Programmer Tacoma, Washington web page.