databasedev.co.uk - database solutions and downloads for microsoft access

Deciding when to use Visual Basic for Applications

In a Microsoft Access database application, code runs only in the context of an object and its associated event, such as changing data in a field or clicking the mouse on an object. Visual Basic for Applications code has no purpose until you build objects in the database. You may find that in your database application you are not required to write any code, or that you only need to write the minimal amount of code.

You may write VBA code to:

• Create user-defined functions that substitute for complex expressions. You can use functions to perform operations that can't be accomplished with Microsoft Access Macros.
• Display custom error messages to help database application users determine what they have done incorrectly.
• Perform actions at system level, outside of Microsoft Access. For example, you can use VBA to initiate dynamic data exchange (DDE) interchanges with other Windows-based applications.
• Step through and perform operations on one record at a time.
• Pass arguments to your code when it is executed, and use variables for arguments.
• Streamline the maintenance of your database application. The event driven code you write is stored with the form's or report's definition; so if you import or export the form or report to another application, the code moves with it.

Determining when to use a Microsoft Access Macro instead of VBA Code

You must use macros for the following application development tasks:

• Assigning a set of actions to a specific key or key combination. Each set of actions is saved in a macro in a special AutoKeys macro group.
• Running your own macro or procedure from a toolbar button.

Before you decide on using a macro or writing VBA code, you may want to check out the List Of Macro actions and the Actions Reference found in the Microsoft Access Help under the search topic Actions: Reference Topics. You can also check out Access 2003 VBA Language Reference online. In VBA, you can use the DoCmd object with the appropriate method to create a statement that runs a macro action. You might find that the power inherent in macros is sufficient to run your application; therefore, you might not need to write any code, or only write a minimal amount of VBA code.

You can write VBA code in either a Sub procedure or a Function procedure. A Sub procedure performs actions, whereas a Function procedure performs actions and returns a value. You can store code in either private form or report modules, which are stored with the form or report design, or in standard modules (public modules by default), which are created and stored as separate database objects. You can execute the procedures in a module in response to events, or you can call the procedures from expressions, macros, menu commands or other procedures.

In event-driven applications such as Microsoft Access, your code responds to a user action or system event, either of which executes an event procedure. For example, you can design your application so that an event procedure runs when a form opens or when the database user clicks a command button. An event property can execute a macro, an event procedure, or code.