July 16, 2011, 1:35 p.m.
posted by dante
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:
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: