The ADO Object Model

Like all other data-access technologies from Microsoft, ADO has a distinct object model that defines the objects and interfaces, and how the objects relate to each other. If you’ve done any database programming before, you are probably familiar with the general layout of these objects, because they lean heavily on the lessons learned from DAO and RDO. ADO has a good object model, but it is not as strict as its ancestors, so it gives the programmer a great deal more flexibility, thereby reducing development time. For example, in ADO you can create a recordset of data by using just a single line of code.

This has been achieved by flattening the object model. It’s still shown in the documentation as a hierarchy (with a single object at the top), but some of the lower objects can exist in your code in their own right, without the need to create higher-level objects explicitly. This means that a programmer can use the object most suitable for a particular task without having to create many other objects that aren’t really required in the program. If ADO requires these objects, it creates them and uses them behind the scenes—and you need never know that they are there.

Versions prior to 2.5 have three main objects: the Connection, the Command, and the Recordset. No hierarchy exists among the three main objects, and you can create them independently of each other. Versions 2.5 and higher make two new objects available: the Record and the Stream. This diagram shows the relationships among these objects in the ADO model:

The objects shown as shaded are collections. Each collection can comprise zero or more instances of its associated object, as shown in the following table:

Note that although the main five objects all can exist independently in our code, the collections must be derived from their parent objects. So (for example) a Recordset contains a Fields collection, which in turn contains zero-or-more Field objects.

Enumerating the objects in a collection is the same for all collections; you just have to use a loop variable of the appropriate type. For example, in Visual Basic the following construct will step through each and every object in the Errors collection:

Dim objErr As ADODB.Error
For Each objErr In objConn.Errors 
    Debug.Print objErr.Description
Next

The same method can be used in VBScript, although you don’t give the object variable a specific type, because VBScript variables are Variants. For example, in ASP you could use:

Dim objFld
For Each objFld In objRec.Fields 
    Response.Write objFld.Name & "<BR>"
Next

In JScript you could use the Enumerator object. For example, in ASP you could use:

for(var objProp = new Enumerator(objConn.Properties);
        !objProp.atEnd(); objProp.moveNext() )
{
  Response.Write (objProp.item().Name + '<BR>');
}

This chapter is from ADO Programmer's Referencep,by David Sussman. (Apress, 2004, ISBN: 1590593421).  Check it out at your favorite bookstore today. Buy this book now!

The Main ADO Objects

The Connection Object

The Connection is actually the hub of ADO, because it provides the methods that allow us to connect to a data store. Note that when you create a Recordset or Command object, it is not necessary to create a connection first, because a Connection object is automatically created for you behind the scenes. What this means is that you can actually create a recordset or run a command with just a single line of code. For example:

rsAuthors.Open "authors", "DSN=pubs", adOpenStatic,
          adLockReadOnly, adCmdTable

This creates a recordset on the authors table from the pubs database, based on the ODBC Data Source Name (DSN) pubs. Although this example is using a Recordset object, it actually creates a connection underneath. In fact, you can create a separate and explicit Connection object from the one automatically created by ADO :

Set conPubs = objRec.ActiveConnection

This sort of mix-and-match approach to connection creation frees the programmer from a rigid structure and allows you to write the code in a way that is naturally easier.

Although this looks simple, you create a new connection every time you use a connection string to establish a connection to a data store (subject to connection pooling—see Chapter 3). If you will be running several commands or creating several recordsets, I recommend that you create a Connection object directly:

conPubs.Open "DSN=pubs; UID=sa; PWD="

or:

conPubs.Open "Provider=SQLOLEDB; Data Source=Piglet; " & _
        "Initial Catalog=pubs; User Id=sa; Password="

You can then use this Connection throughout your code without worrying about the number of connections you create. Not only is this more efficient, but it’s faster too; once a connection to a data store is established, it can be reused.

One area where you shouldn’t try to use a single connection is in the Session or Application scope in ASP pages. Creating an ADO object and storing it in a Session or Application variable has serious performance penalties if you want your ASP pages to scale. You can, however, store the connection string in Session or Application scope.

Once you have decided upon the provider, the properties of the Connection object allow you to see what kinds of facilities are available from the data provider. For example, picking the SQL Server provider allows you to see some of the properties for that provider, even before the connection is opened:

conPubs.Provider = "SQLOLEDB"
For Each objProp in conPubs.Properties
    Print objProp.Name
Next

Because ADO is designed to run against different data stores, it’s often worth checking that a particular function is supported before you call it. You do this by examining the Properties collection of the Connection object. This is particularly useful when using ADO as part of a development or query tool that gives users the ability to connect to different data stores. You could also, for example, use some of the properties to define a schema if manually creating XML from the data.

The Connection object isn’t just about connecting to, and holding information about, a data store. You can actually run commands and create recordsets with a Connection object too. For example:

conPubs.Execute "UPDATE titles SET price = price * 1.10", _
                 ,adCmdText + adExecuteNoRecords

This would run a SQL UPDATE query. Alternatively, to run a command and return a read-only recordset:

Set rsAuthors = _ 
    objConn.Execute ("SELECT * FROM authors", , adCmdText)

So, not only can you use a Recordset object to connect to a data store, but you can also use a Connection object to create recordsets. It’s all part of the “code as you want” strategy.

An ADO operation may generate more than one error, and these are stored in the Connection object’s Errors collection, which is discussed in more detail in Chapter 8. Although you might think this breaks the flexibility of the object model, it gives a central place to find error information. Because the Recordset and Command objects have a connection underneath, it is extremely simple to access this error information without creating a Connection object.

The Command object is designed to run SQL statements (assuming the provider supports the Command object), especially those that require parameters. This is an important point, because the use of stored queries and procedures is a great way to improve speed and segment your application, and it fits naturally with Windows DNA.

Like the Connection object, the Command object can be used to run action queries that don’t return a recordset, and for creating recordsets. Unlike the Connection, however, you’ll need a couple more lines of code, because you cannot specify the command text in the same line as the line that specifies the command type:

objCmd.CommandText = "UPDATE titles SET price = price *
                      1.10"
objCmd.CommandType = adCmdText + adExecuteNoRecords objCmd.Execute

To return a recordset, you use a similar style:

Set rsAuthors = objCmd.Execute

The greatest difference in command execution comes when using the Parameters collection, because this allows you to use stored procedures, queries, and template and parameterized queries saved in the data store. This removes the SQL code from your program, making it faster and easier to maintain. Using stored procedures, you can have SQL statements in a compiled form that perform a set of actions using the values you supply as parameters.

With a Command—if the data provider supports this facility—you can also save the command details for execution at a later date. This is known as a prepared statement, and it allows the precompiled command to be run many times within the same connection. Once you’ve finished with your connection, the saved commands are deleted automatically. You can also use a Command object more than once, and on different connections if you require (assuming that the command can be run on another connection). Just change the ActiveConnection property of the command to point to the new connection, then run the command again. If you wish to reuse the Command object, it’s wise to close any active recordsets that were created as a result of the command’s execution. This ensures that memory is deallocated correctly.

Another feature allows you to turn the command into a method of the Connection object, allowing you to build up a Connection object that contains many new methods. This is quite useful in client/server scenarios, because you could attach different commands to the connection depending upon user privilege. This is explained in more detail in Chapter 4.

The Recordset Object

The Recordset object is probably the most frequently used object in ADO; consequently, it has more properties and methods than other objects. Like the Command object, a Recordset can exist on its own or be attached to a Connection:

rsAuthors.Open "authors", "DSN=pubs", adOpenStatic, _
          adLockReadOnly, adCmdTable

This opens a recordset using a new connection. To use an existing connection, simply substitute the connection string for the existing Connection object:

rsAuthors.Open "authors", conPubs, adOpenStatic, _
          adLockReadOnly, adCmdTable

This is the preferred method if you are creating several recordsets, because it means that the connection to the data store doesn’t have to be established each time you create a recordset.

Another important point about the Recordset object is that it’s the only way to specify the recordset’s cursor type and lock type. If you use the Execute method of the Connection or Command object, it gives you the default cursor type, a read-only, forward-only cursor (often referred to as a firehose cursor, because the data is just “squirted” to the recordset). If you need to use a Command and require a different cursor type, then you can specify the Command object, instead of the table name or query text, as the source of the Recordset.

You use recordsets to examine and manipulate data from the data store. The Recordset object gives you facilities to move about through the records, find records, sort records in a particular order, and update records. You can perform updating in two modes: directly (changes are sent back to the data store as they are made) or in batches (changes are saved locally and then sent back to the data store all at once).

In client/server applications, you can also pass recordsets between the business-logic tier and the user-interface tier, where they can be manipulated locally. This saves database resources, which can be critical on large-scale applications, and it minimizes network traffic. These recordsets are called disconnected recordsets.

The Record object was introduced in version 2.5, and is used in conjunction with a new type of OLE DB Provider, called Document Source Providers. Document Source Providers are designed to enable access to data stores of semistructured data, such as file or mail systems. It was necessary to create a new object because of the way data is stored. In relational data stores, such as SQL Server or Access, we work using set-based data, where every row is in a rowset and has the same structure as the other rows. Semistructured data, on the other hand, doesn’t follow this pattern, because each row might have a different structure. If you think about the structure of a Web site, then you’ll start to understand how this works. A Web site generally consists of directories and files. If you look at the top-level directory for a site, you might see something like this:

All three items here have some common properties, such as a name. However, they also have different properties; the directory doesn’t have a size, but must indicate that it contains other files. These different properties are why we need a different object to cope with this sort of data. When dealing with semistructured data, the Record object maps onto individual files, and the Recordset object is mapped onto a collection of files.

To access a directory through a Record object, use the following syntax:

Dim recRoot     As New ADODB.Record
recRoot.Open "", "URL=http://localhost"
recRoot.Close

Specifying URL= tells ADO to use the Internet Publishing Provider.

The Record object not only allows access to files, but also gives management over them too. The CopyRecord, MoveRecord, and DeleteRecord methods give the ability to control files remotely.

The Stream object is a wrapper around a memory manager (in fact it’s based around the COM IStream interface) and gives control over the contents of the memory used to store an object. This might sound rather complex, but it has wide-ranging uses and is simple to use.

You can use a Stream in several ways. One way is in conjunction with a Document Source Provider (such as the OLE DB Provider for Internet Publishing) to provide access to file contents on a Web server. For example:

Dim stmFile     As New ADODB.Stream
Dim sContents As String
stmFile.Open "URL=http://localhost/postinfo.html"
stmFile.Charset = "ascii"
sContents = stmFile.ReadText
stmFile.Close

This simply opens a file and reads the contents into a string. You also get the ability to write data out to the stream, where it is saved automatically to the file. This gives the ability to perform editing on remote files.

Stream objects have other roles. They can be used when saving and opening recordsets, and in conjunction with the Microsoft XML Parser.

This chapter is from ADO Programmer's Referencep,by David Sussman. (Apress Books, 2004, ISBN: 1590593421).  Check it out at your favorite bookstore today. Buy this book now!

The Other ADO Objects

The Field Object and the Fields Collection

A Field object contains details about a single field (or column) in a recordset. Such information includes the data type of the field, its width, and so on.

The Fields collection contains a reference to an ADO Field object for each field in a recordset, and there are two ways in which you can use this. The first is with existing recordsets, where you can examine each field, see its type, check its name, and so on. This is quite useful when you are unsure what exactly the recordset contains, and it can be useful in scripting when showing tables dynamically. Using the Fields collection allows you to find the name of each field. For example, in ASP script you could do this:

Response.Write "<TABLE>"
For Each objFld In objRec.Fields
  Response.Write "<TR><TD>" & objFld.Name & "</TD></TR>"
Next
Response.Write "</TABLE>"

This creates an HTML table showing which fields are in the recordset objRec. In fact, using a similar method, you could easily create a simple script routine that accepts any recordset and builds an HTML table from it. You could use the Fields collection to supply the table header, showing the column names, and the recordset to fill in table details.

Another use of the Fields collection is for creating programmatic, or fabricated, recordsets. In the past, arrays often were used when it was necessary to store several related items. The trouble with arrays is that you must manage the array manually; you can’t add data to a new array element without first creating room in the array. Then came collections, to which you could add your own objects; but collections are also limited to objects and basic data types. So if you want to store some related items (records), but each item has different attributes (fields), you can use a fabricated recordset.

For example, imagine that you need to store details from a questionnaire. You could create a recordset like so:

rsNew.CursorLocation = adUseClient
rsNew.Fields.Append "Name", adVarChar, 50, adFldFixed
rsNew.Fields.Append "Age", adInteger, , adFldFixed
rsNew.Fields.Append "Question1", adBoolean, , adFldFixed
. . .
rsNew.Fields.Append "Comments", adVarChar, 255, adFldFixed

The good thing about this method is that you can save the contents of this disconnected recordset to a file, and you can use it as a parameter for functions in business objects (just like recordsets obtained from OLE DB Providers).

An Error object contains details of a single error (or warning) returned from a data provider. Details include a description of the error, an error number, and the source object that raised the error. When a provider encounters an error or wishes to return information to the consumer, the details are placed in an Error object, and this is appended to the Errors collection.

The Errors collection holds all of the errors when a data provider generates an error or returns warnings in response to some failure. This is provided as a collection because a failure can generate more than one error. For example, the following statement generates two errors because X and Y are unknown columns:

objConn.Open "DSN=pubs"
Set objRec = objConn.Execute "SELECT X, Y FROM authors"

You could check the errors generated here by looping through the collection:

For Each objErr In conPubs.Errors 
  Print objErr.Description
Next

If you don’t have a predefined Connection object, you can use the ActiveConnection property of the Command or Recordset:

rsAuthors.Open "SELECT X, Y FROM authors", "DSN=pubs", _
           adOpenStatic, adLockReadOnly, adCmdTable
For Each objErr In rsAuthors.ActiveConnection.Errors
  Print objErr.Description
Next

As with the Fields collection, you could easily build a simple routine that centralizes error handling.

There is always an Errors collection, but it will be empty if no errors have occurred. Error objects remain in the collection until the next data access error occurs, when they are cleared and replaced by the new error details.

A Parameter object contains details of a single parameter for a Command object. Some of these details include the name, data type, direction, and value of the parameter. A parameter can be one of the following types:

• An input parameter, which supplies values to the statement represented by the Command object

• An output parameter, which supplies values from the statement represented by the Command object

• An input and output parameter

• A return value, which supplies the return value from the statement represented by the Command object

The usage of the various parameter types is discussed in more detail in Chapter 4.

The Parameters collection is unique to the Command object and is one way of allowing you to pass parameters into and out of stored queries and procedures. When using parameters, you have two options. First, you can ask the data provider to fetch the parameters from the data source, and the Parameters collection will be filled in automatically for you:

objCmd.Parameters.Refresh

The downside of this is that it requires a trip to the server, which may cause your program to perform poorly, especially if you do this often. However, it’s a great way of finding out what type and size the provider expects your parameters to be, and is very useful during development and debugging. You should note that not all OLE DB Providers (or ODBC Drivers) support the Refresh method.

The second option is to add the parameters to the collection manually, like so:

Set objParam = objCmd.CreateParameter ("ID", adInteger, _
                                      adParamInput, 8, 147)

This creates an input parameter called ID, which is an integer (length 8 bytes) with a value of 147. Once created, the parameter can be appended to the Parameters collection:

objCmd.Parameters.Append objParam

Another good use of the Refresh method is, when first connecting to the data store, to cache the parameters details locally, perhaps in an array or in a user collection. This sort of approach allows you to write generic routines that process stored procedures.

A Property object contains provider-specific information about an object. We know that objects have a fixed set of properties, but one of the fundamentals of OLE DB and ADO is that it can talk to a variety of data providers, and these providers often have different ways of working, or they support different properties. If all providers had to support a fixed functionality, then OLE DB and ADO wouldn’t really be very flexible; they would constitute the lowest common denominator of functionality. The solution is to provide a Property object for each provider-specific property, and a Properties collection to store all provider-specific properties.

For a Connection, the Properties collection contains a large amount of information about the facilities that the provider supports, such as the maximum number of columns in a SELECT statement, and what sort of outer join capabilities are supported. For a Recordset, there’s just as much information, ranging from the current locking level to the asynchronous capabilities supported by the provider.

You can examine or set the value of an individual property by just using its name to index into the collection. For example:

Print conPubs.Properties("Max Columns in SELECT")

To examine all the properties, you can use a simple loop:

For Each objProp In conPubs.Properties
  Print objProp.Name
Next

You may well find that you never use this collection, unless you are writing an application that supports multiple data providers, in which case you may need to query the provider to examine what it supports.

This chapter is from ADO Programmer's Referencep,by David Sussman. (Apress Books, 2004, ISBN: 1590593421).  Check it out at your favorite bookstore today. Buy this book now!

The ADOX Object Model

The ADOX library contains extensions for Data Definition Language (DDL) and security, providing a way to access schema creation and modification and for accessing security credentials for the schema. Many data stores allow access to schema and security information, but they often differ in the command language used. ADOX abstracts these provider-specific details into a common set of objects, allowing a single code set to be used regardless of provider-specific syntax.

The ADOX (for DDL and Security) object model observes more hierarchy than does the ADO object model, but this is because of the nature of the objects it contains. This means there is a parent object and several child objects, as this diagram for the ADOX object model illustrates:

The Catalog object is a central repository, and is really just a container for all of the other objects. The Catalog object allows you to connect to a data store and examine the components comprising it. With the Catalog object, you can use the ActiveConnection property to specify the data store to which you want to connect, and then use the collections to obtain more detailed information. It’s also possible to obtain and set the owner of various objects without using the collections.

To use a catalog, you just point to an existing Connection object or a Connection string. For example:

Dim objCatalog As New ADOX.Catalog
Set objCatalog.ActiveConnection = objConn

or:

objCatalog.ActiveConnection = "Provider=SQLOLEDB; . . ."

A Table object represents a single table, containing information such as the table name and the last change date, in a catalog. It is also a container for the Indexes, Keys, and Columns collections (described shortly).

The Tables collection contains Table objects—one for each table in the catalog. I provide an example of the Tables and Columns collections later in this section.

An Index object contains the details for a single index on a table. It identifies attributes such as the index name, whether the index is unique, whether the index allows nulls, and so on. Using the Index object’s Columns collection, you can see which columns comprise the index.

The Indexes collection contains all the Index objects for a particular Table. For example, the following code prints the indexes on a table and the columns that make up each index:

Dim objIndex  As ADOX.Index
Dim objColumn As ADOX.Column
For Each objIndex In objTbl.Indexes
   Print objIndex.Name
   For Each objColumn In objIndex.Columns
      Print objColumn.Name
   Next
Next

A Key object contains information regarding a table key. It identifies such items as its name, whether it is a primary or foreign key, the related table, and so on. Like the Index object, the Key object contains a collection of Column objects, identifying the columns that make up the key.

The Keys collection contains a list of keys for a table. For example, the following prints all the keys and columns for a table:

Dim objKey    As ADOX.Key
Dim objColumn As ADOX.Column
For Each objKey In objTabl.Indexes
   Print objKey.Name
   For Each objColumn In objKey.Columns
      Print objColumn.Name
   Next
Next

A Column object represents an individual column from a Table, Index, or Key object. In many respects it is similar to the ADO Field object, but rather than storing the details of a field, it holds the details of a stored column, such as its name, its data type, and so forth. For Key columns it contains details of the related columns, and for Index columns it contains details of the clustering and sorting. There is just one Column object, but what it contains depends upon its parent (Table, Index, or Key).

The Columns collection contains all the columns for a particular Table, Key, or Index object. For example, to see all the columns in a particular table, you would use this:

Dim objTbl As New ADOX.Table
Dim objCol As ADOX.Column
Set objTbl = objCatalog.Tables(0)
For Each objCol In objTbl.Columns
   Print objCol.Name
Next

The syntax is very similar when looping through all of the Column objects associated with an Index or a Key object.

A Group object identifies a security group. It contains a list of users in the catalog. Its main role is to allow the retrieval and setting of permission for a named group or for allowing access to the Users collection, which contains a list of all users belonging to this group.

The Groups collection contains a list of Groups that belong to a particular catalog, or a list of Groups that a particular user belongs to. For example, the following enumerates the Groups in a Catalog, and then the Users in a Group:

Dim objGroup As ADOX.Group
Dim objUser As ADOX.User
For Each objGroup In objCatalog.Groups
   Print objGroup.Name
   For Each objUser In objGroup.Users
      Print objUser.Name
   Next
Next

Not all providers support the Group object and Groups collection. At the moment, the Jet provider gives the most comprehensive support.

A User object contains details of a single user of the data store and contains properties to retrieve or set the user name and the user’s unique ID, and methods to read and write permissions. The User object also contains a Groups collection, which is a list of groups to which the user belongs. Be careful about writing recursive procedures that traverse the Users and Groups collections, because they point to each other.

The Users collection contains a list of all users in a catalog. For an example, refer to the preceding description of the Group object.

Not all providers support the User object and Users collection. At the moment, the Jet provider gives the most comprehensive support.

A Procedure object identifies a stored procedure in a catalog. The Procedure object has very few properties, mainly because one of these properties represents an ADO Command object. The Procedure object identifies the details about the stored procedure (such as its name and when it was last modified), and the Command object identifies the internal details of the procedure (such as the SQL text).

The Procedures collection contains a Procedure object for each stored procedure in the Catalog. For example, to access a stored procedure and its SQL text, you could use the following code:

Dim objCmd As ADODB.Command
Set objCmd = objCat.Procedures(" proc _ name").Command Print objCmd.CommandText

This uses the Command property of the Procedure object to reference a standard ADO Command object and print the text (command string) of the Command.

A View object identifies a single view in a Catalog, which is a set of records or a virtual table. Like the Procedure object, the View contains a Command object, allowing access to the view command.

The Views collection contains a list of View objects, one for each view in the catalog.

For example, to obtain all views in a catalog, you could do this:

Dim objView As ADOX.View
For Each objView In objCat.Views
   Print objView.Name
Next

Like the Procedure object, the View object also supports an ADO Command as a property. So, to access the details of the command, you could use similar code:

Dim objCmd As ADODB.Command
Set objCmd = objCat.Views(" view _ name").Command
Print objCmd.CommandText

This object and collection is also provider-dependent.

The Property object and Properties collection are identical to their ADODB equivalents.

This chapter is from ADO Programmer's Referencep,by David Sussman. (Apress Books, 2004, ISBN: 1590593421).  Check it out at your favorite bookstore today. Buy this book now!

ADOX Supported Features

ADOX is not supported by all providers, including those supplied by Microsoft. Currently, only the OLE DB Provider for Microsoft Jet fully supports ADOX. For the other Microsoft OLE DB providers, all features are supported except for those listed here:

This chapter is from ADO Programmer's Referencep,by David Sussman. (Apress Books, 2004, ISBN: 1590593421).  Check it out at your favorite bookstore today. Buy this book now!

OLE DB Providers

I’ve already mentioned that ADO can connect to many different data providers, and this is because of OLE DB. Remember that ADO is just a layer sitting on top of OLE DB to hide the complexity. It seems sensible, though, to look at some of these OLE DB data providers in more detail. You need to see their differences, for although most of the ADO usage will be the same, OLE DB providers don’t always support the same facilities. This is natural, because some of them are fundamentally very different. Most of the relational database providers, for example, will provide similar facilities, but other providers (such as for Internet Publishing) might not work the same way.

When you install MDAC, you are supplied with the following OLE DB providers:

These are just the defaults supplied, some of which may not appear, depending upon your installation options. For example, Index Server does not act as a remote provider, and you can connect to only local Index Servers. Therefore, if you do not have Index Server on your machine, the MSIDXS may not be installed. Installation of versions later that 2.6 on top of an earlier version will not remove any deprecated providers, and these will not be installed on a clean MDAC 2.8 install.

You can find out which providers are available on your system by creating a new Data Link. This is similar to an ODBC DSN, but for OLE DB connections. By using Windows Explorer, you can create one in any directory simply by right-clicking (or selecting the File menu), picking New, and selecting Microsoft Data Link. This creates a file with a .udl extension, and you can then view its properties. If this option isn’t available, you can create a text file and rename the extension to .udl. Open the UDL file by double-clicking it. From the Provider tab in the Data Link Properties window, you can see the available providers, as shown here:

In fact, using the Data Link is a good way to create connection strings if you are unfamiliar with them; you can even use them instead of a DSN within connection strings (see the next section). You can create a Data Link file, filling in the details onscreen, and then examine the resulting .udl file in Notepad.

This chapter is from ADO Programmer's Referencep,by David Sussman. (Apress Books, 2004, ISBN: 1590593421).  Check it out at your favorite bookstore today. Buy this book now!

One of the major differences among the various OLE DB providers is found in the connection string: Different providers require different information to be able to make the connection to the data store.

ADO recognizes only four of the arguments in the connection string; the remaining arguments are passed on to the provider. The main argument you are interested in is Provider, which identifies the OLE DB provider to be used. The second argument is File Name, which can be used to point to an existing Data Link file. If you use a Data Link file, you can omit the Provider argument, because the Data Link file contains this information. The other two arguments (Remote Provider and Remote Server) relate to Remote Data Services; I describe them in detail in Chapter 9.

The OLE DB Provider for ODBC is the default provider, so if you don’t specify which one to use, this is what you’ll get. If you do specify this provider explicitly in your connection string, you must give the rather obscure name MSDASQL. When using the OLE DB Provider for ODBC, you have three choices: use an existing ODBC System DSN, a DSN-less connection string, or an ODBC File DSN.

For a DSN-based connection, simply specify the data source name:

Provider=MSDASQL;DSN=data_source_name;
UID=user_id; PWD=user_password

For a DSN-less connection, the connection string varies with the database you are connecting to. It follows the same conventions as an ODBC connection string; in the Control Panel, you can see the parameters in the ODBC applet. One important option is the same for all ODBC connections, because it specifies the ODBC driver to use:

Provider=MSDASQL;Driver=

The name of the driver specified in the Driver attribute of the connection string will be one of those shown on the Drivers tab of the ODBC Control Panel applet. Your list of drivers may differ from those listed in this screen:

On Windows 2000 and Windows XP the ODBC Data Source Administrator, known as simply Data Sources (ODBC) on this platform, can be found under the Administrative Tools group. Regardless of platform, the list of ODBC Drivers installed on a given system can also be found in the system registry at HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers .

You should enclose the driver name in curly braces. Let’s look at some examples.

Microsoft Access

To connect to Microsoft Access, your connection string would start like this:

Provider=MSDASQL;Driver={Microsoft Access Driver (*.mdb)}

You then must specify the full path and filename to the Access database by using the DBQ attribute:

Provider=MSDASQL;Driver={Microsoft Access Driver (*.mdb)};
DBQ=C:\mdb_name.mdb

Note: An interesting fact has recently come to light that causes the following error with the OLE DB Provider for ODBC:

Microsoft OLEDB Provider for ODBC Drivers error ‘80004005’

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

This seems to occur if you leave a space after the first semicolon in the connection string shown in the preceding code example, just before the Driver. So, this works:

Provider=MSDASQL;Driver={Microsoft Access Driver (*.mdb)};
DBQ=C:\mdb_name.mdb

But this doesn’t:

Provider=MSDASQL; Driver={Microsoft Access Driver (*.mdb)};
DBQ=C:\mdb_name.mdb

Notice the space; it’s hard to spot.

Of course, because this is the default OLE DB Provider, you can use the following string (without specifying the Provider) perfectly well:

Driver={Microsoft Access Driver (*.mdb)}
; DBQ=C:\mdb_name.mdb

Microsoft SQL Server

To connect to SQL Server by using the OLE DB Provider for ODBC (MSDASQL), you must supply a little more information:

• Server is the name of the SQL Server

• Database is the database name

• UID is the SQL Server user ID

• PWD is the password for the SQL Server user ID

For example:

Provider=MSDASQL;Driver={SQL Server}; Server=Tigger;
Database=pubs; UID=sa; PWD=

Microsoft Excel

You can connect to a Microsoft Excel (any version) spreadsheet three ways. The first is by specifying the sheet name as the source of the Recordset:

objRs.Open "[Sheet1$]", objConn, adOpenDynamic, _
   adOpenStatic, adCmdTable

The second is by specifying the sheet name with a row and column area:

objRs.Open "Select * from `Sheet1$A2:C4`", oConn, _
   adOpenStatic, adLockBatchOptimistic, adCmdText

The third is by specifying a range name:

objRs.Open "Select * from myRange1", oConn, adOpenStatic, _
   adLockBatchOptimistic, adCmdText

To use the range method, you must first make sure that a range has been specified in the spreadsheet. This range should enclose all the data that you wish to select, and it equates to the recordset. You can have any number of ranges in a spreadsheet. To create a range, you select the cells in the spreadsheet and enter the name in the range box.

You then specify the range as the Source parameter of the Recordset’s Open method:

objRec.Open "Authors", objConn

In all three cases, the connection string must specify Microsoft Excel as the ODBC driver and the spreadsheet name as the data store name:

Provider=MSDASQL;Driver={Microsoft Excel Driver (*.xls)};
DBQ=C:\xls_name.xls

Text Files

Text files are slightly different from Excel files in the following respect: In the connection string, you specify the directory where the text file resides, rather than the text file itself:

Provider=MSDASQL;Driver={Microsoft Text Driver (*.txt;
       *.csv)}; DBQ=C:\directory_name

You then specify the text file as the name of the recordset to open:

objRec.Open "TextFile.txt", objConn

When using the provider for Jet, you must specify only the database name in the Data Source attribute of the connection string:

Provider=Microsoft.Jet.OLEDB.4.0; Data
          Source=C:\mdb_name.mdb

If you have a system database, you can use the Properties collection to set this before opening the connection, but you must specify the provider first:

conDB.Provider = "Microsoft.Jet.OLEDB.4.0"
conDB.Properties("Jet OLEDB:System database") = _
                         "C:\system_db_name"
conDB.Open "Data Source=C:\pubs\pubs.mdb"

A database password is also set in this way:

objConn.Properties("Jet OLEDB:Database Password") =
                   "LetMeIn"

When establishing connections to SQL Server databases using the Microsoft OLE DB Provider for SQL Server (SQLOLEDB), the Data Source attribute is used to specify the name or address of the SQL Server. Consequently, an additional attribute named Initial Catalog is used to specify the name of the database for which the connection will be made:

Provider=SQLOLEDB; Data Source= server_name;
Initial Catalog= database_name; User Id= user_id; Password=
                                              user_password

For example:

Provider=SQLOLEDB; Data Source=Tigger; Initial 
                                       Catalog=pubs;
User Id=sa; Password=

For Index Server you need to specify only the provider name, unless you have multiple catalogs in use under Index Server. In this case you use the Data Source to specify the required catalog:

Provider=MSIDXS; Data Source=catalog_name

The Internet Publishing provider allows you to connect to servers that support either the Microsoft FrontPage Server Extensions or DAV (also known as WebDAV or HTTP-DAV) protocol. This allows you to use ADO to query the servers for directory contents, resources, and so on, as well as to update these resources. You use the Data Source attribute to specify the name of the Web server:

Provider=MSDAIPP.DSO.1; Data Source=http://web.server.name

Alternatively, you can add URL= to the front of the Data Source you are opening, which tells ADO that the Internet Publishing provider is being used. For example:

recRoot.Open "", "URL=http://web.server.name"

A Data Link file can contain connection details for any OLE DB Provider. Data Link files have a .udl suffix and allow the connection details to be stored in a file rather than being embedded in an application. Data Link files can be created by using Windows Explorer. Under Windows 2000 and Windows XP, you should create a Text File, and then rename the suffix to .udl. Double-clicking this file opens the Data Link Properties dialog. Under previous versions of Windows (and ADO), you can select New Data Link File from the Explorer context menu.

To use a Data Link file as a connection string, simply set the File Name option to point to the .udl file:

objConn.Open "File Name=C:\temp\pubs.UDL"

This chapter is from ADO Programmer's Referencep,by David Sussman. (Apress Books, 2004, ISBN: 1590593421).  Check it out at your favorite bookstore today. Buy this book now!

Asynchronous processing was a new feature in ADO 2.0 and allows commands to be executed at the same time as other commands. This is particularly useful when creating very large recordsets or running a query that may take a long time, because you can continue with another task and allow ADO to tell you via an event when the command has finished. You can also cancel a long-running command if you wish. Events can also be used to perform preprocessing and postprocessing around certain ADO operations, such as opening a Recordset or establishing a Connection.

Events

For ADO, only the Recordset and Connection objects support events. There are generally two types of events:

• Will… events—those called before an operation starts

• …Complete events—those called after it has completed

A few other events are called after an event has completed, but they are not part of the Will… and …Complete event pairs.

A Will… event is called just before the action starts. It gives you the chance to examine the details of the action, and to cancel the action if you decide not to run it. The …Complete event is called just after the action completes, even if the operation was cancelled. If it was cancelled, the Errors collection is filled with details of why it was cancelled.

The Will… events generally have an argument, adStatus, indicating the status of the event. You can set this argument to adStatusCancel before the method ends, to cancel the action that caused the event. For example, suppose a connection generated a WillConnect event; setting adStatus to adStatusCancel will cancel the connection. The ConnectComplete event will then be called, with a status indicating that the connection failed.

The nature of events means that there can be several Will… events that could result from a single action. Although all of these will be called, there is no guaranteed order in which this will happen.

The way the Will… and …Complete events are generated can often be confusing, especially in terms of what happens when you wish to cancel the operation that raised the event. The action triggers the Will… event, which has its own event procedure. This code runs, and then the action itself is run. After the action is finished, the …Complete event is triggered, which runs its event procedure. Once that has completed, execution will continue at the line after the action. The following diagram shows this using Connection events when opening a connection:

In the WillConnect event procedure, you can decide whether you want this action (that is, the objConn.Open) to occur or not. The event procedure has a parameter called adStatus, which you can use. If you set this to adStatusCancel before the event procedure finishes, the action is cancelled. The ConnectComplete event will still run, but its parameters will indicate that an error has occurred. For example, the following diagram shows how this can be trapped:

The important thing to note is that the ConnectComplete event is still generated, but the connection does not actually take place. Some actions may generate more than one event, so what happens then? Well, the Will… event is always generated before its associated …Complete event, but when two Will… events exist, the order in which they are generated is not guaranteed. If you cancel the action from within an event procedure, and this happens to run as the first event procedure, then the second set of event procedures (both the Will… and the …Complete) are never generated. For example, imagine a recordset where you have events to detect when fields and records change, as shown here:

Here the action has been canceled in the WillChangeRecord event procedure. The RecordChangeComplete event procedure runs, but indicates that an error occurred, and the actual action, that of setting the field value is not executed. The WillChangeRecord and RecordChangeComplete event procedures are not run.

These diagrams use Visual Basic, but language choice is not crucial; it’s the order of events that’s important.

To use events within Visual Basic, you declare a variable by using the WithEvents keyword. This must be a module/form/class/control level or global variable, because WithEvents is invalid within procedures:

Private WithEvents m_rsAuthors As ADODB.Recordset

Note that the New keyword is omitted when using this syntax, because you can’t instantiate objects at the same time as declaring the variable with events. You must do this separately:

Set m_rsAuthors = New ADODB.Recordset

Once you have declared the variable, the object will appear in the objects list in the code combo and in a list of events in the event drop-down list (for a selected object). Selecting an event will create the event procedure.

This chapter is from ADO Programmer's Referencep,by David Sussman. (Apress Books, 2004, ISBN: 1590593421).  Check it out at your favorite bookstore today. Buy this book now!

There are nine Connection events:

• BeginTransComplete is called after a BeginTrans method has completed.

• CommitTransComplete is called after a CommitTrans method has completed.

• RollbackTransComplete is called after a RollbackTrans method has completed.

• WillConnect is called just before a connection is established.

• ConnectComplete is called after a connection is established.

• Disconnect is called after a connection has disconnected.

• WillExecute is called before an Execute method is run.

• ExecuteComplete is called after an Execute method has completed.

• InfoMessage is called when the provider returns information messages.

An example of the …Complete events could be to indicate to users the status of their connection. The individual events for a Connection are discussed in more detail in Chapter 3.

Recordset Events

The Recordset has more events than the Connection:

• FetchProgress is called periodically during an asynchronous recordset creation.

• FetchComplete is called when the recordset has been fully populated with its records.

• WillChangeField is called before an action causes a field to change.

• FieldChangeComplete is called after an action caused a field to change.

• WillMove is called before an action causes the current record to change.

• MoveComplete is called after an action caused the current record to change.

• EndOfRecordset is called when there is an attempt to move beyond the end of the recordset.

• WillChangeRecord is called before an action causes the data in the current record to change.

• RecordChangeComplete is called after an action caused the data in the current record to change.

• WillChangeRecordset is called before an action causes a change to an aspect of the record-set (such as a filter).

• RecordsetChangeComplete is called after an action caused the recordset to change.

The Will… events can be used to notify the user that an action may change data or may change the underlying records in the recordset.

A word of caution when using the new ADO Data Control in Visual Basic 6: This automatically updates records when using the video-style buttons to move around the records. This differs from the standard Data Control. If you change a field, then the WillChangeField event is called, and if the value is not correct, you can cancel the action. However, it is the Update that is cancelled, not the move. It’s not a major problem, but something to bear in mind.

The individual events for a Recordset are discussed in more detail in Chapter 5.

Object Usage

Some people are confused about when to use which object. After all, the Connection, Command, and Recordset objects are all capable of returning a recordset of data. There are only a few hard-and-fast rules, but in general, here’s what you should do:

• If you only ever need to run queries that don’t return a recordset, and those queries are not parameterized statements (such as stored procedures) with output parameters, then use the Connection object and the Execute method.
  
• If you need to use stored procedures with output parameters, you must use the Command object.

• If you need to specify the cursor type or lock type, then you must use a Recordset object.

• If you are creating only one or two recordsets of data, then you can use the Recordset to implicitly establish a connection to a data store by passing in a connection string in the ActiveConnection parameter of a Recordset object’s Open method.

• If you are creating two or more recordsets, then explicitly create a Connection object first, then reuse the Connection object when opening the Recordset objects. Remember that each time you connect to a data store with a connection string, a new connection is opened or retrieved from the connection pool.

This chapter has introduced the ADO object model, examined how you go about connecting to data stores, and looked at the connection strings for a variety of OLE DB providers. It has also briefly introduced asynchronous processing and the use of events, the details of which are covered in the Chapters 3 and 5, where the Connection and Recordset objects are discussed in more detail.

Most of the rest of the book is concerned with the properties, methods, and events of the various ADO objects and related libraries, such as ADOX, ADOMD, and Jet Replication Objects (JRO).

This chapter is from ADO Programmer's Referencep,by David Sussman. (Apress Books, 2004, ISBN: 1590593421).  Check it out at your favorite bookstore today. Buy this book now!