DataWright Information Services

Consulting and Resources for Excel and Access

ADO Connection Strings

In order to connect to a data source you must have the correct
connection string. One of the best collections on the Web can be
found at
. However, you also need to define
the recordset correctly. It’s useful to point out a few differences that
caught me when I first started using ADO. The best way to do that is
to place the various recordset statements from this tutorial side by
side, and then provide some comments.

Reading a recordset from a table

This is the recordset used in
Part 4 of the tutorial

   rst.Open Source:="tblPopulation", _
            ActiveConnection:=cnn, _
            CursorType:=adOpenDynamic, _ 
            LockType:=adLockOptimistic, _

Reading a recordset from a query

In Part 6 of the
tutorial we used a drop-down box to select a parameter for a query.
The string below is from that sample.

   rst.Open Source:=sSQL, _
            ActiveConnection:=cnn, _
            CursorType:=adOpenForwardOnly, _
            LockType:=adLockOptimistic, _

Three of the properties have changed. Two of them are crucial to
the success of the code, and are detailed below.

Property Table SQL statement Comment
Source table name SQL variable The table names must be in quotes. The SQL string is defined earlier in the code
Options adCmdTable adCmdText If you try to use adCmdTable with a SQL
statement the code will fail. Likewise, if the source is a
table, adCmdText will fail. When adCmdText
is used, ADO expects to see a SQL statement.

The third change is the CursorType. You can use adDynamic and
adForwardOnly interchangeably for retrieving data; on larger data sets you may see a
performance improvement with adForwardOnly.

Writing one or more complete records to a table

If you are writing complete records to a table, you can define
the recordset the same way as if you are transferring a recordset
from a table. The main difference is in the treatment of the
individual records; to write them to Access you can loop through the
records and fields, but to download them the CopyFromRecordset
action is faster. This comes from
Part 2 of the

    rst.Open Source:="tblPopulation", _
             ActiveConnection:=cnn, _
             CursorType:=adOpenDynamic, _
             LockType:=adLockOptimistic, _

Updating one or more existing records

When you update existing records, you need a unique key that lets
you isolate one record at a time and then make changes. In
Part 3 of the tutorial
we saw how to loop through multiple records and populate a new
field. In Part 6 we
saw how to make changes to a single record. Those recordset
definitions are both identical. The definition is shown below:

     rst.Open Source:=sSQL, _
              ActiveConnection:=cnn, _
              CursorType:=adOpenKeyset, _

The difference here is the CursorType. When writing records you
need to use adOpenKeyset; the code will fail if you do not.