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

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