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
ConnectionStrings.com. 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, _
            Options:=adCmdTable

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, _
            Options:=adCmdText

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

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
tutorial.

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

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, _
              LockType:=adLockOptimistic

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

 
Home | About |
Links |

Contact | Excel |
Access