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:

  1. In the Visual Basic Editor, highlight your project in the Project Explorer. (Access only allows one project—that is, one database—to be open at a time, so this step isn't technically necessary.)

  2. Select Tools, References to display the References dialog box.

  3. In the Available References list, activate the check box beside the Microsoft ActiveX Data Objects 2.7 Library item, as shown in Figure.1.

    1. Use the References dialog box to activate the Microsoft ActiveX Data Objects 2.7 Library check box.


  4. Click OK.


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:

  1. Open the Windows Control Panel and launch the ODBC Data Sources icon. (In Windows XP, if you see just the Control Panel categories, first click Switch to Classic View. Double-click Administrative Tools and then double-click Data Sources (ODBC).)

  2. In the System DSN tab, click Add. The Create New Data Source dialog box appears.

  3. Highlight Microsoft Access Driver (*.mdb), and click Finish. The ODBC Microsoft Access Setup dialog box appears.

  4. Use the Data Source Name text box to enter the name of the new data source. Note that this is the name you'll be using in your VBA code. For the code listings in this chapter, I'm going to use the sample Northwind.mdb database that ships with Microsoft Office, so enter the name Northwind.

  5. Enter an optional Description.

  6. Click Select, use the Select Database dialog box to highlight the Access database file you want to use (the Northwind.mdb file's default location is C:\Program Files\Microsoft Office\Office11\Samples\), and click OK. Figure.2 shows the completed dialog box.

    2. Use the ODBC Microsoft Access Setup dialog box to define the data source.


  7. Click OK to return to the ODBC Data Source Administrator.

  8. Click OK.

     Python   SQL   Java   php   Perl 
     game development   web development   internet   *nix   graphics   hardware 
     telecommunications   C++ 
     Flash   Active Directory   Windows