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';"  

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  
.Open   
  
' 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.  
oConn.Close  
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