When faced with the challenge of providing a programmatic solution for updating data for clients, the biggest decision you need to make is whether to use the old Connection standard the ADO Recordset object, or the Command object. Most of the time, you can use either the Command or the Connection object to retrieve and manipulate data. Access developers tend to use the Connection object with .mdb files, even though Access supports both objects. However, as I’ll discuss, the Command object has a few advantages:

  • ·        Command allows you to update data directly, without using a Recordset. Usually, this route is faster because you’re not dragging records across a network; the changes are being made to the data on the server via a SQL statement or stored procedure.
  • ·        Unlike the Connection object, the Command object accepts parameters.
  • ·        The Command object has properties that the Connection object doesn’t—such as Prepare.

About the Command object
OLE DB lets you retrieve data. It’s actually an interface that also manages to translate data processing components from one format to another. All this communication magic is accomplished via OLE DB by three types of access components:

  • ·        Data providers are the programs that talk to one another. In technical terms, data providers expose data.
  • ·        Data consumers actually use the data exposed by a data provider.
  • ·        Service components do the processing and data retrieval.

ADO is a data consumer that manipulates OLE DB via nine objects, one of which is the Command object. The Command object represents a stored procedure or other data access object that returns data. What that means is that the Command object executes the instructions that produce the data. They perform this feat in one of two ways:

  • ·        A Command object executes the code against an OLE DB data source.
  • ·        A Command object retrieves a recordset from an OLE DB data source.

In other words, you can act on the data in the data source or you can retrieve the data from the data source.

Using a connection string
Before you act on or retrieve from a data source, you have to connect to the data source in one of two ways: a connection string or a Connection object. Let’s take a look at the connection string method first:
 
Dim strConn As String
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
strConn = connectionstring
Set cmd = New ADODB.Command
cmd.ActiveConnection = strConn
cmd.CommandText = code
Set rst = cmd.Execute

The connectionstring argument has several arguments and takes this form:
 
Provider=value;File Name=value;Remote Provider = value; Remote Server=value; URL=value;

Table A defines the arguments, which are all optional.
Table A


Arguments
Description
Provider
 Specifies the name of the OLEDB provider. Check your particular provider documentation for a specific string.
Data Source
 Identifies the file that contains connection information.
Remote Provider
 Specifies the provider on the server when opening a client-side connection (for Remote Data Services).
Remote Server
 Specifies the server to retrieve data from (for Remote Data Services).
URL
 Identifies the connection string as a URL.

Connection strings arguments

The Recordset object has been the object of choice when editing data, but the Command object can often be faster because the processing’s done on the server (as long as the process returns no rows) instead of dragging data across the connection to the client. It’s also flexible enough to allow a stored procedure or a SQL statement. For example, Listing A modifies the Access Northwind sample database using the Command object and an UPDATE SQL statement.

The first thing I should point out is that the provider string’s File Name argument in this example identifies a file, not a server. That’s because Access is a file-based database and not technically a server-based database. If necessary, replace the strConn string with the following to connect to the SQL Server version of Northwind:
 
strConn = “Provider=SQLOLEDB;” & _
      “Server=(local);” & _
      “Initial Catalog=Northwind;” & _
      “Integrated Security=SSPI;”

The second point worth noting is the SSPI constant in the SQL Server connection string. That constant allows the code to use your Windows login, bypassing the need to enter a user ID and a password. In addition, a Command object can handle just about any valid select or action query.

Using a stored procedure
The Command object also supports stored procedures. You can create and then execute the stored procedure in the same function, as shown in Listing B. The first Command object executes a SQL statement that creates a stored procedure on the SQL Server. The second Command object executes the new stored procedure to then edit data on the SQL Server. In addition, the procedure accepts parameters by simply passing two strings, the string you want to replace and the string you want to use instead of the original string.

Open the Immediate window and enter this statement:
 
?EditSQLServerSP(“Sales Representative”, “Account Executive”)

Doing so will actually create and store the spUpdateTitle stored procedure on the SQL server. In addition, the function then passes to spUpdateTitle the two strings shown above. As a result, the function changes each occurrence of the string Sales Representative to Account Executive.

Explicitly declaring the connection object
So far, the connections have all used a connection string to specify a connection to a data source. However, you might prefer using an explicit Connection object. To do so, you’d use this syntax:
 
Dim cnn As ADODB.Connection
Dim cnn AS ADODB.Command
Set cnn = New ADODB.Connection
cnn.Open = connectionstring
Set cmd = New ADODB.Command
With cmd
  .CommandType = constant
  Set .ActiveConnection = cnn
  .CommandText = code
  .Execute
End With

Listing C uses the Connection object to run spUpdateTitle again. When you run it this time, swap the two strings, so you can return the Title strings to their original state. To do so, in the Immediate window, run the following statement:
 
?RunspUpdateTitle(“Account Executive”, “Sales Representative”)

Since the stored procedure already exists, you just need to call it and pass the necessary strings to reverse the changes you made in the previous example.

In such a simple example, an explicit Connection object is overkill. All you’re really doing is declaring the Connection object that ADO creates implicitly anyway. Declaring the Connection object is beneficial if the code executes multiple commands because you can reuse a single connection. Declaring the Connection object is probably a good habit to get into unless you’re working with a one-process task.

Command the Command object
Knowing your options is what good development is all about. A simple decision between looping through a Recordset object and executing a SQL statement via a Command object can increase performance most of the time. Don’t fall back on the Recordset object just because that’s what you know best.