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

  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 permission
  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 level
  adXactChaos adXactBrowse	Default setting that protects pending changes from being overwritten
  adXactReadUncommitted adXactCursorStability	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 adXacIsolated	Enables requerying a Recordset to include changes pending from other transactions
  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 strings.

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

• Remote Server Identifies the server.

• URL Identifies the connection string as a URL.

TIP

If you need help constructing a connection string, a visit to http://www.connectionstrings.com/ 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

 connection.Close 

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 

TIP

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.