11

Data Access with ADO

ActiveX Data Objects, or ADO for short, is Microsoft's technology of choice for performing client-server data access between any data consumer (the client) and any data source (the server or provider). There are other data-access technologies of which you may have heard in relation to Excel, including DAO and ODBC. However, these will not be covered in this chapter as Microsoft intends for these older technologies to be superseded by ADO, and for the most part this has occurred.

ADO is a vast topic, easily the subject of its own book. In fact, Wrox has published several excellent books exclusively on ADO, including the ADO 2.6 Programmer's Reference (ISBN 1-861004-63-x) and Professional ADO 2.5 Programming (ISBN 1-861002-75-0). This chapter will necessarily present only a small subset of ADO, covering the topics and situations that you will frequently find useful in Excel programming. For a more detailed look at ADO, we strongly recommend one of the dedicated volumes mentioned above.

As a freestanding, universal data-access technology, ADO has evolved rapidly over the past several years, much more rapidly than the programs that use it. As of this writing, there are several versions of ADO in common use, including: 2.1, 2.5, 2.6, and 2.7. This chapter will focus on ADO 2.7. This is the version of ADO that ships natively with the newest version of Windows and Office. If you aren't running one of these applications and don't have ADO 2.7 installed, you can download it from the Microsoft Universal Data Access Web site at http://www.microsoft.com/data.

An Introduction to Structured Query Language (SQL)

It's impossible to get very far into a discussion of data access without running into SQL, the querying language used to communicate with all databases commonly in use today. SQL is a standards-based language that has as many variations as there are database vendors. This chapter will use constructs compliant with the latest SQL standard, SQL-92, wherever possible. In order to properly cover data access with Microsoft SQL Server, however, the SQL Server variant of SQL called Transact SQL, or T-SQL for short, will be touched upon.

This section will provide a brief overview of basic SQL syntax. This overview is by no means complete, but will serve to introduce you to the concepts used in this chapter. For an excellent and significantly more detailed primer on SQL, we would recommend Beginning SQL Programming (ISBN 1-861001-80-0) from Wrox Press.

There are four SQL commands you will use frequently, and we have added a couple that you will use less frequently but are powerful nonetheless. These are:

• SELECT

  Used to retrieve data from a data source

• INSERT

  Used to add new records to a data source

• UPDATE

  Used to modify existing records in a data source

• DELETE

  Used to remove records from a data source

• CREATE TABLE

  Used to create a new table

• DROP TABLE

  Used to remove and existing table

The terms record and field are commonly used when describing data. The data sources we'll be concerned with in this chapter can all be thought of as being stored in a two-dimensional grid. A record represents a single row in that grid, a field represents a column in the grid. The intersection of a record and a field is a specific value. A resultset is the term used to describe the set of data returned by a SQL SELECT statement.

We will use the Customers table from Microsoft's Northwind demo database, as shown in Figure 11-1, to illustrate our SQL syntax examples. Northwind is installed when you install either MS-Access or MS-SQL Server (the file is usually named NWind.mdb or Northwind.mdb).

The SELECT Statement

The SELECT statement is, by far, the most commonly used statement in SQL. This is the statement that allows you to retrieve data from a data source. The basic syntax of a SELECT statement is demonstrated next:

SELECT columnamel, columnname2 [, columnnamen] FROM tablename

Figure 11-1

The table in the preceding figure is named Customers, and contains a column named Company Name among others. To select all of the Customers by name we could write:

SELECT CompanyName FROM Customers

The SELECT clause tells the data source what columns you wish to return. The FROM clause tells the data source which table the records should be retrieved from. Extending our example, we could select the company name and contact name from the Customers table with the following query:

SELECT CompanyName, ContactName FROM Customers

This statement will notify the data source that you want to retrieve all of the values for the CompanyName and ContactName fields from the Customers table. The SELECT statement provides a shorthand method for indicating that you want to retrieve all fields from the specified table. This involves placing a single asterisk as the SELECT list:

SELECT *FROM Customers

This SQL statement will return all fields and all records from the Customers table. It's generally not considered a good practice to use * in the SELECT list as it leaves your code vulnerable to changes in field names or the order of fields in the table. It can also be very resource expensive in large tables, as all columns and rows will be returned whether or not they are actually needed by the client. However, there are times when it is a useful and time-saving shortcut.

Let's say that you wanted to see a list of countries where you have at least one customer located. Simply performing the following query would return one record for every customer in your table.

SELECT Country FROM Customers. This resultset would contain many duplicate country names. The optional DISTINCT keyword allows you to return only unique values in your query:

SELECT DISTINCT Country FROM Customers

The keyword DISTINCT may not be supported by all database vendors. You will have to check with the documentation of each provider to determine the precise grammar and keywords supported by any particular implementation of SQL. If you want to write SQL that is the most transportable, then you want to employ the GROUP BY clause. GROUP BY is ANSI SQL and performs a role similar to the DISTINCT keyword in Access; GROUP BY ensures that each column in the GROUP BY clause has a value represented just one time. You will get the same results as the preceding statement by writing the following ANSI SQL statement:

SELECT Country FROM Customers GROUP BY Country

If you only want to see the list of customers located in the UK, you can use the WHERE clause to restrict the results to only those customers:

SELECT CompanyName, ContactName
FROM Customers
WHERE Country = 'UK'

Note that the string literal UK must be surrounded in single quotes. This is also true of dates. Numeric expressions do not require any surrounding characters.

Finally, suppose that you would like to have your UK customer list sorted by CompanyName. This can be accomplished using the ORDER BY clause:

SELECT CompanyName, ContactName
FROM Customers
WHERE Country = 'UK'
ORDER BY CompanyName

The ORDER BY clause will sort columns in ascending order by default. If instead you wished to sort a field in descending order, you could use the optional DESC specifier immediately after the name of the column whose sort order you wished to modify.

The INSERT Statement

The INSERT statement allows you to add new records to a table. The basic syntax of the INSERT statement follows:

INSERT INTO table_name ( column1, column2 [, columnn])
VALUES (valuel, value2 [, valuen])

Use of the INSERT statement is very simple. You provide the name of the table and its columns that you'll be inserting data into and then provide a list of values to be inserted. You must provide a value in the VALUES clause for each column named in the INSERT clause and the values must appear in the same order as the column names they correspond to. Here's an example showing how to insert a new record into the Customers table:

INSERT INTO Customers (CustomerID, CompanyName, ContactName, Country)
VALUES (‘ABCD’, ‘New Company’, ‘Owner Name’, ‘USA’)

Note that as with the WHERE clause of the SELECT statement, all of the string literals in the VALUES clause are surrounded by single quotes. This is the rule throughout SQL.

If you have provided values for every field in the table in your VALUES clause and the values are in the same order as they are defined in the table, the field list clause can be omitted:

INSERT INTO Customers
VALUES( ‘ALFKJ’, ‘Alfreds Futterkiste’, ‘Maria Anders’, ‘Sales
Representative’,
‘Obere Str. 57’, ‘Berlin’, ‘Hessen’, ‘12209’, ‘Germany’, ‘ 030-0074321’, ‘030-0076545’)

The UPDATE Statement

The UPDATE statement allows you to modify the values in one or more fields of an existing record or records in a table. The basic syntax of the UPDATE statement is the following:

UPDATE tablename
SET columnl = valuel, column2 = value2 [, columnn = valuen]
[WHERE filters]

Even though the WHERE clause of the UPDATE statement is optional, you must take care to specify it unless you are sure that you don't need it. Executing an UPDATE statement without a WHERE clause will modify the specified field(s) of every record in the specified table. For example, if we executed the following statement:

UPDATE Customers
SET Country = 'USA'

Every record in the Customers table would have its Country field modified to contain the value “USA.” There are some cases where this mass update capability is useful, but it can also be very dangerous, because there is no way to undo the update if you execute it by mistake. Consequently, a good rule of thumb while developing queries is to make a backup copy of your database, and work on sample data. This will permit you to fix any mistakes by restoring the database from the backup.

The more common use of the UPDATE statement is to modify the value of a specific record identified by the use of the WHERE clause. Before we look at an example of this usage, we need to discuss a very important aspect of database design called the primary key. The primary key is a column or group of columns in a database table whose values can be used to uniquely identify each record in that table. The primary key in our sample Customers table is the CustomerID field. Each customer record in the Customers table has a unique value for CustomerID. In other words, a specific CustomerID value occurs in one, and only one, customer record in the table.

Let's say that the ContactName changed for the customer “Around the Horn”, whose CustomerID is “AROUT”. We could perform an UPDATE to record that change in the following manner:

UPDATE Customers

SET ContactName = ‘New Name’

WHERE CustomerID = ‘AROUT’

Since we have included a WHERE predicate that is based on the primary key and ensures uniqueness, then only the record belonging to the Around the Horn company will be updated.

The DELETE statement allows you to remove one or more records from a table. The basic syntax of the DELETE statement is the following:

DELETE FROM tablename
[WHERE filter]

As with the UPDATE statement, notice that the WHERE clause is optional. This is probably more dangerous in the case of the DELETE statement; however, because executing a DELETE statement without a WHERE clause will delete every single record in the specified table. Unless you intend to delete all rows, you should always include a WHERE clause in your DELETE statements. Again, while you are developing queries it is a good idea to work on a non-production database. For example, if you are working with an Access database, simply make a copy of the .mdb file and work on the copy. Let's assume that for some reason an entry was made into the Customers table with the CustomerID value of “BONAP” by mistake (maybe they were a supplier rather than a customer). To remove this record from the Customers table we would use the following DELETE statement:

DELETE FROM Customers
WHERE CustomerID = 'BONAP'

Once again, since we used the record's primary key in the WHERE clause, only that specific record will be affected by the DELETE statement.

The CREATE TABLE Statement

The CREATE TABLE statement is used to add new tables to an existing database programmatically. While you are likely to use this statement less frequently than the SELECT, UPDATE, INSERT, or DELETE commands, it is a good statement to know. You may want to use the CREATE TABLE statement to permit consumers to add tables after your solution is employed, to make backup tables, or to support an existing table yet replicate a new, blank table. The syntax for CREATE TABLE follows:

CREATE TABLE tablename (columnnamel typel [, columnnamen typen])

Keep in mind that generally tables will have a couple of useful elements. One is a primary key to aid in searchability. A second feature might be what is referred to as a foreign key. A foreign key is a unique value that is an index of another table. Suppose, for instance, we wanted to extend the Employees table in the Northwind database without changing it. We could add a new table with its own primary key, and a unique column named EmployeeID that is logically related to the Employees.EmployeeID column. Finally, we could add the new information, such as an email address. Such a query could be written as:

CREATE TABLE EmployeeContactInformation

(EmployeeContactInformationID Int Primary Key,
 EmployeeID Int UNIQUE,
 Email Text)

After running the previous query in the Northwind database we would have a new table named EmployeeContactInformation with a primary key of EmployeeContactInformationID, a unique (no duplicates permitted) EmployeeID that represents a logical relationship between our new table and the Employees table, and an extra column named Email, which could be used to store employee e-mail addresses.

Obviously, we could modify the Employees table to include such a column, but this may be undesirable, for example, if the database is provided by an outside source or such a change may break existing applications.

The DROP TABLE Statement

The first programmers were mathematicians. Mathematicians appreciate the notion of symmetry or opposites, a yin and yang, if you will. Hence, if we have a CREATE TABLE statement, then it only seems natural that we have a statement to uncreate tables. The statement to delete a whole table—as opposed to deleting all of the rows in a table—is:

DROP TABLE tablename

Supposing that we created the table EmployeeContactInformation for temporary usage then we could drop the same table with the following statement:

DROP TABLE EmployeeContactInformation.

An Overview of ADO

ADO is Microsoft's universal data-access technology. By universal they mean that ADO is designed to allow access to any kind of data source imaginable, from a SQL Server database, to the Windows 2000 Active Directory, to a text file saved on your local hard disk, and even to non-Microsoft products such as Oracle. All of these things and many more can be accessed by ADO. You can find a wealth of information on ADO in the ADO section of the Microsoft Universal Data Access Web site: http://www.microsoft.com/data/ado/.

ADO doesn't actually access a data source directly. Instead, ADO is a data consumer that receives its data from a lower-level technology called OLE DB. OLE DB cannot be accessed directly using VBA, so ADO was designed to provide an interface that allows you to do so. ADO receives data from OLE DB providers. Most OLE DB providers are specific to a single type of data source. Each is designed to provide a common interface to whatever data its source may contain. One of the greatest strengths of ADO is that, regardless of the data source you are accessing, you use essentially the same set of commands. There's no need to learn different technologies or methods to access different data sources.

Microsoft also provides an OLE DB provider for ODBC. This general-purpose provider allows ADO to access any data source that understands ODBC, even if a specific OLE DB data provider is not available for that data source.

ADO's object model consists of five top-level objects, all of which can be created independently. In this chapter, we'll be covering the Connection object, the Command object, and the Recordset object. ADO also exposes a Record object (not to be confused with the Recordset object), as well as a Stream object. These objects are not commonly used in Excel applications, so it's left to the interested reader to learn more about them from one of the sources mentioned at the beginning of this chapter.

In addition to the five top-level objects, ADO contains four collections, and the objects contained in those collections (for example the Errors collection contains Error objects). Master a handful of classes and you know how to use ADO. Figure 11-2 shows the ADO object model.

Figure 11-2

The next three sections will provide an introduction to each of the top-level ADO objects that we'll be using in this chapter. These sections will provide general information that will be applicable whenever you are using ADO. Specific examples of how to use ADO to accomplish a number of the most common data access tasks you'll encounter in Excel VBA will be covered in the sections that follow.

This is not intended to be an exhaustive reference to ADO. We will only be covering those items whose use will be demonstrated in this chapter, or which we consider particularly important to point out. ADO frequently provides you the flexibility to make the same setting in multiple ways, as both an object property and an argument to a method, for instance. In these cases, we will usually only cover the method we intend to demonstrate in the example sections.

The Connection Object

The Connection object is what provides the pipeline between your application and the data source you want to access. Like the other top-level ADO objects, the Connection object is extremely flexible. In some cases, this may be the only object you need to use. Simple commands can easily be executed directly through a Connection object. In other cases, you may not need to create a Connection object at all. The Command and Recordset objects can create a Connection object automatically, if they need one.

Constructing and tearing down a data source connection can be a time-consuming process. If you will be executing multiple SQL statements over the course of your application, you should create a publicly scoped Connection object variable and use it for each query. This allows you to take advantage of connection pooling.

Connection pooling is a feature provided by ADO that will preserve and reuse connections to the data source rather than creating new connections for each query, which would be a waste of resources. Connections can be reused for different queries as long as their connection strings are identical. This is typically the case in Excel applications, so we recommend taking advantage of it.

Connection Object Properties

In this section, we will examine the important Connection object properties.

The ConnectionString Property

The ConnectionString property is used to provide ADO, and the OLE DB provider that you are using with the information required to connect to the data source. The connection string consists of a semicolon-delimited series of arguments in the form of “name=value;” pairs.

For the purposes of this chapter, the only ADO argument that we will be using is the Provider argument. All other arguments in connection strings presented in this chapter will be specific to the OLE DB provider being used. ADO will pass these arguments directly through to the provider. The Provider argument tells ADO which OLE DB provider to use. The following sample code demonstrates a connection string to the Northwind database using the Jet OLE DB provider:

“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesMicrosoft OfficeOFFICE11SAMPLESNorthwind.mdb;Persist Security Info=False”

The only argument specific to ADO in this string is the Provider argument. All other arguments are passed directly through to the SQL Server OLE DB provider. If a different provider were being used, the arguments would be different as well. We will see this when we begin to connect to various data sources in the example sections. The Provider argument to the connection string is optional. If no provider is specified, ADO uses the OLE DB provider for ODBC by default.

A reasonable person might be concerned about the specificity of the connection string. How on earth are you going to remember a cryptic string of text, especially if the arguments vary based on the OLE DB provider? Well, that's why you buy books like this, so we can show you.

Instead of remembering all of the possible details of any particular OLE DB provider—the proverbial fish offering, we will show you how to catch your own fish—here is a technique that will build the connection string for you. OLE DB is implemented as code in the oledb32.dll. This DLL has a GUI tool that is a dialog box named Data Link Properties (see Figure 11-3). If you create an empty text file and change the extension from .txt to .udl, double-clicking the file will invoke the Data Link Properties dialog box because files with a .udl extension are associated with OLE DB core services. Naturally, one of these core services is defining connections to data providers.

The Data Link Properties dialog box is a wizard. Start on the first tab named Provider, pick a provider and click next. The next tab is the Connection tab. This tab changes slightly based on the information each provider needs. For our example, we pick the Microsoft Jet 4.0 OLE DB Provider, the MS Access OLE DB provider. On the Connection tab we only need to browse to the location of the Northwind.mdb file and we are finished. After you click OK the dialog box is closed and the .udl file is updated with the connection string. Open the .udl file with something like Notepad and copy the connection string from the .udl file. Figure 11-4 shows the contents of the .udl file opened with Notepad. All we need is the last line of text; the first two lines of text represent comments.

Here is some code we could use to initialize an ADO connection object and assign the connection string to the connection object's ConnectionString property. (Remember to use Tools References in the menu item in the VBE to add a reference to the Microsoft ActiveX Data Objects 2.7 Library (ADO).)

Const ConnectionString As String = _
  “Provider=Microsoft.Jet.OLEDB.4.0;” + _
  “Data Source=C:Program FilesMicrosoft ” + _
  “OfficeOFFICE11SAMPLESNorthwind.mdb;Persist Security Info=False”

Figure 11-3

Figure 11-4

Dim Connection As ADODB.Connection
Set Connection = New ADODB.Connection
Connection.ConnectionString = ConnectionString

The first statement declares a constant variable initialized with the connection string we copied from the .udl file. The second statement declares a connection object, followed by initialization of the connection object, and assignment to the connection string property.

The ConnectionTimeout Property

Almost all classes have several members. The Connection class has a ConnectionTimeout property that specifies how long ADO will wait for a connection to complete before giving up and raising an error. The default value is 15 seconds. We rarely spend any time on this value; either the connection is there or it is not. Here is how you can modify the ConnectionTimeout property using the connection object created from the previous section:

Connection.ConnectionTimeout = 30

The State Property

The State property allows you to determine whether a connection is open, closed, connecting, or executing a command. The value will be a bit mask containing one or more of the following ObjectStateEnum constants defined in ADO:

• AdStateClosed

  Means the connection is closed

• AdStateOpen

  Means the connection is open

• AdStateConnecting

  Means the object is in the process of making a connection

• AdStateExecuting

  Means the connection is executing a command

If you attempt to close a connection object that is already closed or open a connection that is already open, then your code will cause an error. You can prevent this from occurring by testing the state of the Connection object before closing it:

If (Connection.State = ObjectStateEnum.adStateOpen) Then Connection.Close

At first, it might seem laughable that code might be written that tries to open an opened connection or close a closed connection, but attempts to open a connection might fail or if the connection is a field in the class then other code paths may have previously opened or closed an existing connection. To prevent this, it is a good idea to test the state of a connection before calling Open or Close.

Connection Methods

Methods define the behavior of objects. A connection presents a connection to a provider. As you might imagine, connections can be opened and closed, but ADO supports some additional convenience behaviors you will find useful.

The Open Method

The Open method establishes a connection to a provider (also called data source, though provider is the currently in vogue term). Open accepts several optional parameters. If we initialize the ConnectionString property first then we can call open with no arguments. If we don't initialize the ConnectionString then we can pass the ConnectionString and things like UserID, Password, and an additional Options argument that we will discuss in a minute. The following code demonstrates how we can initialize a connection object, open a connection, and test the state of that connection:

Const ConnectionString As String = _
  “Provider=Microsoft.Jet.OLEDB.4.0;” + _
  “Data Source=C: Program Files Microsoft ” + _
  “OfficeOFFICE11 SAMPLESNorthwind.mdb;Persist Security Info=False”

Dim Connection As ADODB.Connection
Set Connection = New ADODB.Connection
Connection.ConnectionString = ConnectionString
Connection.Open
MsgBox Connection.State = ObjectStateEnum.adStateOpen

A moment ago we mentioned an additional Options argument. The Options argument allows you to make your connection asynchronously. That is, you can tell your Connection object to go off and open the connection in the background while your code continues to run. You do this by setting the Options argument to the ConnectOptionEnum value adAsyncConnect. The following code sample demonstrates making an asynchronous connection:

objConn.Open Options:=adAsyncConnect

This can be useful if you initialize a connection when your code begins running, but you may not need the connection for a minute and want to let the rest of your code to complete initialization in the mean time.

The Execute Method

The Execute method runs the command text provided to the Execute method's CommandText argument. The Execute method has the following syntax for an action query (one that does not return a result set, like DELETE, INSERT, or UPDATE but not SELECT):

connection.Execute CommandText, [RecordsAffected], [Options]

And for a select query, we assign the return value of the Execute method to a Recordset object:

Set Recordset = connection.Execute(CommandText, _
                         [RecordsAffected], [Options])

The CommandText argument can contain any executable string recognized by the OLE DB provider like a SQL statement. The optional RecordsAffected argument is a return value that tells you how many records the CommandText operation affected. You can check this value against the number of records that you expected to be affected so that you can detect potential errors in your command text.

The Options argument is crucial to optimizing the execution efficiency of your command. Therefore, you should always use it even though it's nominally optional. The Options argument allows you to relay two different types of information to your OLE DB provider: what type of command is contained in the CommandText argument and how the provider should execute the contents of the CommandText argument.

In order to execute the CommandText, the OLE DB provider must know what type of command it contains. If you don't specify the type, the provider will have to determine that information for itself. This will slow down the execution of your query. You can avoid this by specifying the CommandText type using one of the following CommandTypeEnum values:

• AdCmdText

  The CommandText is a raw SQL string.

• AdCmdTable

  The CommandText is the name of a table. This sends an internally generated SQL statement to the provider that looks something like “SELECT * FROM tablename”.

• AdCmdStoredProc

  The CommandText is the name of a stored procedure (we'll cover stored procedures in the section on SQL Server).

• AdCmdTableDirect

  The CommandText is the name of a table. However, unlike adCmdTable, this option does not generate a SQL statement and therefore returns the contents of the table more efficiently. Use this option if your provider supports it.

You can provide specific execution instructions to the provider by including one or more of the ExecuteOptionEnum constants:

• AdAsyncExecute

  Instructs the provider to execute the command asynchronously, which returns execution to your code immediately.

• AdExecuteNoRecords

  Instructs the provider not to construct a Recordset object. ADO will always construct a recordset in response to a command, even if your CommandText argument is not a row-returning query. In order to avoid the overhead required to create an unnecessary recordset, use this value in the Options argument whenever you execute a non-row-returning query.

The CommandTypeEnum and ExecuteOptionEnum values are bit masks that can be combined together in the Options argument using the logical Or operator. For example, to execute a plain text SQL command and return a Recordset object you would use the following syntax:

Const ConnectionString As String = _
  “Provider=Microsoft.Jet.OLEDB.4.0;” + _
  “Data Source=C:Program FilesMicrosoft ” + _
  “OfficeOFFICE11SAMPLESNorthwind.mdb;Persist Security Info=False”

Dim Connection As ADODB.Connection
Set Connection = New ADODB.Connection
Connection.ConnectionString = ConnectionString
Connection.Open

Debug.Print Connection.State = ObjectStateEnum.adStateOpen

Const SQL As String = _
  “SELECT * FROM Customers WHERE Country = ‘USA’”

Dim Recordset As Recordset
Dim RowsAffected As Long
Set Recordset = Connection.Execute(SQL, RowsAffected, CommandTypeEnum. adCmdText)

The preceding code builds on our example. The MsgBox statement was switched to a Debug.Print statement because we don't want a message dialog box to interrupt the smooth flow of our code, but we might like to keep tabs on how things are going. After the Debug.Print statement we declared a SQL statement, a Recordset variable, and a long integer to store the number of rows returned. (If we didn't care whether or not there were any rows returned, we could skip the RowsAffected argument and use an extra comma as a placeholder in the call to Execute as follows.)

Set Recordset = Connection.Execute(SQL, , adCmdText)

The Close Method

When we are finished interacting with a provider we need to close the connection. There are only a finite number of handles for database connections, hence leaving connections open may result in an eventual inability to open future connections and make it impossible for other applications to interact with the same database. The Close method requires no arguments. Combining with our check for the current state being open followed by a call to the Close method can be written as follows:

If (Connection.State = ObjectStateEnum.adStateOpen) Then
  Connection.Close
End If

Connection Object Events and Asynchronous Programming

Synchronous means that things happen in order. Asynchronous means that things happen out of order. Asynchronous programming can be powerful because it permits your code to run background tasks while continuing to do things in the foreground. For example, if a query takes a while to execute and a user can perform other useful tasks in the meantime, then you can eliminate the need for the user to wait on a long task, doing nothing in the mean time.

The basic behavior of an asynchronous call is that the call is made and immediately returns. When an asynchronous call returns it hasn't necessarily finished. The process is actually running on a background thread (modern CPUs and Windows supports multiple threads of execution, which is why you can be listening to Coolio on your Windows Media Player while typing in Word and running a query in Access, all at the same time) and your code continues running on the foreground thread. There are many ways to support notification when an asynchronous background thread has completed; one way supported in Excel is to use an event. By associating an event with a connection the background process can call the event when it's finished permitting the foreground code to keep on running. When used correctly, asynchronous behaviors and events can enhance the performance of your solutions, resulting in a very fluid and responsive application.

To associate events with a Connection object we need to use a WithEvents statement in a class module, declaring a connection object. After we have declared the WithEvents statement, we can use the Object and procedure combo boxes to pick the connection object and the available events, respectively. There are several events, including BeginTransComplete, CommitTransComplete, ConnectComplete, Disconnect, ExecuteComplete, InfoMessage, RollbackTransComplete, WillConnect, and WillExecute. To respond to the completion of an asynchronous command execution, we will implement the ExecuteComplete event. The following code is a revision of the code we have been working on. The revised code shows how to execute the SQL asynchronously and populate a range from the recordset:

1: Option Explicit
2: Private WithEvents AsyncConnection As ADODB.Connection
3:
4: Public Sub AsyncConnectionToDatabase()
5:
6:   Const ConnectionString As String = _
7:     “Provider=Microsoft.Jet.OLEDB.4.0;” + _
8:     “Data Source=C:Program FilesMicrosoft ” + _
9:     “OfficeOFFICE11SAMPLESNorthwind.mdb;” + __
10:    “Persist Security Info=False”
11:
12:  Set AsyncConnection = New ADODB.Connection
13:
14:  AsyncConnection.ConnectionString = ConnectionString
15:  AsyncConnection.Open
16:
17:  Const SQL As String = _
18:     “SELECT * FROM Customers WHERE Country = ‘USA’”
19:
20:  Call AsyncConnection.Execute(SQL, , CommandTypeEnum.adCmdText _
21:    Or ExecuteOptionEnum.adAsyncExecute)
22:
23:  Debug.Print “I ran before the query finished”
24:
25: End Sub
26:
27: Private Sub AsyncConnection_ExecuteComplete( _
28:   ByVal RecordsAffected As Long, _
29:   ByVal pError As ADODB.Error, _
30:   adStatus As ADODB.EventStatusEnum, _
31:   ByVal pCommand As ADODB.Command, _
32:   ByVal pRecordset As ADODB.Recordset, _
33:   ByVal pConnection As ADODB.Connection)
34:
35:   Debug.Print “Query finished”
36:
37:   If (adStatus = EventStatusEnum.adStatusOK) Then
38:     Call Sheet1.Range(“A1”).CopyFromRecordset(pRecordset)
39:   End If
40:
41:   If (pConnection.State = ObjectStateEnum.adStateOpen) Then
42:     pConnection.Close
43:   End If
44: End Sub

Due to the length of the listing, line numbers were added for reference. As always, don't include the line numbers in the VBE editor. The asynchronous methods comprise two statements, but the total code is very similar to what we have been using so far.

Line 2 declares the WithEvents statement. WithEvents will permit us to generate event handlers, including the AsyncConnection_ExecuteComplete method. Calling Execute asynchronously means we don't get the Recordset back from the Execute method and the rest of the code that responds to execute is implemented in the ExecuteComplete event handler. Oring

ExecuteoptionEnum.adAsyncExecute on line 21 makes the Execute call asynchronous. The Debug.Print statement on line 23 was added to illustrate that line 23 runs before the query completes.

The ExecuteComplete event is called asynchronously when the Execute method actually finishes. The arguments to ExecuteComplete make it easy to determine which connections and commands the event is responding to and to retrieve that information. In our example, we make sure the query completed (line 37), and if so, we copy the Recordset into the worksheet starting at cell A1. Line 41 checks to see if the connection is opened and closes it if it is.

The Debug.Print statement on line 23 represents work, but we could include as much code as we'd like after the asynchronous call to Execute. For example, if we were deleting a table then the user would be able to delete the table in the background and continue interacting with our solution with little, if any noticeable, deterioration in performance or wait time between the moment the delete was requested and control was returned to the user.

Exercise some care when using asynchronous behavior. For example, trying to close a connection that is processing a background request will cause an error. Asynchronous program takes a little getting used to but can yield impressive performance improvements.

Connection Object Collections

The Connection Object has two collections, Errors and Properties.

Errors Collection

The Errors collection contains a set of Error objects, each of which represents an OLE DB provider-specific error (ADO itself generates runtime errors). The Errors collection can contain errors, warnings, and messages (generated by the T-SQL PRINT statement, for instance). The Errors collection is very helpful in providing extra detail when something in your ADO code has malfunctioned. When debugging ADO problems, you can dump the contents of the Errors collection to the Immediate window with the following code:

Dim E As Error
  For Each E In pConnection.Errors
    Debug.Print E.Value
  Next

The Properties Collection

The Properties collection contains provider-specific, or extended properties, for the Connection object. Some providers add important settings that you will want to be aware of. Extended properties are beyond the scope of this chapter. You can find more information about them by consulting one of the ADO references mentioned earlier.

The Recordset Object

Just as the most commonly used SQL statement is the SELECT statement, the most commonly used ADO object is the Recordset object. The Recordset object serves as a container for the records and fields returned from a SELECT statement executed against a data source.

Recordset Object Properties

Learning how to use any class is a matter of learning about the Properties that describes a class's state, the methods that describe its behavior, and the events that determine what occurrences your code can respond to. We'll start our exploration of the Recordset by starting with the ActiveConnection property.

The ActiveConnection Property

Prior to opening the Recordset object, you can use the ActiveConnection property to assign an existing Connection object to the Recordset or a connection string for the recordset to use to connect to the database. If you assign a connection string, the recordset will create a Connection object for itself. Once the recordset has been opened, this property returns an object reference to the Connection object being used by the recordset.

The following code assigns a Connection object to the ActiveConnection property:

Set Recordset.ActiveConnection = Connection

The following code assigns a connection string to the ActiveConnection property, resulting in an implicit creation of a connection object:

Recordset.ActiveConnection = “Provider=Microsoft.Jet.OLEDB.4.0;” + _
    “Data Source=C:Program FilesMicrosoft ” + _
    “OfficeOFFICE11SAMPLESNorthwind.mdb;” + _
                                    “Persist Security Info=False”

The BOF and EOF Properties

These properties indicate whether the record pointer of the Recordset object is positioned before the first record in the recordset (BOF, or beginning of file) or after the last record in the recordset (EOF, or end of file). If the recordset is empty, both BOF and EOF will be True. The following code example demonstrates using these properties to determine if there is data in a recordset:

If (Recordset.EOF And Recordset.BOF) Then
    Debug.Print “There is no data”
End If

Note that there is a difference between an empty recordset and a closed recordset. If you execute a query that returns no data, ADO will present you with a perfectly valid open recordset, but one that contains no data. Therefore, you should consider performing a check similar to the preceding code to determine if the recordset is empty or not.

The Recordset also provides a RecordCount property, but some providers don't support this property. For example, Recordset.RecordCount always returns −1 for the Microsoft Jet 4.0 OLE DB Provider. Better checks are to use code that doesn't care if there is no data like Worksheet.CopyFromRecordset, use BOF or EOF, or a For Each statement (because For Each will only process records that exist in the Recordset).

The Filter Property

The Filter property allows you to filter an open recordset so that only records that meet the specified condition are visible, acting like an additional WHERE predicate on the recordset. The records that cannot be seen are not deleted, removed, or changed in any way, but are simply hidden from normal recordset operations. This property can be set to a string that specifies the condition you want to place on the records, or to one of the FilterGroupEnum constants.

You can set multiple filters, and the records exposed by the filtered recordset will be only those records that meet all of the conditions. To remove the filter from the recordset, set the Filter property to an empty string or the adFilterNone constant. The following code sample demonstrates filtering a recordset so that it displays only regions described by the abbreviation OR, as in Oregon:

Recordset.Filter = “Region = ‘OR’”

You can use the logical AND, OR, and NOT operators to set additional Filter property values:

Recordset.Filter = “Region = ‘OR’ AND City = ‘Portland’”

The State Property

The Recordset.State property has the same possible values as the Connection.State property. (Refer to the previous section entitled “The State Property” from earlier in the chapter.)

Recordset Methods

Classes can be large and complex, like the Recordset class. However, if you know what the most commonly used properties and methods are and you understand the general principle behind properties and methods, then you will be able to use a class effectively right away and have the tools necessary to explore further. In this section, we will cover five commonly used methods of the Recordset class.

The Open Method

The Open method retrieves the data and makes it accessible to your code, much like you open a dictionary to look up words. The Open method has the following general syntax:

Call Recordset.Open( Source, ActiveConnection, CursorType, LockType, Options)

The Source argument tells the recordset about the data source. The Source argument can be a SQL statement, a Command object, a table name, a URL, a stored procedure call, or a recordset persisted to a file. Generally, you will use something like a SQL statement.

The ActiveConnection argument can be a connection string or a Connection object that identifies the connection to be used. If you assign a connection string to the ActiveConnection argument, the recordset will create a Connection object for itself.

The CursorType argument specifies the type of cursor to use when opening the recordset. This is set using one of the CursorTypeEnum values. In this chapter, we will demonstrate two (adOpenForwardOnly and adOpenStatic) of the four (adOpenForwardOnly, adOpenStatic, adOpenkeyset, and adOpenDynamic) cursor types. It is up to the reader to devise further exploration.

adOpenForwardOnly means that the recordset can only be negotiated in a forward-only direction from beginning to end. Opening a recordset using adOpenForwardOnly is the fastest but least flexible way of traversing a recordset. Additionally, forward-only data is read-only. The adOpenStatic CursorType is used for disconnected recordsets. adOpenStatic permits random navigation and modification. If you fail to specify a CursorType then adOpenForwardOnly is used by default.

The LockType argument specifies what type of locks the provider should place on the underlying data source when opening the recordset. The five possible lock types are adLockBatchOptimistic, adLockOptimistic, adLockPessimistic, adlockReadOnly, and adLockUnspecified. Later in this chapter, we have provided examples that demonstrate adLockReadOnly and adLockBatchOptimistic. AdlockBatchOptimistic is required for disconnected recordsets where records are updated as a batch. adLockOptimistic locks records when the Update method is called assuming no one has changed records between the time they were retrieved and the time an update occurs. adLockPessimistic locks records immediately after editing begins. adLockReadOnly means the recordset cannot be used to modify records and is consistent with a forward-only recordset, and adLockUnspecified represents an unspecified locking strategy. Further exploration, apart from the examples provided later in this chapter, is left to the reader.

The Options argument here is the same as the Options argument we covered in the Connection object's Execute method earlier in the chapter. It is used to tell the provider how to interpret and execute the contents of the Source argument.

The Close Method

The Close method closes the Recordset object. This does not free any memory used by the recordset. To free up the memory used by the Recordset object, you must set the Recordset object variable to Nothing, but VBA is designed to be programmer-friendly and any object will be cleaned up when the object goes out of scope. For example, if we define a recordset in a method, then the recordset will be removed from memory when the method exits. Contrariwise, some language demand that you explicitly release objects from memory and getting in the habit of setting objects to Nothing (explicitly releasing them) is a good practice.

Methods for Moving the Cursor

When a recordset is first opened the current record pointer is positioned on the first record in the recordset. The Move methods are used to navigate through the records in an open recordset. They do this by repositioning the Recordset object's current record pointer. Listed next are the basic navigation methods that are available:

• MoveFirst

  Positions the cursor to the first record of the recordset.

• MovePrevious

  Positions the cursor to the prior record in the recordset.

• MoveNext

  Positions the cursor to the next record in the recordset.

• MoveLast

  Positions the cursor to the last record in the recordset.

The following code sample demonstrates common recordset navigation handling:

Public Sub RecordsetNavigation()

  Const SQL As String = _
    “SELECT * FROM Customers”
  Const ConnectionString As String = _
    “Provider=Microsoft.Jet.OLEDB.4.0;” + _
    “Data Source=C:Program FilesMicrosoft ” + _
    “OfficeOFFICE11SAMPLESNorthwind.mdb;Persist Security Info=False”

  Dim Recordset As Recordset
  Set Recordset = New Recordset
  Call Recordset.Open(SQL, ConnectionString)

  Recordset.MoveFirst

  While Not Recordset.EOF
    Debug.Print Recordset.Fields(“CompanyName”)
    Recordset.MoveNext
  Wend

End Sub

Pay particular attention to the use of the MoveNext method within the While loop. Omitting this is a very common error and will lead to an endless loop condition in your code.

If we wanted to switch the order of the data in the recordset, we could add a ORDER BY clause and order the data (ORDER BY CompanyName DESC) in descending order or change the CursorType from the default of adOpenForwardOnly to adOpenDynamic and move to the last record and navigate to the first record. The revision is shown here:

Public Sub RecordsetNavigation()

  Const SQL As String = _
    “SELECT * FROM Customers”

  Const ConnectionString As String = _
    “Provider=Microsoft.Jet.OLEDB.4.0;” + _
    “Data Source=C:Program FilesMicrosoft ” + _
    “OfficeOFFICE11SAMPLESNorthwind.mdb;Persist Security Info=False”

  Dim Recordset As Recordset
  Set Recordset = New Recordset
  Call Recordset.Open(SQL, ConnectionString, adOpenDynamic)

  Recordset.MoveLast

  While Not Recordset.BOF
    Debug.Print Recordset.Fields(“CompanyName”)
    Recordset.MovePrevious
  Wend

End Sub

The NextRecordset Method

Some providers allow you to execute commands that return multiple recordsets.
The NextRecordset method is used to move through these recordsets. The
NextRecordset method clears the current recordset from the Recordset object,

loads the next recordset into the Recordset object, and sets the current
record pointer to the first record in that recordset. If the NextRecordset
method is called and there are no more recordsets to retrieve, the Recordset
object is set to Nothing.

Recordset Events

Recordset events must be trapped by creating a WithEvents Recordset object variable in a class module. Trapping these events is necessary whenever you are using a Recordset object asynchronously, since events are used to notify the code when an asynchronous process has completed its task. Asynchronous behavior consistently needs a mechanism to permit notification when the asynchronous behavior has completed. Blocking mechanisms and events are commonly employed in conjunction with asynchronous operations. As with the Connection object, you will be interested in the events that are raised when you invoke asynchronous recordset methods. These include FetchComplete and FetchProgress. You already know how to use the WithEvents statement, and use the VBE editor to generate event methods. We will leave it to you to explore asynchronous behavior of recordsets.

Recordset Collections

We will wrap up our look at the recordset by exploring the objects that the recordset keeps track of. These include the Fields collection, and the Properties collection. A collection is a collection is a collection. When you know how to use one collection you know how to use them all. What varies about a collection is the collected object. Thus, we will be focusing on the class stored in both of the Fields and Properties collection.

The Fields Collection

Fields describe tables, views, and recordsets. A Field is the state of the intersection of a data source's row and column. The Fields collection contains all of the fields in a recordset, referring to one row at a time. The class of the object stored in the Fields collection is the Field class. The Field class is primarily used to store the value of a single row-column intersection. Generally, what you will want to know is the value of the field, but you may need to know the name, type, size, index of the field, or constraints placed on the field. The following example demonstrates how to display the state of a field, providing a good picture of the record and field values:

Public Sub DescribeARow()

  Const SQL As String = _
    “SELECT * FROM Customers”

  Const ConnectionString As String = _
    “Provider=Microsoft.Jet.OLEDB.4.0;” + _
    “Data Source=C:Program FilesMicrosoft ” + _
    “OfficeOFFICE11SAMPLESNorthwind.mdb;Persist Security Info=False”

  Dim Recordset As Recordset
  Set Recordset = New Recordset
  Call Recordset.Open(SQL, ConnectionString, adOpenDynamic)

  Recordset.MoveFirst

  Dim Field As Field
  For Each Field In Recordset.Fields

    Debug.Print    “Name: ” & Field.Name
    Debug.Print    “Type: ” & Field.Type
    Debug.Print    “Size: ” & Field.ActualSize
    Debug.Print    “Value: ” & Field.Value
    Debug Print    “***********************”
  Next

End Sub

The first half of the method initializes the Recordset and fills it. The For Each loop walks across each column in the recordset and dumps some salient data about each field in the Immediate window.

In general, it is useful to be cognizant of every aspect of our environment if we are to master it, but it is unlikely that you will generally need to interact with specific field properties other than the name, index or value. However, these properties exist because they are useful at some level. The most common reason we have found it helpful to read the attributes of a field was to build dynamic applications. For example, in theory if one knows the name, type, size, and value of a field then it is possible to create a graphical user interface on the fly. In VBA this capability isn't opportunistically realized, but in development environments like Delphi or Visual Basic .NET, great technologies and capabilities exist to build dynamic GUIs.

The Properties Collection

The Properties collection contains provider-specific, or extended properties, for the Recordset object. Some providers add important settings that you will want to be aware of. These extended properties are even more varied than the number of database vendors. It is important to note that the properties are in a name and value pairs, like a dictionary; hence, reading one extended property is no different than reading the other. Index the Properties collection by name or number and the associated value is accessible.

The Command Class

The Command class is an object-oriented representation of an instruction to a provider. One of the first languages I programmed in (aside from RomBasic as a young boy) was Databus. Mike Groer showed me how to write Databus code. Databus is a structured language like COBOL. The language depends on state and behavior but not entities with associated behavior. Unfortunately, state and behavior don't completely mirror the physical world because in the physical world we associate state and behavior with instances of specific things. For example, all people breathe (at least those we have met so far) but only some people are pilots. From an architectural perspective, this implies that people are capable of learning but what they have chosen to learn represents the state (or knowledge) of specific people.

As an evolutionary response of greater understanding many things in programming are being wrapped in classes, this includes SQL commands. A string containing SQL text is just a string that can contain any value, but a string contained within a Command object can be required—the behavior being the validation of the string—to be valid SQL. In this section, we'll take a moment to zoom in on the Command class.

Command Properties

Everything in programming is subjective. One of my favorite analogies is Grady Booch's description of a cat. From the perspective of a veterinarian, a cat has specific genus and species and is made of certain biological attributes. From the perspective of a cat-loving grandmother, a cat is a purring, furry, loving, lap companion. (And, from my brother's perspective cats are excellent targets for blowgun practice. Just kidding.) Thus, from a programmer's perspective a cat, and every classes' state and behavior is captured relative to the domain for which we are providing a solution. For a veterinarian, the proper implementation of a cat might be biological. For pet shelters, affection, color, gender, size, and whether the animal has been spayed or neutered might be sufficient to advertise for a new home for the kitty. (And, if programming for my brother, speed, stealth, and hunting success might be sufficient attributes to describe and record cats. Still kidding.) Getting back to Command classes even these are likely to evolve over time. Twenty years ago all data sources may have been monolithic, flat text files. Ten years ago the notion of logical relationships may have been incorporated, and currently just about everything, including XML files, are encompassed by the concept data source. Consequently, the current notion of a Command includes the names of tables, SQL, and stored procedures. For our purposes, we'll look at a snapshot of the state and capabilities offered by Commands to date.

The ActiveConnection Property

The ActiveConnection property, as discussed earlier, represents a connection object whether explicitly initialized or implicitly created via a connection string. (Refer to earlier code examples demonstrating how to use the ActiveConnection property.)

The CommandText Property

The CommandText property is used to set the string value that will be used by the data provider to figure out what information to fill the recordset with.

The CommandType Property

The CommandType property represents a hint to the provider indicating the meaning of the CommandText property. If the CommandText represents a stored procedure then the CommandType needs to be CommandTypeEnum.adStoredProcedure. If the CommandText represents a persisted recordset file name then the CommandType needs to be CommandTypeEnum.adCmdFile. Refer to the Help documentation on Command.CommandText and Command.CommandType for more information.

Command Methods

There are only three Command methods. (Keep in mind that ease of use, utility, and robustness represent how we measure goodness when it comes to classes. Thus, having only three methods does not mean a class is not important or useful.) Let's take a moment to look at the utility of the CreateParameter, Cancel, and Execute methods.

The CreateParameter Method

This method is used to manually create Parameter objects that can then be added to the Command.Parameters collection. The CreateParameter object has the following syntax:

CreateParameter([Name As String], _
 [Type As DataTypeEnum = adEmpty], _
 [Direction As ParameterDirectionEnum = adParamInput], _
 [Size As ADO_LONGPTR], [Value]) As Parameter

Name is the name of the argument. You can use this name to reference the Parameter object through the Command object's Parameters collection. When working with SQL Server, the name of a Parameter should be the same as the name of the stored procedure argument that it corresponds to.

Type indicates the data type of the parameter. It is specified as one of the DataTypeEnum constants. Each of the types correlates to a type you might pass to a stored procedure. The Help documentation for DataTypeEnum covers these in detail.

Direction is a ParameterDirectionEnum value that indicates whether the parameter will be used to pass data to an input argument, receive data from an output argument, or accept a return value from a stored procedure. The parameter enumerations—adParamInput, adParamInputOutput, adParamOutput, and adParamReturnValue—are self-describing.

The Size argument is used to indicate the size of the value in bytes, and Value represents the value of the argument to be passed to the command.

The following code sample demonstrates how you can use the CreateParameter method in conjunction with the Parameters collection Append method to create a Parameter and append it to the Parameters collection with one line of code. The next exampleconnects to a SQL Server instance of the Northwind database and calls the Sales by Year stored procedure:

1: Public Sub CallStoredProcedure()
2:
3:   Const ConnectionString As String = _
4:     “Provider=SQLOLEDB.1;Integrated Security=SSPI;” + _
5:     “Persist Security Info=False;Initial Catalog=NorthwindCS;” + _
6:     “Data Source=LAP80 0;Workstation ID=LAP800;”
7:
8:
9:   Dim Command As Command
10:  Set Command = New Command
11:
12:  Command.ActiveConnection = ConnectionString
13:  Command.CommandText = “[Sales by Year]”
14:  Command.CommandType = CommandTypeEnum.adCmdStoredProc
15:
16:  Dim BeginningDate As ADODB.Parameter
17:  Dim EndingDate As ADODB.Parameter
18:
19:  Dim StartDate As Date
20:  StartDate = #1/1/1995#
21:
22:  Dim EndDate As Date
23:  EndDate = #1/1/2004#
24:
25:  Set BeginningDate = Command.CreateParameter(“@Beginning_Date”, _
26:    DataTypeEnum.adDate, ParameterDirectionEnum.adParamInput, , StartDate)
27:
28:  Set EndingDate = Command.CreateParameter(“@Ending_Date”, _
29:    DataTypeEnum.adDate, ParameterDirectionEnum.adParamInput, , EndDate)
30:
31:  Call Command.Parameters.Append(BeginningDate)
32:  Call Command.Parameters.Append(EndingDate)
33:
34:  Dim Recordset As ADODB.Recordset
35:  Set Recordset = Command.Execute

36:
37:  Call Sheet1.Range(“A1”).CopyFromRecordset(Recordset)
38:
39:End Sub

The new connection string is defined on lines 3 through 6. Line 9 and 10 declare and initialize a Command object. Line 12 associates the Command with the connection string, which causes the Command object to create a Connection object. Alternatively, we could create a Connection object and assign the Connection object to the ActiveConnection property. Line 13 sets the name of the stored procedure, and line 14 sets the CommandType to CommandTypeEnum.adCmdStoredProc telling the Command object how to treat the CommandText. Lines 16 and 17 declare the two parameters we need to pass to Sales by Year. Lines 19 through 23 declare and initialize the date values that will be used to initialize the parameters. We don't need to write the code in such a verbose way, but it makes it easier to debug the code and is clearer what is going on.

Lines 25 and 26 and lines 28 and 29 use the Command.CreateParameter method to initialize each parameter. The parameter names can be read from the stored procedure. The parameter type can be determined in the same way. Since these two parameters will be used as input arguments, we initialize the parameters with ParameterDirectionEnum.adParamInput and their respective values. Finally, we finish up by adding the parameter objects to the Command's Parameters collection (lines 31 and 32), execute the command (line 35), and copy the resultset into a worksheet.

The Execute Method

This method executes the command text in the Command object's CommandText property. The Execute method has the following syntax for an action query (one that does not return a resultset):

Call Command.Execute( [RecordsAffected], [Parameters], [Options])

And for a select query:

Set Recordset = Command.Execute([RecordsAffected], [Parameters], [Options])

The RecordsAffected and Options arguments are identical to the corresponding arguments for the Connection object's Execute method described in the Connection Methods section earlier. If you are executing a SQL statement that requires one or more parameters to be passed, you can supply an array of values to the Parameters argument, one for each parameter required. (Refer to the sample at the end of the preceding section for an example.)

Command Collections

The example in the CreateParameter section earlier demonstrated all of the constituent parts needed to prepare and run a command. Let's wrap up our discussion with a brief review of the Parameters and Properties collection.

The Parameters Collection

The Parameters collection contains all of the Parameter objects associated with the Command object. Parameters are used to pass arguments to SQL statements and stored procedures as well as to receive output and return values from stored procedures.

The Properties Collection

The Properties collection contains provider-specific or extended properties for the Command object. Some providers add important settings that you will want to be aware of. Find out more about provider-specific properties by exploring the Help documentation for each provider in which you are interested.

Using ADO in Microsoft Excel Applications

Here's where it all comes together. In this section, we will combine the understanding of Excel programming that you've gained from previous chapters along with the SQL and ADO techniques discussed earlier. Excel applications frequently require data from outside sources. The most common of these sources are Access and SQL Server databases. However, we've created applications that required source data from mainframe text file dumps and even Excel workbooks. As we'll see, ADO makes acquiring data from these various data sources easy.

To run the code examples, select the Tools References menu item from within the VBE. This will bring up the References dialog box. Scroll down until you find the alphabetically located entry labeled Microsoft ActiveX Data Objects 2.7 Library. Place a checkmark beside this entry (as seen in Figure 11-5) and click OK.

Figure 11-5

Note that it's perfectly normal to have multiple versions of the ADO object library available.

Using ADO with Microsoft Access

The rest of this chapter contains additional examples demonstrating how to use MS Access and MS SQL Server databases. We will continue our discussion with MS Access examples and wrap up with some MS SQL Server examples. (It is important to keep in mind that just about any data source can be used with ADO, but the examples in this chapter were chosen for the general availability of the sample databases.)

Connecting to Microsoft Access

ADO connects to Microsoft Access databases through the use of the OLE DB provider for Microsoft Jet (Jet refers to the database engine used by Access). We will be using version 4.0 of this provider. To connect to a Microsoft Access database, you simply specify this provider in the ADO connection string and then include any additional provider-specific arguments required. Earlier in the chapter, we used the Data Link Properties dialog box to build the connection string. Let's take a moment to dissect the connection string used to connection to an Access database:

• Provider=Microsoft.Jet.OLEDB.4.0

  The Provider is a required part of the connection string that describes the OLE DB provider used for a specific connection

• Data Source=[full path and filename to the Access database]

  The Data Source is a required part of the connection string used to indicate the file path to the database

• Mode=mode

  The Mode is an optional part of the connection string that describes the kinds of operations that can be performed on the data source. Check the ConnectModeEnum for all of the available connection types. Three commonly used settings for this property are:

  • adModeShareDenyNone

    Opens the database and allows complete shared access to other users. This is the default setting if the Mode argument is not specified.

  • adModeShareDenyWrite

    Opens the database and allows other users read-access but prevents write-access.

  • adModeShareExclusive

    Opens the database in exclusive mode, which prevents any other users from connecting to the database.

• User ID=username

  The User ID is an optional argument used to authenticate the user. If the database requires a username and it is not supplied, then the connection will fail.

• Password=password

  The password is an optional argument that can be paired with the User ID to authenticate the connection request. If the database is password protected then this argument is required.

The following example showcases a connection string demonstrating all of the elements mentioned in the preceding list:

Public Const ConnectionString As String = _
    “Provider=Microsoft.Jet.OLEDB.4.0;” & _
    “Data Source=C:FilesNorthwind.mdb;” & _
    “Mode=Share Exclusive;” & _
    “User ID=Admin;” & _
    “Password=password”

It is worth pointing out that if we initialize a Connection object with a complete connection string, then we use the representative values for elements like the Mode. For example, if we define an entire connection string then we might use the mode Share Exclusive, but if we initialized the Connection.Mode property in code then we'd use the equivalent ConnectModeEnum.adModeShareExlcusive. A reliable technique for building connection strings for any provider is to use the Data Link Properties dialog box.

Retrieving Data from Microsoft Access Using a Plain Text Query

The following procedure demonstrates how to retrieve data from a Microsoft Access database using a plain text query and place it on an Excel worksheet:

Public Sub PlainTextQuery()

  Const ConnectionString As String = _
    “Provider=Microsoft.Jet.OLEDB.4.0;” + _
    “Data Source=C:Program FilesMicrosoft ” + _
    “OfficeOFFICE11SAMPLESNorthwind.mdb;Persist Security Info=False”

  Dim Recordset As ADODB.Recordset

  ' Define the SQL Statement
  Const SQL As String = _
     “SELECT CompanyName, ContactName ” & _
     “FROM Customers ” & _
     “WHERE Country = ‘UK’ ” & _
     “ORDER BY CompanyName”

  ' Initialize the Recordset object and run the query
  Set Recordset = New ADODB.Recordset
  Call Recordset.Open(SQL, ConnectionString, CursorTypeEnum.
adOpenForwardOnly, _
    LockTypeEnum.adLockReadOnly, CommandTypeEnum.adCmdText)

  ' Make sure we got records back
  If Not Recordset.EOF Then
    ' Dump the contents of the recordset onto the worksheet
    Call Sheet1.Range(“A2”).CopyFromRecordset(Recordset)

    ' Add headers to the worksheet
    With Sheet1.Range(“A1:B1”)
    .Value = Array(“Company Name”, “Contact Name”)
    .Font.Bold = True
  End With

    ' Fit the column widths to the data
    Sheet1.UsedRange.EntireColumn.AutoFit
  Else
    Call MsgBox(“Error: No records returned.”, vbCritical)
  End If

  ' Close the recordset if it is still open
  If (Recordset.State And ObjectStateEnum.adStateOpen) Then Recordset.Close
  Set Recordset = Nothing

End Sub

The only ADO object used was the Recordset object. As mentioned at the beginning of the ADO section, all of the top-level ADO objects can be created and used independently. If we were going to perform multiple queries over the course of our application, we would have created a separate, publicly scoped Connection object in order to take advantage of ADO's connection-pooling feature.

The syntax of the Recordset.Open method has been optimized for maximum performance. We've told the provider what type of command is in the Source argument (adCmdText, a plain text query) and we've opened a forward-only, read-only cursor. The CursorLocation property is not specified. This type of cursor is often referred to as a firehose cursor, because it's the fastest way to retrieve data from a database.

We do not make any modifications to the destination worksheet until we are sure we have successfully retrieved data from the database. This avoids having to undo anything if the query fails. If we have data, then the CopyFromRecordset method is used to move the data from the Recordset into the worksheet. And, finally, we format the column headers, auto-fit the column data and close the Recordset.

Retrieving Data from Microsoft Access Using a Stored Query

Microsoft Access allows you to create and store SQL queries in the database. You can retrieve data from these stored queries just as easily as you can use a plain text SQL statement. The following procedure demonstrates how to invoke a query defined in the Access database:

Public Sub SavedQuery()

  Dim Field As ADODB.Field
  Dim Recordset As ADODB.Recordset
  Dim Offset As Long

  Const ConnectionString As String = _
    “Provider=Microsoft.Jet.OLEDB.4.0;” + _
    “Data Source=C:Program FilesMicrosoft ” + _
    “OfficeOFFICE11SAMPLESNorthwind.mdb;Persist Security Info=False”

  ' Create the Recorset object and run the query.
  Set Recordset = New ADODB.Recordset
  Call Recordset.Open(“[Sales By Category]”, ConnectionString, _
    CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockReadOnly, _
    CommandTypeEnum.adCmdTable)

  ' Make sure we got records back
  If Not Recordset.EOF Then
    ' Add headers to the worksheet.
    With Sheet1.Range(“A1”)
      For Each Field In Recordset.Fields
        .Offset(0, Offset).Value = Field.Name
    Offset = Offset + 1
      Next Field
      .Resize(1, Recordset.Fields.Count).Font.Bold = True
    End With

    ' Dump the contents of the recordset onto the worksheet.
    Call Sheet1.Range(“A2”).CopyFromRecordset(Recordset)

    ' Fit the column widths to the data.
    Sheet1.UsedRange.EntireColumn.AutoFit
  Else
    MsgBox “Error: No records returned.”, vbCritical
  End If

  ' Close the recordset
  Recordset.Close
  Set Recordset = Nothing

End Sub

Examine the differences between the Recordset.Open method used in this procedure and the one used in the plain text query. In this case, rather than providing a SQL string we specified the name of the stored query that we wanted to execute. We also told the provider that the type of query being executed was a table query. The Jet OLE DB provider treats stored queries and queries of entire database tables in the same manner.

Since we did not create the SQL statement ourselves, we did not know the names of the fields we were retrieving, or even how many fields there were. Therefore, in order to create the correct set of headers for each column in the destination worksheet we needed to loop the Fields collection of the Recordset object and determine this information dynamically. In order to accomplish this, the recordset had to be open, so we added the fields to the worksheet prior to closing the recordset in this case.

Inserting, Updating, and Deleting Records with Plain Text SQL in Microsoft Access

Executing plain text INSERT, UPDATE, and DELETE statements use virtually identical preparation code. Therefore, we'll examine these action queries by inserting a new record, updating that record, and then deleting it, all within the same procedure. This, of course, is not normally something you would do. You can take this generic procedure, however, and create a single purpose insert, update, or delete procedure by simply removing the sections that you don't need.

We're going to use the Shippers table from the Northwind database in the next procedure. The original contents of this table are shown in Figure 11-6. There are two things that you'll want to keep in mind before we begin to alter this table. First, you'll notice that the column headings in the table are slightly different from the field names that we'll use in our SQL statements. This is because Access allows you to assign a Caption property to each field in a table, which is then displayed instead of the underlying field name. However, you must use the underlying field name in SQL statements. Second, notice that the last row in the Shipper ID column contains the value “(AutoNumber)”. This isn't really a value; rather it's a prompt that alerts you to the fact that values for the Shipper ID column are automatically generated by the Jet database engine. This column is the primary key for the Shippers table and AutoNumber fields are a common method used to generate the unique value required for the primary key. As you'll see in the following example, you don't (and can't), set or change the value of an AutoNumber field. If you need to maintain a reference to a new record that you've inserted into the table, you'll need to retrieve the value that was assigned to that record by the AutoNumber field. We'll show how this is done in the following example:

Figure 11-6

1: Public Sub CheckError(ByVal RecordsAffected As Long, _
2:   ByVal Expected As Long, ByVal Description As String)
3:
4:  If RecordsAffected <> Expected Then
5:    Call RaiseError(Description)
6:  End If
7:
8: End Sub
9:
10: Public Sub RaiseError(ByVal Description As String)
11:   Call Err.Raise(vbObjectError + 1024, , Description)
12: End Sub
13:
14:
15: Public Function GetPrimaryKey(ByVal Command As ADODB.Command) As Long
16:
17:   Dim RecordsAffected As Long
18:   Dim Recordset As ADODB.Recordset
19:
20:   ' Retrieve the primary key generated for our new record.
21:   Command.CommandText = “SELECT @@IDENTITY”
22:
23:   Set Recordset = Command.Execute(Options:=CommandTypeEnum.adCmdText)
24:
25:   If Recordset.EOF Then
26:    Call RaiseError(“Error retrieving primary key value.”)
27:   End If
28:

29:   GetPrimaryKey = Recordset.Fields(0).Value
30:   Recordset.Close
31:
32: End Function
33:
34: Public Sub ExecuteCommand(ByVal Command As ADODB.Command, _
35:   ByVal CommandText As String, _
36:   ByVal Description As String)
37:
38:   Dim RecordsAffected As Long
39:   Command.CommandText = CommandText
40:
41:   Call Command.Execute(RecordsAffected, , _
42:    CommandTypeEnum.adCmdText Or ExecuteOptionEnum.adExecuteNoRecords)
43:
44:   Call CheckError(RecordsAffected, 1, Description)
45:
46: End Sub
47:
48:
49: Public Sub InsertRecord(ByVal Command As ADODB.Command)
50:
51:   Const CommandText As String = _
52:    “INSERT INTO Shippers(CompanyName, Phone) ” & _
53:    “VALUES(‘Air Carriers’, ‘(205) 555 1212’)”
54:
55:   Const Description As String = _
56:    “Error executing INSERT statement.”
57:
58:   Call ExecuteCommand(Command, CommandText, Description)
59:
60: End Sub
61:
62: Public Sub UpdateRecord(ByVal Command As ADODB.Command, ByVal Key As Long)
63:
64:   Dim CommandText As String
65:   CommandText = _
66:    “UPDATE Shippers SET Phone=‘(206) 546 0086’ ” & _
67:    “WHERE ShipperID=” & CStr(Key) & “;”
68:
69:   Const Description As String = _
70:    “Error executing UPDATE statement.”
71:
72:   Call ExecuteCommand(Command, CommandText, Description)
73:
74: End Sub
75:
76: Public Sub DeleteRecord(ByVal Command As ADODB.Command, ByVal Key As Long)
77:
78:   Dim CommandText As String
79:   CommandText = “DELETE FROM Shippers WHERE ShipperID = ” & CStr(Key) & “;”
80:
81:   Const Description As String = _
82:    “Error executing DELETE statement.”

83:
84:   Call ExecuteCommand(Command, CommandText, Description)
85:
86: End Sub
87:
88: Private Property Get ConnectionString() As String
89:   ConnectionString = _
90:    “Provider=Microsoft.Jet.OLEDB.4.0;” + _
91:    “Data Source=C:Program FilesMicrosoft ” + _
92:    “OfficeOFFICE11SAMPLESNorthwind.mdb;Persist Security Info=False”
93:
94: End Property
95:
96:
97: Public Sub InsertUpdateDelete()
98:
99:   Dim Command As ADODB.Command
100:  Dim Key As Long
101:
102:  On Error GoTo ErrorHandler
103:
104:  Set Command = New ADODB.Command
105:  Command.ActiveConnection = ConnectionString
106:
107: Call InsertRecord(Command)
108: Key = GetPrimaryKey(Command)
109:
110: Call UpdateRecord(Command, Key)
111:
112: Call DeleteRecord(Command, Key)
113:
114: ErrorExit:
115:   Set Command = Nothing
116:   Exit Sub
117:
118: ErrorHandler:
119:   Call MsgBox(Err.Description, vbCritical)
120:   Resume ErrorExit
121: End Sub

There are about many ways to decompose a solution. We prefer many small, self-documenting methods that can be reused, easily understood at a glance, and consequently easy to reorder and debug. The preceding listing demonstrates this style. Some programmers (and authors) will write large methods with a lot of comments, but these monolithic methods generally end up being single-purpose methods. The example program is decomposed into several parts: CheckError, RaiseError, GetPrimaryKey, ExecuteCommand, InsertRecord, UpdateRecord, DeleteRecord, and InsertUpdateDelete.

CheckError and RaiseError are used to compare the records affected and raise an error if the result doesn't match the expected result. GetPrimaryKey, InsertRecord, UpdateRecord, and DeleteRecord all rely on ExecuteCommand to send a query to the database and check the results. GetPrimaryKey is called immediately after the INSERT command (see lines 107 and 108) sending SELECT IDENTITY to the database; this query will get the recently generated primary key from an insert statement. The UpdateRecord and DeleteRecord demonstrate how to use the primary key to find specific records. Finally, the InsertUpdateDelete method exercises the various queries, tying it all together by demonstrating how to factor code for reuse and employ error handling to ensure things get tidied up (see lines 114 through 121).

Using ADO with Microsoft SQL Server

In the previous section on Microsoft Access, we covered the basics of performing the various types of queries in ADO. Since ADO is designed to present a common gateway to different data sources, there isn't a lot of difference in these basic operations whether your database is in Access or in SQL Server. Therefore, after a brief introduction to the few important differences that arise when using ADO with SQL Server, in this section, we'll cover more advanced topics, including stored procedures, multiple recordsets, and disconnected recordsets. We won't be going into a lot of detail on how to use SQL Server itself, as that is beyond the scope of this chapter. If you'd like to learn more about SQL Server, one of the best references available is Robert Vieira's Professional SQL Server 2000 from Wrox press, ISBN 1-861004-48-6.

Connecting to Microsoft SQL Server

To connect to a Microsoft SQL Server database, you simply specify the OLE DB provider for SQL Server in the ADO connection string, and then include any additional provider-specific arguments required. The following is a summary of the connection string arguments you will most frequently use when connecting to a SQL Server database:

• Provider=SQLOLEDB;
• Data 2Source=servername;

  In the case of SQL Server 7.0, this will almost always be the NetBIOS name of the computer that SQL Server is installed on. SQL Server 2000 added the ability to install multiple SQL Servers on one machine, so the server name will have the following syntax: NetBIOS nameSQL Server name. If SQL Server is installed on the same machine as your spreadsheet, you can use the name localhost.

• Initial Catalog=databasename;

  Unlike Access, one instance of SQL Server can contain many databases. This argument will be the name of the database you want to connect to.

• User ID=username;

  The username for SQL Server authentication.

• Password=password;

  The password for SQL Server authentication.

• Network Library=netlib;

  By default, the SQL Server OLE DB provider will attempt to use named pipes network protocol to connect to SQL Server. This is required for using Windows integrated security (explained next). There are many instances, however, where it is not possible to use named pipes. These include accessing SQL Server from a Windows 9x operating system and accessing SQL Server over the Internet. In these cases, the preferred protocol for connecting to SQL Server is TCP/IP. This can be specified on each machine by using the SQL Server Client Network Utility, or you can simply use the Network Library connection string argument to specify the name of the TCP/IP network library, which is dbmssocn.

• IntegratedSecurity=SSPI;

  This connection string argument specifies that you want to use Windows integrated security rather than SQL Server authentication. The User ID and Password arguments will be ignored if this argument is present.

A Note About SQL Server Security

There are three types of security that SQL Server can be set to use: SQL Server authentication, Windows integrated security, and mixed mode. SQL Server authentication means that separate user accounts must be added to SQL Server and each user must supply a SQL Server username and password to connect.

This type of security is most commonly used when SQL Server must be accessed from outside the network. With Windows integrated security, SQL Server recognizes the same usernames and passwords that are used to log in to the Windows NT/2000 network. Mixed mode simply means you can use either one of the first two.

The following is an example of a connection string that shows some of the elements of a basic connection. Keep in mind that you can use the Data Link Properties dialog box to build connection strings for SQL Server and many other providers too:

Const ConnectionString As String = _
    “Provider=SQLOLEDB.1;Integrated Security=SSPI;” + _
    “Persist Security Info=False;Initial Catalog=NorthwindCS;” + _
    “Data Source=LAP800;Workstation ID=LAP800;”

Microsoft SQL Server Stored Procedures

The syntax for executing plain text queries against SQL Server is identical to that which we used in the example for Access. The only difference is the contents of the connection string. Since you already know how to send text queries, we will take a closeup look at invoking stored procedures stored in a SQL Server database.

Stored procedures are simply precompiled SQL statements that can be accessed by name from the database. They are much like VBA procedures in that they can accept arguments and return values. Following is an example of a simple stored procedure that queries the Orders table and Order Subtotals view:

ALTER Procedure dbo.[Employee Sales by Country]
@Beginning_Date datetime,
@Ending_Date datetime AS
SELECT Employees.Country, Employees.LastName,
  Employees.FirstName, Orders.ShippedDate, Orders.OrderID,
  “Order Subtotals”.Subtotal AS SaleAmount FROM Employees
INNER JOIN (Orders INNER JOIN “Order Subtotals”
 ON Orders.OrderID = “Order Subtotals”.OrderID)
 ON Employees.EmployeeID = Orders.EmployeeID
WHERE Orders.ShippedDate
  Between @Beginning_Date And @Ending_Date

This stored procedure takes two arguments, @Beginning_Date and @Ending_Date, and returns a recordset containing the values for the fields specified in the SELECT list for Orders and Order subtotals whose ShippedDate falls between the Beginning_Date and Ending_Date parameters.

ADO provides a very quick and simple way to execute stored procedures using the Connection object. ADO treats all stored procedures in the currently connected database as dynamic methods of the Connection object. You can call a stored procedure exactly like any other Connection object method, passing any arguments to the stored procedure as method arguments and optionally passing a Recordset object as the last argument if the stored procedure returns a result set.

This method is best used for “one off” procedures rather than those you will execute multiple times, since it isn't the most efficient method. However, it is significantly easier to code. The following exampledemonstrates executing the preceding stored procedure as a method of the Connection object:

Public Sub ExecuteStoredProcAsMethod()

  Dim Connection As ADODB.Connection
  Dim Recordset As ADODB.Recordset

  Const ConnectionString As String = _
    “Provider=SQLOLEDB.1;Integrated Security=SSPI;” + _
    “Persist Security Info=False;Initial Catalog=NorthwindCS;” + _
    “Data Source=LAP800;Workstation ID=LAP800;”

  On Error GoTo Cleanup
    Set Connection = New ADODB.Connection
    Set Recordset = New ADODB.Recordset

    Call Connection.Open(ConnectionString)

    Dim StartDate As Date, EndDate As Date
    StartDate = #1/1/1995#
    EndDate = #1/1/2004#
    Connection.Employee_Sales_by_Country StartDate, EndDate, Recordset

    Call Sheet1.Range(“A1”).CopyFromRecordset(Recordset)

    Sheet1.UsedRange.EntireColumn.AutoFit

  Cleanup:
  If (Err.Number <> 0) Then Debug.Print Err.Description
  If (Connection.State = ObjectStateEnum.adStateOpen) Then Connection.Close
  If (Recordset.State = ObjectStateEnum.adStateOpen) Then Recordset.Close

  End Sub

In the preceding procedure, we executed our Employee_Sales_by_Country stored procedure and passed it the values #1/1/1995# for the @Beginning_Date, #1/1/2004 for the @Ending_Date. This populated the recordset with all of the employee sales between 1995 and 2004. Note that the Connection object must be opened before the dynamic methods are populated and that you must instantiate the Recordset object prior to passing it as an argument.

The most efficient way to handle stored procedures that will be executed multiple times is to prepare a publicly scoped Command object to represent them. The Connection will be stored in the Command object's ActiveConnection property, the stored procedure name will be stored in the Command object's CommandText property, and any arguments to the stored procedure will be used to populate the Command object's Parameters collection.

Once this Command object has been created, it can be executed as many times as you like over the course of your application without incurring the overhead required to perform the previously described tasks with each execution.

For this example, let's create a simple stored procedure that we can use to insert new records into our Shippers table:

ALTER PROCEDURE InsertShippers
  @CompanyName nvarchar(40),
  @Phone nvarchar(24)
AS
INSERT INTO Shippers
(CompanyName, Phone)
VALUES (@CompanyName, @Phone)
RETURN @@IDENTITY

CREATE PROCEDURE InsertShippers @CompanyName nvarchar(4 0), @Phone
nvarchar(24) AS
INSERT INTO Shippers

As you can see, the stored procedure in the preceding code has two arguments, @CompanyName and @Phone, which are used to collect the values to insert into those respective fields in the Shippers table. However, as you may recall from our Access example, the Shippers table has three fields, and the stored procedure above doesn't reference the first field, ShipperID, anywhere.

This is because, similar to the ShipperID field in the Access version of the Northwind database, the ShipperID field in the SQL Server version of Northwind is populated automatically by the database any time a new record is inserted. We also retrieve this automatically assigned value in a similar fashion; through the use of SQL Server's @@IDENTITY system function. In this case, however, we won't have to make a separate query to retrieve the Shipper ID value since it will be returned to us by the stored procedure.

In order to present a more realistic application scenario, the following example uses publicly scoped Connection and Command objects, procedures to create and destroy the connection, a procedure to prepare the Command object for use, and a procedure that demonstrates how to use the Command object:

Option Explicit

Private Const ConnectionString As String = _
  “Provider=SQLOLEDB.1;Integrated Security=SSPI;” + _
  “Persist Security Info=False;Initial Catalog=NorthwindCS;” + _
  “Data Source=LAP800;Workstation ID=LAP800;”

Public Command As ADODB.Command
Public Connection As ADODB.Connection

Private Sub CreateConnection()
  Set Connection = New ADODB.Connection
  Call Connection.Open(ConnectionString)
End Sub

Private Sub DestroyConnection()
  If (Connection.State = ObjectStateEnum.adStateOpen) Then
    Connection.Close
  End If

  Set Connection = Nothing
End Sub

Private Sub PrepareCommandObject()

  Set Command = New ADODB.Command
  Set Command.ActiveConnection = Connection
  Command.CommandText = “InsertShippers”
  Command.CommandType = adCmdStoredProc

  Call Command.Parameters.Append( _
    Command.CreateParameter(“@RETURN_VALUE”, DataTypeEnum.adInteger, _
      ParameterDirectionEnum.adParamReturnValue, 0))

  Call Command.Parameters.Append( _
    Command.CreateParameter(“@CompanyName”, DataTypeEnum.adVarWChar, _
      ParameterDirectionEnum.adParamInput, 40))

  Call Command.Parameters.Append( _
    Command.CreateParameter(“@Phone”, DataTypeEnum.adVarWChar, _
      ParameterDirectionEnum.adParamInput, 24))

End Sub

Public Sub UseCommandObject()
  Dim Key As Long
  Dim RecordsAffected As Long

  On Error GoTo ErrorHandler

  CreateConnection
  PrepareCommandObject

  Command.Parameters(“@CompanyName”).Value = “Air Carriers”
  Command.Parameters(“@Phone”).Value = “(206) 555 1212”

  Call Command.Execute(RecordsAffected, , ExecuteOptionEnum.adExecuteNoRecords)
  If (RecordsAffected <> 1) Then
    Call Err.Raise(vbObjectError + 1024, , _
        Description:=“Error executing Command object.”)
  End If

  Key = Command.Parameters(“@RETURN_VALUE”).Value
  Debug.Print “The key value of the new record is: ” & CStr(Key)

ErrorExit:

    Set Command = Nothing
    DestroyConnection
    Exit Sub

ErrorHandler:
    Call MsgBox(Err.Description, vbCritical)
    Resume ErrorExit
End Sub

In a normal application you would not create and destroy the Connection and Command objects in the UseCommandObject procedure. These objects are intended for reuse and therefore, would typically be created when your application first started and destroyed just before it ended.

When constructing and using the Command object's Parameters collection, keep in mind that the first parameter is always reserved for the stored procedure return value, even if the stored procedure doesn't have a return value.

Even though we didn't make any particular use of the Shipper ID value returned from the stored procedure for the new record, in a normal application this value would be very important. The CompanyName and Phone fields are for human consumption; the primary key value is how the database identifies the record. For example, in the Northwind database the Shipper ID is a required field for entering new records into the Orders table. Therefore, if you planned on adding an order that was going to use the new shipper you would have to know the Shipper ID.

Multiple Recordsets

The SQL Server OLE DB provider is an example of a provider that allows you to execute a SQL statement that returns multiple recordsets. This feature comes in very handy when you need to populate multiple controls on a form with lookup-table information from the database. You can combine all of the lookup-table SELECT queries into a single stored procedure and then loop through the individual recordsets, assigning their contents to the corresponding controls.

For example, if you needed to create a user interface for entering information into the Orders table you would need information from several related tables, including Customers and Shippers (shown in Figure 11-7).

We'll create an abbreviated example of a stored procedure that returns the lookup information from these two tables, and then use the result to populate drop-downs on a UserForm:

CREATE PROCEDURE GetLookupValues
AS
   SELECT
      CustomerID,
      CompanyName
   FROM
     Customers
   SELECT
    ShipperID,
    CompanyName
   FROM
     Shippers

Note that the stored procedure above contains two separate SELECT
statements. These will populate two independent recordsets when the stored
procedure is executed using ADO. The procedure below is an example of
a UserForm_Initialize event that populates dropdowns on the UserForm
with the results of the GetLookupValues stored procedure:Option Explicit

Private Const ConnectionString As String = _
  “Provider=SQLOLEDB.1;Integrated Security=SSPI;” + _
  “Persist Security Info=False;Initial Catalog=NorthwindCS;” + _
  “Data Source=LAP800;Workstation ID=LAP800;”

Private Sub UserForm_Initialize()

  Dim Connection As ADODB.Connection
  Dim Recordset As ADODB.Recordset

  Set Connection = New ADODB.Connection
  Connection.ConnectionString = ConnectionString

  Connection.Open

  Set Recordset = New ADODB.Recordset
  Call Recordset.Open(“GetLookupValues”, Connection, _
    CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockReadOnly, _
    CommandTypeEnum.adCmdStoredProc)

  Do While Not Recordset.EOF
    Call ComboBoxCustomers.AddItem(Recordset.Fields(1).Value)
    Recordset.MoveNext
  Loop

  Set Recordset = Recordset.NextRecordset

  Do While Not Recordset.EOF
    Call ComboBoxShippers.AddItem(Recordset.Fields(1).Value)
    Recordset.MoveNext
  Loop

  ' Closes the recordset implicitly
  Set Recordset = Recordset.NextRecordset

  If (Connection.State = ObjectStateEnum.adStateOpen) Then Connection.Close

End Sub

Figure 11-7

One thing to note about the method just demonstrated is that it requires prior knowledge of the number and order of recordsets returned by the call to the stored procedure. We have also left out any handling of the primary key values associated with the lookup table descriptions. In a real-world application, you would need to maintain these keys (we prefer using a Collection object for this purpose) so that you could retrieve the primary key value that corresponded to the user's selection in each drop-down.

Disconnected Recordsets

In the Returning Data from Microsoft Access Using a Plain Text Query section above, we mentioned that getting in and out of the database as quickly as possible was an important goal. However, the Recordset object is a powerful tool that you would often like to hold onto and use without locking other users out of the database. The solution to this problem is ADO's disconnected recordset feature.

A disconnected recordset is a Recordset object whose connection to its data source has been severed, but which can still remain open. The result is a fully functional Recordset object that does not hold any locks in the database from which it was queried. Disconnected recordsets can remain open as long as you need them, they can be reconnected to and resynchronized with the data source, and they can even be persisted to disk for later retrieval. We will examine a few of these capabilities in the following example.

Let's say that you wanted to implement a feature that would allow users to view any group of customers they chose. Running a query against the database each time the user specified a different criterion would be an inefficient way to accomplish this. A much better alternative would be to query the complete set of customers from the database and hold them in a disconnected recordset. You could then use the Filter property of the Recordset object to quickly extract the set of customers that your user requested. The following example shows all of the elements required to create a disconnected recordset:

Option Explicit

Private Const ConnectionString As String = _
  “Provider=SQLOLEDB.1;Integrated Security=SSPI;” + _
  “Persist Security Info=False;Initial Catalog=NorthwindCS;” + _
  “Data Source=LAP800;Workstation ID=LAP800;”

Public Connection As ADODB.Connection

Public Recordset As ADODB.Recordset

Public Sub CreateDisconnectedRecordset()

   Dim SQL As String

   SQL = “SELECT CustomerID, CompanyName, ContactName, Country ” & _
         “FROM Customers”

  Set Connection = New ADODB.Connection
  Connection.ConnectionString = ConnectionString
  Connection.Open

  Set Recordset = New ADODB.Recordset
  Recordset.CursorLocation = CursorLocationEnum.adUseClient
  Recordset.CursorType = CursorTypeEnum.adOpenStatic

  Recordset.LockType = LockTypeEnum.adLockBatchOptimistic
  Call Recordset.Open(SQL, Connection, , , CommandTypeEnum.adCmdText)

  Set Recordset.ActiveConnection = Nothing

  Call Sheet4.Range(“A1”).CopyFromRecordset(Recordset)

  ' Connection intentionally left open
End Sub

Note that the Recordset object variable in the preceding example is declared with public scope. If we were to declare the Recordset object variable at the procedure level, VBA would automatically destroy it when the procedure ended and it would no longer be available for use.

There are six crucial steps required in order to successfully create a disconnected recordset. It's possible to combine several of them into one step during the Recordset.Open method, and it's more efficient to do so, but we've separated them for the sake of clarity:

• You must create a new, empty Recordset object to start with.
• You must set the cursor location to client-side. Since the recordset will be disconnected from the server, the cursor cannot be managed there. Note that this setting must be made before you open the recordset. It is not possible to change the cursor location once the recordset is open.
• The ADO client-side cursor engine supports only one type of cursor, the static cursor, so this is what the CursorType property must be set to.
• ADO has a lock type specifically designed for disconnected recordsets called Batch Optimistic. The Batch Optimistic lock type makes it possible to reconnect the disconnected recordset to the database and update the database with records that have been modified while the recordset was disconnected. This operation is beyond the scope of this chapter, so just note that the Batch Optimistic lock type is required in order to create a disconnected recordset.
• Opening the recordset is the next step. In this example, we've used a plain text SQL query. This is not a requirement. You can create a disconnected recordset from almost any source that can be used to create a standard recordset. There are a few capabilities that the client-side cursor engine lacks, however, multiple recordsets are one example.
• The final step is disconnecting the recordset from the data source. This is accomplished by setting the recordset's Connection object to Nothing. If you recall from the Recordset Object Properties section discussed earlier, the Connection object associated with a Recordset object is accessed through the Recordset.ActiveConnection property. Setting this property to Nothing severs the connection between the recordset and the data source.

Now that we have a disconnected recordset to work with, what kind of things can we do with it? Just about any operation that the Recordset object allows is the answer. Let's say the user wanted to see a list of customers located in Germany and sorted in alphabetical order. This is how you'd accomplish that task:

Public Sub FilterDisconnectedRecordset()
  Call Sheet4.Range(“A:D”).Clear
  Recordset.Filter = “Country = ‘Germany’”
  Recordset.Sort = “CompanyName”
  Call Sheet4.Range(“A1”).CopyFromRecordset(Recordset)
End Sub

If you are working in a busy, multiuser environment, the data in your disconnected recordset may become out-of-date during the course of your application due to other users inserting, updating, and deleting records. You can solve this problem by requerying the recordset. As demonstrated by the following example, this is a simple matter of reconnecting to the data source, executing the Recordset.Requery method, then disconnecting from the data source:

Public Sub RequeryConnection()

  Set Recordset.ActiveConnection = Connection
  Call Recordset.Requery(Options:=CommandTypeEnum.adCmdText)
  Set Recordset.ActiveConnection = Nothing

End Sub

Using ADO with Non-Standard Data Sources

This section will describe how you can use ADO to access data from two common non-standard data sources (data sources that are not strictly considered databases), Excel workbooks and text files. Although the idea may seem somewhat counterintuitive, ADO is often the best choice for retrieving data from workbooks and text files because it eliminates the often lengthy process of opening them in Excel. Using ADO also allows you to take advantage of the power of SQL to do exactly what you want in the process.

Querying Microsoft Excel Workbooks

When using ADO to access Excel workbooks, you use the same OLE DB provider that you used earlier in this chapter to access data from Microsoft Access. In addition to Access, this provider also supports most ISAM data sources (data sources that are laid out in a tabular, row, and column format). ADO will allow you to operate on workbooks that are either open or closed. However, by far the most common scenario will involve performing data access on a closed workbook.

We will be using the Sales.xls workbook, a picture of which is shown in Figure 11-8, for our Excel examples. You can download this workbook, along with the rest of the examples for this book, from the Wrox Web site.

Figure 11-8

When using ADO to work with Excel, the workbook file takes the place of the database while worksheets within the workbook, as well as named ranges, serve as tables. Let's compare a connection string used to connect to an Access database with a connection string used to connect to an Excel workbook.

Connection string to an Access database:

“Provider=Microsoft.Jet.OLEDB.4.0;” & _
            “Data Source=C:FilesNorthwind.mdb;”

Connection string to an Excel workbook:

“Provider=Microsoft.Jet.OLEDB.4.0;” & _
            “Data Source=C:FilesSales.xls;” & _
        “Extended Properties=Excel 8.0;”

Note that the same provider is used and that the full path and filename of the Excel workbook takes the place of the full path and filename of the Access database. (The file paths here are artificially contrived. Replace the example file locations with the physical file path where the files are actually located on your computer.) The only difference is that when using the OLE DB provider for Microsoft Jet to connect to data sources other than Access, you must specify the name of the data source you wish to connect to in the Extended Properties argument. When connecting to Excel, you set the Extended Properties argument to Excel 8.0 for Excel 97 and higher.

You query data from an Excel worksheet using a plain text SQL statement exactly like you would query a database table. However, the format of the table name is different for Excel queries. You can specify the table that you wish to query from an Excel workbook in one of four different ways:

• Worksheet Name Alone. When using the name of a specific worksheet as the table name in your SQL statement, the worksheet name must be suffixed with a $ character and surrounded with square brackets. For example, [Sheet1$] is a valid worksheet table name. If the worksheet name contains spaces or non-alphanumeric characters, you must surround it in single quotes. An example of this is:

  ['My Sheet$'].

• Worksheet-level Range Name. You can use a worksheet-level range name as a table name in your SQL statement. Simply prefix the range name with the worksheet name it belongs to, using the formatting conventions described earlier. An example of this would be:

  [Sheet1$SheetLevelName].

• Specific Range Address. You can specify the table in your SQL statement as a specific range address on the target worksheet. The syntax for this method is identical to that for a worksheet-level range name:

  [Sheet1$A1:E20].

• Workbook-level Range Name. You can also use a workbook-level range name as the table in your SQL statement. In this case, there is no special formatting required. You simply use the name directly without brackets.

Although, our sample workbook contains only one worksheet, the target workbook can contain as many worksheets and named ranges as you wish. You simply need to know which one to use in your query. The following procedure demonstrates all four table-specifying methods discussed just now:

Option Explicit

Public Sub QueryWorksheet()

  Dim Recordset As ADODB.Recordset
  Dim ConnectionString As String

  ConnectionString = _
    “Provider=Microsoft.Jet.OLEDB.4.0;” & _
    “Data Source=” & ThisWorkbook.Path & “Sales.xls;” & _
    “Extended Properties=Excel 8.0;”

  Dim SQL As String

   ' Query based on the worksheet name.
   SQL = “SELECT * FROM [Sales$]”

 ' Query based on a sheet level range name.
 ' SQL = “SELECT * FROM [Sales$MyRange]”
 ' Query based on a specific range address.
 ' SQL = “SELECT * FROM [Sales$A1:E14]”
 ' Query based on a book level range name.
 ' SQL = “SELECT * FROM BookLevelName”

 Set Recordset = New ADODB.Recordset

 On Error GoTo Cleanup

Call Recordset.Open(SQL, ConnectionString, _
  CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockReadOnly, _
  CommandTypeEnum.adCmdText)

 Call Sheet1.Range(“A1”).CopyFromRecordset(Recordset)

Cleanup:
  Debug.Print Err.Description

  If (Recordset.State = ObjectStateEnum.adStateOpen) Then
    Recordset.Close
  End If

  Set Recordset = Nothing

End Sub

By default, the OLE DB provider for Microsoft Jet assumes that the first row in the table you specify with your SQL statement contains the field names for the data. If this is the case, you can perform more complex SQL queries, making use of the WHERE and ORDER BY clauses. If the first row of your data table does not contain field names, however, you must inform the provider of this fact or you will lose the first row of data. The way to accomplish this is by providing an additional setting, HDR=No, to the Extended Properties argument of the connection string:

“Provider=Microsoft.Jet.OLEDB.4.0;” & _
“Data Source=” & ThisWorkbook.Path & “Sales.xls;” & _
“Extended Properties=”“Excel 8.0;HDR=No”“;”

Note that when you pass multiple settings to the Extended Properties argument, the entire setting string must be surrounded with double quotes and the individual settings delimited with semicolons. If your data table does not include column headers, you will be limited to plain SELECT queries.

Inserting and Updating Records in Microsoft Excel Workbooks

ADO can do more than just query data from an Excel workbook. You can also insert and update records in the workbook, just as you would with any other data source. Deleting records, however, is not supported. Updating records, although possible, is somewhat problematic when an Excel workbook is the data source, as Excel-based data tables rarely have anything that can be used as a primary key to uniquely identify a specific record. Therefore, you must specify the values of enough fields to uniquely identify the record concerned in the WHERE clause of your SQL statement when performing an update. If more than one record meets WHERE clause criteria, all such records will be updated.

Inserting is significantly less troublesome. All you do is construct a SQL statement that specifies values for each of the fields and then execute it. Note once again that your data table must have column headers in order for it to be possible to execute action queries against it. The following example demonstrates how to insert a new record into our sales worksheet data table:

Public Sub WorksheetInsert()

  Dim Connection As ADODB.Connection
  Dim ConnectionString As String

  ConnectionString = _
    “Provider=Microsoft.Jet.OLEDB.4.0;” & _
    “Data Source=” & ThisWorkbook.Path & “Sales.xls;” & _
    “Extended Properties=Excel 8.0;”

  Dim SQL As String

  SQL = “INSERT INTO [Sales$] ” & _
      “VALUES(‘VA’, ‘On Line’, ‘Computers’, ‘Mid’, 30)”

  Set Connection = New ADODB.Connection
  Call Connection.Open(ConnectionString)

  Call Connection.Execute(SQL, , _
    CommandTypeEnum.adCmdText Or ExecuteOptionEnum.adExecuteNoRecords)

  Connection.Close
  Set Connection = Nothing
End Sub

Querying Text Files

The last data access technique we'll discuss in this chapter is querying text files using ADO. The need to query text files doesn't come up as often as some of the other situations that we've discussed. However, when faced with an extremely large text file, the result of a mainframe database data dump, for example, ADO can be a lifesaver.

Not only will it allow you to rapidly load large amounts of data into Excel, but using the power of SQL to limit the size of the resultset can also enable you to work with data from a text file that is simply too large to be opened directly in Excel. For our discussion on text file data access, we'll be using a comma-delimited text file, Sales.csv, whose contents are identical to the Sales.xls workbook we used in the preceding Excel examples.

The OLE DB provider for Microsoft Jet is once again used for connecting to text files with ADO. However, the connection string details are slightly different. The following example demonstrates how to construct a connection string to access a text file:

“Provider=Microsoft.Jet.OLEDB.4.0;” & _
            “Data Source=C:Files;” & _
        “Extended Properties=Text;”

Note that in the case of text files, the Data Source argument to the provider is set to the directory that contains the text file. Do not include the name of the file in this argument. Once again, the provider is informed of the format to be queried by using the Extended Properties argument. In this case, you simply set this argument to the value “Text”.

Querying a text file is virtually identical to querying an Excel workbook. The main difference is how the table name is specified in the SQL statement. When querying a text file, the filename itself is used as the table name in the query. This has the added benefit of allowing you to work with multiple text files in a single directory without having to modify your connection string.

As with Excel, you are limited to plain SELECT queries if the first row of your text file does not contain field names. You must also add the HDR=No setting to the Extended Properties argument, if this is the case, in order to avoid losing the first row of data. Our example text file has field names in the first row, and we'll assume that we need to limit the number of records we bring into Excel by adding a restriction in the form of a WHERE clause to our query. The following procedure demonstrates this:

Public Sub QueryTextFile()

    Dim Recordset As ADODB.Recordset

    Dim ConnectionString As String
    ConnectionString = _
      “Provider=Microsoft.Jet.OLEDB.4.0;” & _
      “Data Source=” & ThisWorkbook.Path & “;” & _
      “Extended Properties=Text;”

    Const SQL As String = _
      “SELECT * FROM Sales.csv WHERE Type='Art';”

    Set Recordset = New ADODB.Recordset
    Call Recordset.Open(SQL, ConnectionString, _
      CursorTypeEnum.adOpenForwardOnly, _
      LockTypeEnum.adLockReadOnly, CommandTypeEnum.adCmdText)

   Call Sheet1.Range(“A1”).CopyFromRecordset(Recordset)

   Recordset.Close
   Set Recordset = Nothing

End Sub

Summary

This concludes our discussion of data access with ADO. Due to space constraints we were only able to scratch the surface of possibilities in each section. If data access is, or might become, a significant part of your development effort, you are strongly recommended to obtain the additional resources mentioned throughout this chapter.