Using a Connection Object

Before opening a Connection object, you must define certain information about the data source and type of connection. Most of this information is held by the ConnectionString parameter of the Open method on the Connection object, or by the ConnectionString property on the Connection object. A connection string consists of a list of argument/value pairs separated by semi-colons, with the values enclosed within single quotes. For example:

Dim sConn As String  
sConn = "Provider='SQLOLEDB';Data Source='MySqlServer';" & _  
             "Initial Catalog='Northwind';Integrated Security='SSPI';"  


You can also specify an ODBC Data Source Name (DSN) or a Data Link (UDL) file in a connection string. For more information about DSNs, see Managing Data Sources in the ODBC Programmer's Reference. For more information about UDLs, see Data Link API Overview in the OLE DB Programmer's Reference.

Typically, you establish a connection by calling the Connection.Open method with an appropriate a connection string as its parameter. An example is shown in the following Visual Basic code snippet:

Dim oConn As ADODB.Connection  
Dim oRs As ADODB.Recordset  
Dim sConn As String  
Dim sSQL as String  

' Open a connection.  
Set oConn = New ADODB.Connection  

' Make a query over the connection.  
sSQL = "SELECT ProductID, ProductName, CategoryID, UnitPrice " & _  
             "FROM Products"  
Set oRs = New ADODB.Recordset  
oRs.Open sSQL, , adOpenStatic, adLockBatchOptimistic, adCmdText  

MsgBox oRs.RecordCount  

' Close the connection.  
Set oConn = Nothing  

Here oRs.Open takes a Connection object (oConn) variable as the value of its ActiveConnection parameter. Also, the Connection.CursorLocation property assumes the default value of adUseServer. Contrast this to the HelloData example in the preceding section. The following instruction would result in run-time errors.

oRs.MarshalOptions = adMarshalModifiedOnly  
' Disconnect the Recordset.  
Set oRs.ActiveConnection = Nothing