Access VBA Macro Programming

Using the object models and a programming language such as Visual Basic, it’s not

difficult to develop your own Access front end with exactly the same functionality as the

Microsoft Access front end. Every menu command and function key on the Microsoft Access

l front end is represented within the object models. This is not to say that these are the exact

objects that Access uses itself, but Microsoft has empowered you with all the objects and

methods for you to do anything in code that can be done from the Access menu, and as you

will find out in the practical examples later in this book, a whole lot more besides.

Strangely enough, if you decided to write your own front end, there would be relatively

little code to write because all the functionality is contained in the object model. Below the

object models sits the data services layer, which holds the data in the tables and is modified

by commands from the object models.

The object models contain a large number of objects—for example,CurrentDb,TablesDef,

QueryDef,Forms, andReports. These objects are discrete entities that offer various pieces

of data analysis functionality. Most important, they can be controlled from your code.

When programming in Access using VBA, you use standard VBA commands and functions

such as For..Next, If..Then..Else, and MsgBox, but you use the object model to communicate

with the Access application by manipulating the properties and methods of the various objects

at your disposal, such as theTableDefobject or theQueryDefobject.

An object is a programming structure encapsulating both data and functionality that is

defined and allocated as a single unit and for which the only public access is through the

programming structure’s interfaces.

An object is a part of the Access program. The objects are arranged in a hierarchy. For

example, at the top of the Access object model is theApplicationobject, which is Access

itself. For example, under theApplicationobject is theCurrentDbobject, and within the

CurrentDbobject areTableDefobjects. Within eachTableDefobject areFieldobjects,

and so on.

Each object can contain settings, called properties, and actions that can be performed on

the object, called methods. For example, if you want to view or edit the SQL for a specific

query, you can look at the SQL property of a particular query within theQueryDefscollection.

An example might be

MsgBox CurrentDb.QueryDefs("MyQuery").SQL

You can also write to some of the properties (if they are not read-only). In this way, you

can change a query definition:

CurrentDb.QueryDefs("MyQuery").SQL = "select * from MyTable"

Properties and Methods Explained

All objects in the object models have properties or methods or both. Some have very few; others

have many, depending on the complexity of the object. Apropertyis a scalar attribute that defines

various parameters of an object. An example is theVisibleproperty of an open form, which

can beTrue (–1)orFalse (0)and dictates whether the form is visible to the user or hidden. This

is done here using built-in constants for True or False (numeric values are in parentheses).

176 Microsoft Access 2010 VBA Macro Programming