| There are three objects for you to use in establishing a connection to a database using ADO. They will be largely unfamiliar to you if you're used to DAO. Which object you use depends in part on what you want to accomplish. One object, the Connection object, is discussed here. The remaining two, the Command object and the Recordset object, are discussed in Chapters 10, "Defining Fields and Records in DAO," and 11, "Getting Data from Access and into Excel with ADO and DAO." Establishing a Reference to the ADO Library Before you can use ADO at all, you need to establish a reference to the ADO library. If necessary, switch to the VBE and choose References from the Tools menu. Scroll down until you find the reference you want and fill its check box (see Figure 8.1). If you want to establish more than one reference, continue locating them and filling their check boxes. When you're through, click OK. Figure 8.1. The ADO library shown in the list box provides references to ADODB objects. 
Although the object library's contents are commonly referred to as ADO, or ActiveX Data Objects, ADO is not a single library but a set of libraries. The one you're actually making reference to by checking, say, Microsoft ActiveX Data Objects 2.7 Library, is ADODB. When you make reference to the library in your VBA code by declaring an object variable, you often should qualify the reference with ADODB. It's usually best to choose the highest release level available. If you find that a higher (the term is equivalent to newer, but alas, not always to better) release level doesn't work as you expect, you can step back down to a lower level. NOTE As shown in Figure 8.1, the References dialog box has a list box containing available libraries. Of the libraries that are checked, the higher in the list box, the higher the library's priority for resolving naming conflicts. For example, both the Excel object library and the ADO object library have a Parameter object. VBA will use the Excel Parameter object if the Excel library is higher in the list box, and the ADO Parameter object if the ADO library is higher. Still, it's better to make things explicit. If you want to declare an object variable to represent an ADO object, qualify it with ADODB, an Excel object with Excel, and so on: Dim rsRevenues As ADODB.Recordset Dim dbFinancials As DAO.Database Dim prmYear As Excel.Parameter
TIP There's something about the structure of the Available References list box that tends to mislead users. It's easy to click a library so that it's highlighted, and then click OK to dismiss the dialog box. If you do that, you might think that you've established a reference to a library when in fact you haven't. Of course, you have to fill the library's check box before you close the dialog box. But I've seen many users just click on a library's name and immediately click OK then they're mystified when their code doesn't recognize a library. I've done it myself more times than I care to admit. If your code doesn't recognize a library that you think you've selected, double-check the references list.
Preparing a Connection Object You declare a Connection object in much the same way that you declare any object variable in VBA. For example Dim cnConnectToLedger As ADODB.Connection
If you're certain that you have not set a reference to any other library that has a Connection object, you could instead declare it this way: Dim cnConnectToLedger As Connection
As noted earlier, however, you might have also referenced another library that has a Connection object, and that library might be higher up in the priority pecking order than is the ADO library. It's partly a matter of how sure you are of yourself and partly of programming style, but you should at least consider preceding the Connection class with the ADODB qualifier. NOTE A class is an object's definition. It contains all the information that VBA needs to create a new instance of the object: what properties apply to it and what values they can take on, methods that can be used with the object, and so on. It is a sort of template for its object.
After you've declared an object variable as a Connection, you can create a new instance of it: Set cnConnectToLedger = New ADODB.Connection
The Dim statement probably looked reasonably familiar to you, but this Set statement might not. The Set statements that were discussed in Chapter 7 all set an object variable equal to a particular worksheet, or chart axis, or range of cells whatever the object variable represented, the object already existed. So, it was possible to set an object variable to represent Sheet1, or the chart's vertical axis, or the range A3:D15. When you're first setting a Connection object, however, it doesn't yet exist. So, your Set statement uses the keyword New to inform VBA that it needs to create a new instance of an ADODB.Connection. At this point, you haven't pointed the connection, cnConnectToLedger, at any database. You haven't opened the connection. You haven't stated that the connection will use the Jet database engine, or SQL Server, or some other provider. It's just out there. Using the Keyword New The example given in the prior section was Dim cnConnectToLedger As ADODB.Connection Set cnConnectToLedger = New ADODB.Connection
and the keyword New was used in the Set statement. It could instead have been used in the Dim statement: Dim cnConnectToLedger As New ADODB.Connection
There's a difference between the two usages. When you use the keyword New in the Set statement, as was done in the prior section, you're allowing for the possibility that the object variable might already have been representing another object. In that case, when you set the object variable to some other object, the first reference is released and VBA creates a new instance of the class. In contrast, suppose that you declare the object variable as a New ADODB.Connection, as in this example. If you do so, you do not use Set to create a new instance of the class. You just use the object variable. For example Dim cnConnectToLedger As New ADODB.Connection With cnConnectToLedger .Provider = "Microsoft.Jet.OLEDB.4.0" .ConnectionString = "C:\GeneralLedger.mdb" End With
Notice that a Set statement is not used. Opening the ADO Connection After you've established the connection, you need to refine it. There are several properties that you can set and methods that you'll want to invoke. Because of the great flexibility of ADO, you'll find that you have different ways to do so. If you've used DAO in the past and are just now learning about ADO, you might legitimately wonder why you should change horses. One reason is that the Jet engine won't be around forever. Microsoft Access is modular. It's partly a development toolkit, providing the user means of designing tables, queries, forms, reports, and VBA code. Wrapped in with all those tools is a database engine that actually manages the storage, modification, and retrieval of the data. The Access database engine is termed the Jet engine, and DAO is optimized to work with Jet databases. If you were never going to deal with databases other than those that use Jet, there would be little reason to use any object library other than DAO. DAO is optimized for Jet, and is more efficient for working with Jet than are other models such as ADO. But Microsoft has been distancing itself from Jet not, though, from Access starting at least with the 2000 release of Office. Microsoft has developed a newer product, SQL Server, which is intended to provide a more robust engine and to handle much more user traffic than does Jet. Access itself is capable of using other database engines. For example, you can use Access as a front end into a SQL Server back end. You continue to design your tables, queries, forms, reports, and code in Access, which then turns over the data management to SQL Server instead of Jet. But DAO is comparatively ineffective for working with SQL Server or, for that matter, data sources other than those managed by Jet. ADO, on the other hand, effectively handles a wide variety of data sources, including SQL Server. This is not intended to be an argument for using ADO or against using DAO. It is intended as background to the fact that when you use ADO, you should specify a provider for the connection. Here's an example: Dim cnConnectToLedger As New ADODB.Connection cnConnectToLedger.Provider = "Microsoft.Jet.OLEDB.4.0"
You could use this syntax to specify that the connection is to use an Access database that manages its data with Jet. If you're connecting to SQL Server, you'd use something like this: Dim cnConnectToLedger As New ADODB.Connection cnConnectToLedger.Provider = "SQLOLEDB.1"
The actual specification of the provider would depend on the provider version you have installed. | 