Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources] نسخه متنی

VBA Explained

VBA is the development language for Microsoft Access 2003. It offers a consistent language for application development in the Microsoft Office suite. The core language, its constructs, and the environment are the same in Microsoft Access 2003, Microsoft Visual Basic 6.0 and earlier, Microsoft Excel, Microsoft Word, Microsoft Outlook (for application-wide programming), and Microsoft Project. What differs among these environments are the built-in objects specific to each application. For example, Access has a CurrentProject object, but Excel has a Workbook object. Each application's objects have appropriate properties (attributes) and methods (actions)and, in some cases, eventsassociated with them. This chapter gives you an overview of the VBA language and its constructs.

Unlike macros in Word or Excel, Access macros are not subprocedures in modules; instead, they are a different type of database object, with their own interface. Because of this, you can't use Access macros to learn to program in VBA, as you can by recording a Word or Excel macro and then examining its VBA code. You can write simple Access applications by using macros. Although macros are okay for quick prototyping and very basic application development, you will do most serious Access development by using the VBA language. Unlike macros, VBA gives you the ability to do the following:

• Work with complex logic structures (case statements, loops, and so on)

• Use constants and variables

• Take advantage of functions and actions not available in macros

• Loop through and perform actions on recordsets

• Perform transaction processing

• Create database objects programmatically and work with them

• Implement error handling

• Create libraries of user-defined functions

• Call Windows API functions

• Perform complex Dynamic Data Exchange (DDE) and object linking and embedding (OLE) automation commands

The VBA language enables you to use complex logic structures. Macros let you perform only simple If…Then…Else logic, but the VBA language offers a wealth of logic and looping constructs, which are covered later in this chapter.

The VBA language also lets you declare and work with variables and constants. You can scope these variables appropriately and pass them as parameters to subroutines and functions. As you will see later in this chapter, variables and constants are integral parts of any Access application.

If you try to develop an application using only macros, you can't take advantage of many of the rich features available in the VBA language. In addition, many of the actions available in both macros and modules can be performed much more efficiently with VBA code.

Complex Access applications often require you to loop through a recordset, performing some action on each member of the set. There's no way to do this using Access macros. However, with the VBA language and ActiveX Data Objects (ADO), you can add, delete, update, and manipulate data. Chapter 14, "What Are ActiveX Data Objects and Data Access Objects, and Why Are They Important?" covers the details of ADO.

When manipulating sets of records, you want to ensure that all processing finishes successfully before Jet permanently updates your data. Macros don't enable you to protect your data with transaction processing. Using the BeginTrans, CommitTrans, and Rollback methods, you can make sure that Jet updates your data only if all parts of a transaction finish successfully. Transaction processing, if done properly, can substantially improve your application's performance because no data is written to disk until the process is finished. Transaction processing and its benefits are covered in

Alison Balter's Mastering Access 2002 Enterprise Development .Chapter 14). When users are able to build queries on the fly, for example, you might want to give them the capability to design a query by using a front-end form that you provide. You can also enable users to store the query so that they can run it again later.

Access macros don't allow you to implement error handling. If an error occurs while an Access macro is executing in Access's runtime version, the user is exited out of the application and is returned to the Windows desktop. By using error-handling techniques, you can determine exactly what will happen when an error occurs during the execution of your application. Error handling is covered in more depth in Chapter 16, "Error Handling: Preparing for the Inevitable."

VBA also makes it easier for you to write code libraries of reusable functions, design and debug complex processes, and even write your own add-ins. If you're developing even moderately complex applications, you want to be able to create generic function libraries that can be used with all your Access applications. It's extremely difficult, if not impossible, to do this using macros.Chapter 23, "Exploiting the Power of the Windows API," covers the Windows API.

Both DDE and Automation technology enable you to communicate between your Access applications and other applications. Although DDE is an older technology than Automation, it's still used to communicate with a few applications that don't support Automation. Automation is used to control Automation server applications, such as Excel and Project, and their objects (all Microsoft Office applications are Automation servers). Automation is covered in Chapter 22, "Automation: Communicating with Other Applications."

Although macros can offer a quick fix to a simple problem, their limitations make the VBA language the only real option for developing complex solutions. To make the transition from macros to modules easier, Microsoft has given you a feature that enables you to convert any macro to VBA codea Save As menu option available when saving an existing macro.