Ado Examples and Best Practices

Figure 2-3 illustrates the ADO 2.5 object model. It seems a lot simpler than the DAO or RDO models—at least on the surface. In the older DAO and RDO technologies, developers referenced object-based properties and methods to manipulate the underlying relational data sources. However, with ADO there are far fewer object-based properties, a lot fewer objects, and only four collections. Both DAO and RDO were choked (literally) with collections that consumed memory, and more importantly, CPU resources. They were also a constant source of memory leak problems, as objects were created and destroyed out of turn.

Figure 2-3: The ADO 2.5 Object Model

The ADO 2.5 object model has only five base objects—Connection, Command, Recordset, Record, and Stream. These can all be created independently. This means you don't have to create a Connection object or a Command object to create a Recordset—as a matter of fact, it often makes sense not to do so. Note that the Connection, Command, and Recordset objects are extended through the Properties collection associated with each object. These additional properties are generated (for the most part) by the provider.

When you connect to SQL Server, the Connection object's Properties collection is populated with properties specifically exposed by the selected "native" or ODBC SQL Server provider. These expose features and options specific to SQL Server. While other providers might also expose properties by the same name, they might not work in the same way—if they are implemented at all. I tell my ADO programming students to dump the Connection properties collection before connecting and again after connecting to their data provider. This illustrates how much the Properties collection is affected by the choice of provider and what's there regardless of the provider chosen.

These properties are actually documented—exactly where is the question. After some snooping around, I discovered that MSDN does not include OLEDB.CHM in its search path. This means that if you want to know what these properties do, you'll need to look there. I would set up a shortcut to that help file. On my system, OLEDB.CHM was loaded along with the other MSDN CHM files, but there's no telling where it will be on your system—go look for it. If you can't find it on your system, be sure to look back a couple of pages and download the latest version of ADO from www.microsoft.com/data. It's included there too.

Before you open an ADO Connection, but after having set the ConnectionString property, the Connection.Properties collection contains the following properties. Note that most of these properties don't need a lot of explanation once you understand the OLE DB jargon. For example, you might be used to seeing a Default Database property in DAO or RDO, but in OLE DB it's "Initial Catalog."

Password = Persist Security Info = User ID = Data Source = Window Handle = Location = Mode = Prompt = 4 Connect Timeout = 15 Extended Properties = DSN=LocalServer Locale Identifier = 1033 Initial Catalog = OLE DB Services = −5

After you open the connection, a lot more properties are added to the list (there are about three pages of them, so I won't list them here). These properties are a little more obscure, but as I said before, they're documented in the OLE DB SDK. I'll show how to dump your property collections a little later.

Perusing the resulting properties after the Connection object is open can be very enlightening. Someone could write an entire book on these properties and how to make best use of them. The best source of information on the properties not discussed in this chapter or in later chapters is the OLE DB SDK.

Consider that the OLE DB provider developers have a lot of freedom when implementing these properties. That is, when an OLE DB developer builds a provider to access their data source, he or she is not required to implement many of the properties in any particular way. While most well-defined and understood properties are implemented uniformly, developers are granted quite a bit of slack when it comes to more obscure operations. They're also on their own when it comes to implementing features unique to their data provider.

Once your ADO Connection object is open, you can dump the Properties collection to see what additional lights, knobs, and switches are available to control your data provider as shown in the following code:

Dim cn As ADODB.Connection Dim pr As Property Private Sub Form_Load() Set cn = New ADODB.Connection cn.ConnectionString = "DSN=LocalServer" For Each pr In cn.Properties Debug.Print pr.Name, " = ", pr.Value Next pr cn.Open "DSN=LocalServer", "SA", "" For Each pr In cn.Properties Debug.Print pr.Name, " = ", pr.Value Next pr End Sub