Getting Ready: Two Steps Before You Begin

Getting Ready: Two Steps Before You Begin

When programming Word, Excel, and PowerPoint, you just create or open a module in the Visual Basic Editor and start typing away. Database programming is a bit different because there's a bit of prep work you need to do before you start "slinging code," as programming types like to say. The next two sections explain the details.

Step One: Create a Reference

This may sound strange, but the ability to program a database is not built into Access VBA by default! As I mentioned at the top of the chapter, Access VBA is set up to program forms and reports (among other things); it just can't work with the data that's in those forms and reports, not to mention the tables where the data actually resides. It's weird, I know, so why did Microsoft set things up this way? In simplest terms, there are actually several different ways to program data, and Microsoft quite rightly didn't want to foist a particular method on VBA programmers. (Dedicated database coders are very particular about how they access their data; not only that, but Microsoft has developed several new ways to program databases in recent years, so there are compatibility issues to worry about: a program written using a old method won't work with any of the new methods.)

So the first thing you need to do is tell Access which method of database programming you want to use. Technically, you're choosing the database object model. If you have no idea which one to choose, don't worry about it: As a beginning database programmer, your best bet by far is to choose the most recent object model, which is the Microsoft ActiveX Data Objects 2.7 Library, a mouthful that I'll usually just shorten to ADO in the rest of this chapter. Follow these steps:

The interesting thing about database programming is that you can do it from programs other than Access! For example, you could create a VBA program in Excel that works with data in a separate Access database. The secrets to this powerful idea are the Available References dialog box and the Microsoft ActiveX Data Objects 2.7 Library. By following the steps in this section in, say, Excel (for Step 1, you'd highlight the Excel VBA project you wanted to work with), you can use all of the database programming techniques that you'll learn in the rest of this chapter.

Note that you although you only have to do this once for a given Access database, you must repeat these steps for each subsequent Access database that you use.

Step Two: Create a Data Source

Another strange thing about database programming is that you always have to set up a connection, which is a kind of behind-the-scenes communications link that your code uses to request and change the data. There are two ways to set up a connection.

The first way is to declare a variable as a Connection type and then use the CurrentProject object's Connection property to return the connection:

Dim conn As Connection

Set conn = CurrentProject.Connection

Alternatively, you need to create a data source that specifies the database, and then (as you'll see in the next section) you use your program code to connect to that data source. The good news is that you only have to do this once for each database. Here are the steps to follow: