7.4. Data Access with ADO

Accessing data is the reason why we use databases, and a large proportion of your programming will usually revolve around manipulating those objects that deal with data: views, stored procedures, and recordsets

7.4.1. The ADO Object Model

Figure 7.3. Figure 7-3

ADO actually supports two additional objects not shown in Figure 7-3: the Record and Stream objects.

The Record object can be seen as a single-row recordset. Although records have a limited functionality when compared to recordsets, they relieve you of the overhead of having to instantiate the more complex recordset. In addition, they have different properties and methods that can be quite useful.

The Record object can also manipulate data such as folders and files in a file system, e-mail messages, and so on. That means the source of data for the Record object can be the current row of a recordset, a Uniform Resource Locator (URL).

The Stream object reads, writes, and manages a stream of bytes. This byte stream can be text or binary, and is limited in size only by the available system resources.

You would typically use an ADO stream object to contain the text or bytes of a file or message supplied using a provider such as the Microsoft OLE DB Provider for Internet Publishing.

The data source for a stream object can be a file whose location is specified by a URL, a field in a record or recordset that contains a stream object, a resource field containing the URL of a file, a BLOB field in a recordset, or a custom-designed stream that exists in memory.

Unfortunately, an in-depth study of these objects is beyond the scope of this book. So if you wish to examine these objects in greater detail, consult your provider's documentation and the Access help.

7.4.2. Executing Action Queries

An action query is a query that carries out some action on a set of data, without returning any rows. There are two ways to programmatically execute an action query, both of which involve using the Execute method.

7.4.2.1. The Connection.Execute Method

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

You can use the Execute method of the Connection object. Just instantiate a connection and issue its Execute method—it's that simple!

Dim cn As New ADODB.Connection
Dim lngRA As Long, lngOptions As Long

lngOptions = adCmdUnspecified And adAsyncExecute

cn.Open CurrentProject.Connection
cn.Execute strSQL, lngRA, lngOptions

The CommandText argument can be an SQL statement: the name of a table, the name of a stored procedure, or a provider-specific text or command. The RecordsAffected argument, if supplied, is populated with the number of records affected by the operation, when the operation completes.

The Options argument can be a combination of the CommandTypeEnum values or ExecuteOptionEnum values that affect the way the Execute method works. Appendix J contains a list of all the available CommandTypeEnum and ExecuteOptionEnum values.

7.4.2.2. The Command.Execute Method

You can also execute an action query by calling the Execute method against a Command object.

cmd.Execute RecordsAffected, Parameters, Options

Simply set the command object's CommandText, CommandType, and ActiveConnection properties; then call the Execute method. The Parameters property can be populated by a variant array of parameter values passed with an SQL statement. The Options property defines the provider how to evaluate the CommandText property (refer to previous table).

Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim strSQL As String
Dim RA As Long

'Open the connection
cn.Open CurrentProject.Connection

'Setup the SQL statement
strSQL = "UPDATE Suppliers SET Region = 'None'"

With cmd
    .CommandText = strSQL
    .CommandType = adCmdUnknown
    .ActiveConnection = cn
    .Execute RA
End With

Debug.Print RA & " records were updated."

cn.Close
Set cmd = Nothing
Set cn = Nothing

7.4.2.3. Specifying Command Parameters

Instead of specifying the Command object's parameters in the SQL statement, you can also set them using the Command object's Parameter object.

For example, the following function updates the prices of all products of a given category by calling an SQL Server stored procedure, which returns a count of the products that were updated.

Public Function UpdatePrices( _
        strCategory As String, _
        curNewPrice As Currency) As Integer
Dim cmd As New ADODB.Command
Dim RA As Long

'Build the Command object
With cmd

'Set the connection
.ActiveConnection = CurrentProject.Connection

'Set other properties
.CommandText = "qryUpdatePrices"
.CommandType = adCmdTable

To be able to refer to the parameters by name (for providers that support it), you must first refresh the Parameters collection; otherwise you'll need to refer to them by ordinal position.

.Parameters.Refresh
    .Parameters("Category") = strCategory
    .Parameters("Price") = curNewPrice

    'If we were specifying parameters for a
    'stored procedure, we would use the "@"
    'predicate.
    '.Parameters("@Category") = strCategory
    '.Parameters("@Price") = curNewPrice

    'Execute the query
    .Execute RA
End With

    Debug.Print RA & " products were updated."
    UpdatePrices = RA

    Set cmd.ActiveConnection = Nothing
    Set cmd = Nothing
End Function

7.4.2.4. Creating Your Own Parameters

If you're attempting to execute an Access stored query (one that has been saved to disk), you can provide values for its parameters in two ways.

First you can rewrite the query, like

Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset

Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset

'Setup the Command object
With cmd
    .CommandText = "SELECT * FROM qrySuppliersByCity WHERE City = 'London'"
    .CommandType = adCmdUnknown

    'Associate the Command object with a connection
    .ActiveConnection = CurrentProject.Connection

'Request the recordset
    Set rs = .Execute
End With

But that involves wrapping the stored query in a hard-coded SQL statement every time you want to run it, and using a WHERE clause. But that's not the same as supplying a parameter value. Additionally, if the stored query is a parameter query, the above method does not provide you a way to supply values to the parameters.

At this point, you might be thinking that you can simply use the Parameter object's Refresh method to supply a parameter, as was demonstrated above. You would be correct, but not if you intend to do it against a parameter query in Access database. In an Access database, you can't refer to a parameter in a query's Parameters collection by name.

The second method is to create parameters in the Command object, which match those specified in the parameter query. The following code segment demonstrates how to do this.

Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset

Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset

'Setup the Command object
With cmd
    .CommandText = "qrySuppliersByCity"
    .CommandType = adCmdUnknown

    'Create the parameter
    .Parameters.Append .CreateParameter( _
            "City", adVarChar, adParamInput, 50)

    'Set the parameter's value
    .Parameters("City") = "London"

    'Associate the Command object with a connection
    .ActiveConnection = CurrentProject.Connection

    'Request the recordset
    Set rs = .Execute
End With

Just to round out the ways in which you can create parameters, an alternative method is to create the parameter as an object in its own right, and append that object to the Parameters collection, like

Dim prm As ADODB.Parameter
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset

Set prm = New ADODB.Parameter
Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset

'Setup the Command object
With cmd
    .CommandText = "qrySuppliersByCity"
    .CommandType = adCmdUnknown

    'Create the parameter
    Set prm = .CreateParameter ("City", adVarChar, adParamInput, 50)
    .Parameters.Append prm
    'Set the parameter's value
    .Parameters("City") = "London"

    'Associate the Command object with a connection
    .ActiveConnection = CurrentProject.Connection

    'Request the recordset
    Set rs = .Execute
End With