Microsoft Access Programmer Madison, Wisconsin

Microsoft Access VBA & Macros

Call MS Access Solutions at (323) 285-0939 for your FREE consultation.

The material below originally appeared in Alison Balter's book Mastering Microsoft Office Access 2007 Development and is reprinted here with the author's permission. There may be references to "Figures" or "Chapters" that are not reprintable and are not used on this page.

The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This chapter introduces you to the VBA language. It serves as a foundation for the remainder of the book. After reading this chapter, you will be familiar with the development environment. You will know how to declare variables, use control structures, pass and return parameters, work with built-in functions, and more.

Microsoft Access VBA Explained

VBA is the development language for Microsoft Access 2007. 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 2007, 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, events—associated with them. This chapter gives you an overview of the VBA language and its constructs.

Microsoft Access Macros & VBA

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 some Access 2007 applications by using macros. Although macros are okay for relatively basic application development, you will do most serious Access development by using the VBA language. Unlike macros, VBA enables you to do the following:

• Work with complex logic structures (case statements, loops, and so on).
• 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.
• Create libraries of user-defined functions.
• Call Windows API functions.
• Perform complex 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.

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 15, "What Are ActiveX Data Objects, and Why Are They Important?" covers the details of ADO.

---

Author Attribution

The preceding material 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 Madison, WI

Need a Microsoft Access programmer in Madison, Wisconsin? Call MS Access Solutions at (323) 285-0939. We've been building Access database applications for over 25 years. We've worked with hospitals, universities, federal and state agencies, the U.S. military, agriculture operations, HR departments, employment agencies, oil and gas firms, insurance providers — just about every sector that needs serious data management.

It doesn't matter if you're starting fresh or trying to salvage an old system; we can handle it. We work on everything from quick fixes to large, enterprise level builds. If you're running a standalone Access setup or a SQL Server backend with Access as the front-end, we've got it covered. We've solved messy architecture problems, improved clunky user interfaces, and tuned slow systems to run right.

We also handle smaller, day-to-day tasks. Broken Access forms, reports that crash or won't run, macros that act up, VBA code that throws errors - we deal with that all the time. If your database isn't behaving, we'll dig in, find the problem, and fix it properly.

---

Find out more about our programming services on the: Microsoft Access Programmer Milwaukee, Wisconsin web page.