Special Edition Using Microsoft Office Access 2003

In the Real World Macro Schizophrenia

Macros have been a common add-on to productivity applications since the early days of WordPerfect, Lotus 1-2-3, and other popular DOS word processing and spreadsheet applications. Each application took a different approach to automating repetitive operations, which resulted in a Tower of Macro Language Babel. WordPerfect 4.x and 5.x for DOS, in particular, had an arcane set of macro commands and peculiar program structure that frustrated thousands of erstwhile programmers.

About 12 years ago, Bill Gates decided that all Microsoft applications using macros would share a common macro language built on BASIC. BASIC is the acronym for Beginners All-Purpose Symbolic Instruction Code, an interpreted language developed at Dartmouth College. The intended application for BASIC was programming on terminal-based (usually Teletype) time-sharing computers. Gates' choice of BASIC for a macro language isn't surprising when you consider that Microsoft Corporation was built on the foundation of Gates' BASIC interpreter that ran in the 8KB (not MB) of RAM common to the early predecessors of the PC, such as the Altair microcomputer. Gates reiterated his desire for a common macro language in an article that appeared in a late 1991 issue of the now-extinct One-to-One with Microsoft magazine.

Note

A few years ago, Bill Gates gave a speech in which he mentioned a subscription model for licensing productivity software, thereby creating an "Office Annuity" for Microsoft Corporation. Microsoft abandoned the subscription model proposed for Office XP. But Microsoft's new Software Assurance and Licensing 6.0 terms, which most analysts and IT managers contend increases software costs substantially, is tantamount to a "Windows Annuity" for Office, operating systems, and server products. John Connors, Microsoft's chief financial officer, stated on January 24, 2003, that the company plans to establish a "financing group," presumably to deliver "easy monthly license payments" for small- and medium-size businesses.

Prior to Access 1.0, the only Microsoft application with a BASIC-like macro language was Word. WordBASIC, later Word Basic, was far more versatile, easy to understand, and useful than the competitors' pidgin-like languages. Access 1.0 offered Embedded Basic (EB) later to become Access Basic as its programming language. Apparently Microsoft believed that Access Basic would be incomprehensible to average Access users, so Microsoft's product team tacked on a simplified macro language. Thus, Access became saddled with two "macro" languages.

Visual Basic 3.0 was the most popular Windows programming tool by the time Microsoft released Access 1.0. Visual Basic, Access, and Word each had their own Basic flavor. Microsoft touted Visual Basic as a programming language, while Access Basic and Word Basic retained the macro terminology. Excel was the next Microsoft application to gain Basic as a macro language, this time in the guise of Visual Basic, Applications Edition, also known as Visual Basic for Applications. Microsoft's goal was to unify all three Visual Basic dialects under the VBA umbrella. Microsoft finally achieved Gates' objective with the release of Visual Basic 6.0 and Office 2000. There's a common aphorism that "after a few releases, Microsoft usually gets it right." But 10 years is a long time, even by Microsoft standards.

Macro and script traditionally have been synonyms for code that automates operations in productivity applications, but Visual Basic, Scripting Edition, also known as VBScript, ends that tradition. VBScript, a lightweight variant of VBA designed to compete with Netscape's JavaScript, appears primarily in Web-based applications. VBScript also can be used to automate repetitive operating system activities when run under the Windows Scripting Host (WSH). Access 2003 includes the Microsoft Script Editor described in Chapter 24, "Designing and Deploying Data Access Pages" to aid in adding VBScript subprocedures to DAP. So Access 2003 now offers macro, script, and programming languages.

Calling VBA a macro language is undeserved damnation by faint praise. VBA is a true programming language and, because of its integration with Microsoft Office, is undoubtedly the most widely used of all programming languages including C/C++, COBOL, and Java. VBA is easier to learn than Java and is an order of magnitude less difficult to master than C++ or COBOL. Although VBA doesn't qualify as a truly object-oriented (OO) programming language it lacks inheritance and some other OO niceties VBA is sufficiently object-enabled to handle virtually all common database-related programming chores. Visual Basic. NET adds the missing OO features that developers have been requesting since the release of Visual Basic 1.0.

After you gain experience with VBA in Access, you can leverage your programming skills in Visual Basic 6.0 or Visual Basic .NET. A good foundation in VBA makes the transition to VBScript a snap, but mastering Visual Basic .NET involves a steep learning curve. Even if you're an accomplished Access macro writer, use Access's Macro converter to automate the process of moving to VBA. Click Tools, Macros, and then choose Convert Macros to Visual Basic, Create Menu from Macro, Create Toolbar from Macro, or Create Shortcut Menu from Macro to bring your existing applications to current Access development standards.