Using the ADO Connection Object

 < Day Day Up > 

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 behavior:

  • 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




    Read-only connection


    Read-write connection


    Write-only connection


    Other applications can't open a read connection


    Other applications can't open a write connection


    All applications can open a connection with any permission


    Other applications can't open a connection


    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




    Returned when provider can't determine the isolation level



    Default setting that protects pending changes from being overwritten



    Enables you to view but not change uncommitted changes from other transactions


    Enables you to view changes from other transactions only after they're committed



    Enables requerying a Recordset to include changes pending from other transactions


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

    Table 16.3. Common Provider Strings



    Microsoft Jet 3.51


    Microsoft Jet 4.0


    ODBC Drivers




    SQL Server


  • Data Source Identifies the file to which you're connecting.

  • Remote Provider Specifies the server provider when opening a client-side connection.

  • 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\" & _    "Office\Samples\Northwind.mdb;"   cnn.Open strConn   MsgBox "Connection Made"   cnn.Close   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 again.

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 follows:


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

     < Day Day Up > 

    Automating Microsoft Access with VBA
    Automating Microsoft Access with VBA
    ISBN: 0789732440
    EAN: 2147483647
    Year: 2003
    Pages: 186 © 2008-2017.
    If you may any questions please contact us: