Automating Microsoft® Access with VBA by tlp18619


									  Automating Microsoft® Access with VBA
  By Susan Sales Harkins, Mike Gunderloy

Using the ADO Connection Object
You need a connection to a data source and an object in which to store the data you
retrieve from the data source. Technically, an ADO Connection object is a single
connection to an OLE DB data source. What that means is that you can use the
Connection object to connect to a data source.

You can connect implicitly or explicitly and neither method is more correct than the
other. However, if you're going to use the same connection more than once, use an
explicit Connection object. An implicit connection is created whenever you retrieve data
without first declaring a Connection object. You might find it less confusing to use
explicit Connection objects until you feel comfortable with the ADO object model.

Opening the Connection

An explicit connection actually declares and instantiates a Connection object as follows:

Dim cnn as ADODB.Connection
Set cnn = New ADODB.Connection

We can't really show you an implicit connection because there's no declaration.

The Connection object comes with a number of properties that control the object's

      ConnectionString— Specifies the data source.
      ConnectionTimeout— Determines how many seconds          to wait for a data source
       to respond. The default is 15, which might not be long enough if you're on a busy
       network or pulling data across the Internet.
      Mode— Sets the permission mode. See Table 16.1 for the intrinsic constants for
       this property.

                              Table 16.1. Mode Property Constants
    Constant                   Explanation
    adModeRead                 Read-only connection
    adModeReadWrite            Read-write connection
    adModeWrite                Write-only connection
    adModeShareDenyRead        Other applications can't open a read connection
    adModeShareDenyWrite       Other applications can't open a write connection
    adModeShareDenyNone        All applications can open a connection with any
    adModeShareExclusive       Other applications can't open a connection
    adModeRecursive            Subrecords inherit permissions of current record

   CursorLocation—       Determines the location of the OLE DB provided cursor.
    There are two constants: adUseServer sets a server-side cursor and adUseClient
    sets a client-side cursor. You can think of a cursor as a set of rows from a table
    plus an indicator of the current row.
   DefaultDatabase— Specifies a specific database on a server to use for all data
    processes with this particular Connection object.
   IsolationLevel— Controls how database operations on different connections
    affect one another. See Table 16.2 for a list of intrinsic constants. You don't need
    to worry about this unless you're writing code that works with multiple users
    logged into the database at the same time.

                  Table 16.2. IsolationLevel Property Constants
    Constant                    Explanation
    adXactUnspecified           Returned when provider can't determine the isolation
    adXactChaos                 Default setting that protects pending changes from
                                being overwritten
    adXactReadUncommitted       Enables you to view but not change uncommitted
                                changes from other transactions
    adXactReadCommitted         Enables you to view changes from other transactions
                                only after they're committed
    adXactRepeatableRead        Enables requerying a Recordset to include changes
                                pending from other transactions
                     Table 16.2. IsolationLevel Property Constants
       Constant                    Explanation
       adXactSerializable          Isolates all transactions from all other transactions

      Provider— Specifies an OLE DB provider before opening the connection.
      CommandTimeout— Specifies how long to wait before terminating an attempt            to
       connect while executing a command.

Just remember to set the properties before actually opening the connection. For instance,
to set a server side cursor, you use the following code:

Dim cnn as ADODB.Connection
Set cnn = New ADODB.Connection
cnn.CursorLocation = adUseServer

To open the connection, use the Connection object's Open method in the form

Connection.Open [connectionstring][, userID][, password][, options]

All the arguments are optional. The options argument is one of two intrinsic constants:
adConnectUnspecified opens a synchronous connection (one on which only a single
operation can proceed at one time) and is the default; adAsyncConnect opens an
asynchronous connection. Using the default synchronous connection is typically fine for
any operation that doesn't involve an extremely large amount of data.

About Connection Strings

There are two opportunities to specify the connection string:

      Use the Open method's connectionstring argument after creating the
       Connection object.
      Use the Connection object's connectionstring argument after creating the
       Connection object, but before opening the actual connection.

Either way, certain information can be passed in the form of five arguments that are
concatenated together in the connection string:

      Provider—   Specifies the name of the OLE DB provider; check provider
       documentation for the exact string. See Table 16.3 for a list of common provider
         Table 16.3. Common Provider Strings
       Application         String
       Microsoft Jet 3.51 Microsoft.Jet.OLEDB.3.5.1
       Microsoft Jet 4.0   Microsoft.Jet.OLEDB.4.0
       ODBC Drivers        MSDASQL
       Oracle              MSDAORA
       SQL Server          SQLOLEDB

      Data Source— Identifies the file to which you're connecting.
      Remote Provider— Specifies the server provider when opening       a client-side
      Remote Server— Identifies the server.
      URL— Identifies the connection string as   a URL.


If you need help constructing a connection string, a visit to can likely solve all your problems.

The following connection string connects to the Northwind sample database that comes
with Access on your local system from any database, assuming that it's installed in the
default location:

Private Sub MakeConnection()
  Dim cnn As ADODB.Connection
  Dim strConn As String
  Set cnn = New ADODB.Connection
  strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
   "Data Source=C:\Program Files\Microsoft Office\" & _
  cnn.Open strConn
  MsgBox "Connection Made"
  Set cnn = Nothing
End Sub

Just open a standard module and enter the procedure. Then, press F5. If the connection is
made, Access displays the message shown in Figure 16.2. Click OK to clear the message
box. This example establishes the data source as it opens the actual connection. (You can
use any database as the Data Source argument, just be sure to type the complete and
correct path.)
      Figure 16.2. This message lets you know the connection was made.

Closing a Connection

The previous example doesn't do anything but connect to a data source. No data is
retrieved. But you might have noticed the Close method at the end of the procedure. It's
best to always disconnect the Connection object when you're done by executing the
Close method. You don't have to destroy the object, because you might want to use it

The Close method takes the form


where connection represents a Connection object.

To reuse a closed Connection object, just execute the Open method. When you're
actually done with the Connection object, set it to Nothing after closing the object as

Set connection = Nothing


More often than not, you can use a simple connection shortcut. If the data you need is in
the current database, use the CurrentProject object to define the connection as follows:

Dim cnn As ADODB.Connection
Dim cnn As New ADODB.Connection
Set cnn = CurrentProject.Connection

Using this method, you'll share exactly the connection to the data that Access itself is
Working with Command Objects
You can use the Connection or the Command object to retrieve and manipulate data.
However, the Command object has a few advantages:

      Using a Command object, you can avoid a Recordset object and update data
       directly, which can be faster with some databases because the changes are made
       on the server instead of dragging the records across the network.
      The Command object supports parameters; the Connection object doesn't.
      The Command object has more properties than the Connection object, which
       enables you to fine-tune its behavior.

By way of a definition, you can compare the Command object to a stored procedure or
other data access object that returns data because the Command object executes the process
that actually returns the data. The object does so by executing code against the OLE DB
data source or by retrieving data from the OLE DB data source. This makes the Command
object a flexible tool to have around.

Creating a Command Object

Creating a Command object is similar to creating a Connection object: declare it and then
define it. But you don't open it, which makes sense given the difference between the two
objects. Use the following structure to declare and define a Command object:

Dim strConn As String
Dim cmd As ADODB.Command
strConn = connectionstring
Set cmd = New ADODB.Command
cmd.ActiveConnection = strConn

The Command's connectionstring argument is identical to that used by the Connection

Executing the Command Object

The Command object executes code against the data source that manipulates that data in
some way—either changing it at the source or retrieving it. Usually, you'll use a
Recordset object to retrieve data, you'll read about that object a bit later.

To execute code that updates or retrieves data, use the Command object's CommandText
property, which contains the actual instructions executed against the data (technically, the
provider). This property is a string expression or value that contains a provider command,
which can be a SQL statement, the name of a table, a URL, or a call to a stored
The following procedure automates a simple update query that increases each of the
hourly rate values (in the Timetrack.mdb Tasks table) by three percent using a SQL
UPDATE command directly against the data:

Private Sub EditCA()
  'Update hourly rate values by 3 percent.
  Dim strConn As String
  Dim cmd As ADODB.Command
  Set cmd = New ADODB.Command
  With cmd
   .ActiveConnection = CurrentProject.Connection
   .CommandText = "UPDATE Tasks " & _
    "SET HourlyRate = HourlyRate + (HourlyRate * .03)"
  End With
  Set cmd = Nothing
End Sub

Enter the procedure into a standard procedure (or use Chapter 16's example module).
With the cursor inside the procedure, press F5. Nothing seems to happen, but open the
Tasks table and you can see that each value has been updated, as shown in Figure 16.3.
The first value, $154.50, was $150.00. All the values have been increased.

         Figure 16.3. A Command object updated the hourly rate values.

To top