To create a module where you can write VBA code, open your database and click the Ribbon's Database Tools tab. Click the Visual Basic button on the Ribbon. (Or, simply press Alt+F11).

The VBA Editor opens. Choose Insert

After creating a VBA module in the VBA Editor, you can create a function within it as described earlier in the book. The following example creates a function named Standard_Setup that simply displays a message box to indicate that it is running (the next section uses this macro as an example):

Public Function Standard_Setup()
    'put your choice of commands here
    MsgBox "The Standard_Setup macro is running."
End Function

You can test this by pressing F5 after typing it into the VBA Editor.

After creating the function, switch back to Access by pressing Alt+F11 or clicking the View Microsoft Office Access button on the far left of the Standard toolbar in the VBA Editor. Of course, you can also use the traditional Alt+Tab shortcut.

Although the chapters on Access in this book focus on automating Access via the more flexible and powerful VBA language, you may also be interested to know in what ways Access's legacy Macro Builder utility has been redesigned in Access 2010, so I'll detail that here. Renamed Macro Designer, it has been improved primarily by adding some programming capabilities that were previously missing from it and by upgrading its editor. The new editor is far less sophisticated than the VBA Editor but will nonetheless be welcomed by those who want to work with Access-style macros.

What's new? Here are the main enhancements:

If you're interested in learning more about the Macro Designer, see the tutorial on this web page:

http://blogs.msdn.com/access/archive/2009/07/28/meet-the-access-2010-macro-designer.aspx

Recall that you can't directly trigger a VBA procedure interactively from the main Access interface. You'll find no Macros dialog box like the one in Word and other Office 2010 applications. True, there is a Run Macro button on the Database Tools tab of the Access Ribbon, but this feature cannot directly trigger a VBA procedure. (It triggers an Access-style "macro.")

To run a VBA procedure, you have to create an Access-style macro that was built using Access's Macro Designer. You use the RunCode action (command) to call the VBA procedure. We'll see how to do that now:

You can also execute Access-style macros when the Macro Designer is closed. Just double-click test in the All Access Objects list. Or click the Database Tools tab of the Ribbon, then click the Run Macro icon on the far left of the Ribbon (it too has a red exclamation point). Access's Run Macro dialog box opens. Select test as the macro name you want to run, then click OK to close the dialog box and execute your macro.

Figure 28.1. Use the Macro Designer window to create a new macro in Access.

To set up preconditions for an Access session, you can use an AutoExec macro. When Access starts running, it checks to see if there is a macro named AutoExec. If so, that macro is executed (runs) automatically when Access opens. For example, you might choose to maximize the application window, open a particular item (for example, a table), or display a particular record. Note that AutoExec must be the name of an Access-style macro, not a VBA procedure.

By the way, you can prevent an AutoExec macro from running when you open a database by holding down the Shift key while the database opens.

To create an AutoExec macro, start a new macro as described in the previous section, add to it the actions that you want the macro to perform, and save it with the special reserved name AutoExec. The macro then runs the next time you open the database.

There's no good reason to create Access VBA code in a subprocedure rather than in a function because a Sub cannot be triggered directly by an Access-style (Designer) macro. Only functions can be directly triggered, as the example in the previous section illustrated. If you feel you must create a Sub, the only way to execute it is to create a function that runs the subprocedure. So what is the point?

This additional indirection is clumsy, but it can be made to work if for some unimaginable reason you want to use a Sub procedure. Here is a simple example:

When you launch the VBA Editor in Access (by pressing Alt+F11 or clicking the Visual Basic button on the Ribbon's Database Tools tab) and then insert a code module, you'll notice that Access automatically enters an Option Compare Database statement in the General Declarations area of the Code window.

As an aside, recall that if you've selected the Require Variable Declaration check box on the Editor tab of the VBA Editor Options dialog box (Tools

Access supports three different ways of comparing text strings: Option Compare Database, Option Compare Binary, and Option Compare Text. Here's what these options mean:

To work with the database that's currently open in Access, use the CurrentDb method on the Application object or an object variable representing the Application object. The CurrentDb method returns a Database object variable representing the currently open database that has the focus.

The following example declares an object variable of the Database type named myDatabase and then uses the CurrentDb method to assign the active database to it:

Dim myDatabase As Database
Set myDatabase = Application.CurrentDb

In Access, you can choose from among several ways of opening and closing a database. This section discusses the simplest method of opening and closing a database—by treating it as the current database. This method is similar to opening and closing a database when working interactively in Access. See the next section for another method of opening and closing databases that lets you have two or more databases open at the same time.

To open a database as the current database, use the OpenCurrentDatabase method of the Application object. The syntax is as follows:

expression.OpenCurrentDatabase(Filepath, Exclusive, bstrPassword)

Here are the components of the syntax:

To close the current database, use the CloseCurrentDatabase method with the Application object. This method takes no arguments.

You can run the CloseCurrentDatabase method from the current database, but you can't do anything after that because the code stops after VBA executes the CloseCurrentDatabase method and the database containing the code closes. To close the current database and open another by using the OpenCurrentDatabase method, you must run the code from outside the databases involved—for example, by using automation from another application. Chapter 30, "Accessing One Application from Another Application," describes this technique.

filepath:="C:TempNorthwind.accdb"

There's an additional requirement when you're writing code that communicates between Office applications. You can't simply declare an object variable to point to an application object, like this:

Dim myAccess As Access.Application

This code will run only if you first provide a reference in the host application. For example, if you're trying to manipulate Access from VBA code within a Word macro, you need to set a reference in Word's VBA Editor.

The following example illustrates a way to contact and manipulate Access from another VBA host—for example, from Excel or from Word. But before you can execute this code from Word or some other application, you must first choose Tools

This next example declares the object variable myAccess as the Access.Application type and the object variable myDatabase as the Object type. The example uses the GetObject method to assign to myAccess the copy of Access that's running, uses the CloseCurrentDatabase method to close the open database, and then uses the OpenCurrentDatabase method to open another database in Exclusive mode. The final statement uses the CurrentDb method to assign the open database to the myDatabase object variable:

Dim myAccess As Access.Application
Dim myDatabase As Object

Set myAccess = GetObject(, "Access.Application")
myAccess.CloseCurrentDatabase
myAccess.OpenCurrentDatabase _
    filepath:="C:TempNorthwind.accdb", Exclusive:=True
Set myDatabase = myAccess.CurrentDb

When you test this code by executing it in the Word VBA Editor, you'll know it works because whatever database was open in Access will be replaced by Northwind. Also note that when running this code, you might get an error message saying "User-defined type not defined." And the editor will highlight this line of code:

Dim myAccess As Access.Application

This means that the editor can't locate the object named Access. For reasons unknown, a newly added library is sometimes deselected in References. To fix this problem, just repeat the steps described previously to use Tools

Instead of using the OpenCurrentDatabase method to open a database as the current database, you can use the OpenDatabase method of the Workspace object to open another database and return a reference to the Database object representing it. The syntax for the OpenDatabase method is as follows:

Set database = workspace.OpenDatabase (Name, Options, ReadOnly, Connect)

Here are the components of the syntax:

The following example declares a Workspace object variable named myWorkspace and a Database object variable named myDatabase, assigns to myWorkspace the first Workspace object in the Workspaces collection (the default workspace), and assigns to myDatabase the database Northwind.accdb, which it opens in Exclusive mode with read/write access. You can try this by entering this code in a module in the Access VBA Editor:

Sub test()
Dim myWorkspace As Workspace
Dim myDatabase As Database
Set myWorkspace = DBEngine.Workspaces(0)

Set myDatabase = myWorkspace.OpenDatabase _
    (Name:="C:	emp
orthwind.accdb", _
    Options:=True, ReadOnly:=False)

End Sub

To close a database that you've opened by using the OpenDatabase method, use the Close method of the object variable to which you've assigned the database. For example, the following statement closes the database assigned to the object variable myDatabase:

myDatabase.Close

To keep different databases in separate sessions, you can create a new workspace as needed and remove it when you have finished working with it.

Set workspace = CreateWorkspace(Name, UserName, Password, UseType)

Dim myWorkspace As Workspace
Set myWorkspace = CreateWorkspace(Name:="Workspace2", _
    UserName:="admin", Password:="", UseType:=dbUseJet)

myWorkspace.Close

To open a form, use the OpenForm method of the DoCmd object. The syntax is as follows:

expression.OpenForm(FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs)

Here are the components of the syntax:

The following example uses the DoCmd object to open a form in the Northwind sample database (you must have this database open in Access for this to work). Press Alt+F11 to open Access's VBA Editor, and then type in this code. When you execute the code by pressing F5, the example displays the first record for which the Employee field matches Jan Kotas:

Sub test ()

DoCmd.OpenForm FormName:="Sales Analysis Form", View:=acNormal, _
WhereCondition:="Employee ='Jan Kotas'"

End Sub

To print an object, use the PrintOut method. The syntax is as follows:

expression.PrintOut(PrintRange, PageFrom, PageTo, PrintQuality, Copies, CollateCopies)

Here are the components of the syntax:

The following example prints one copy (the default) of the first page in the active object at full quality without collating the copies:

DoCmd.PrintOut _
PrintRange:=acPages, PageFrom:=1, PageTo:=1, CollateCopies:=False

Be sure to trap this code for an error in case you've requested a printout of something that doesn't exist—such as a range of 1 to 4 for a single-page form. In fact, it's always a good idea to trap errors in code that contacts peripherals such as printers or hard drives. What if the printer isn't turned on or the hard drive is full? Your code should anticipate and manage situations like these.

To run a macro, use the RunMacro method. The syntax is as follows:

expression.RunMacro(MacroName, RepeatCount, RepeatExpression)

Here are the components of the syntax:

The following example runs a macro named RemoveDuplicates:

DoCmd.RunMacro "RemoveDuplicates"