Using a Recordset Object
Alternatively, you can use Recordset.Open to implicitly establish a connection and issue a command over that connection in a single operation. For example, in Visual Basic:
Dim oRs As ADODB.Recordset Dim sConn As String Dim sSQL as String sConn = "Provider='SQLOLEDB';Data Source='MySqlServer';" & _ "Initial Catalog='Northwind';Integrated Security='SSPI';" sSQL = "SELECT ProductID, ProductName, CategoryID, UnitPrice " & _ "FROM Products" ' Create and Open the Recordset object. Set oRs = New ADODB.Recordset oRs.CursorLocation = adUseClient oRs.Open sSQL, sConn, adOpenStatic, _ adLockBatchOptimistic, adCmdText MsgBox oRs.RecordCount oRs.MarshalOptions = adMarshalModifiedOnly ' Disconnect the Recordset. Set oRs.ActiveConnection = Nothing oRs.Close Set oRs = Nothing
Notice that oRs.Open takes a connection string (sConn), in place of a Connection object (oConn), as the value of its ActiveConnection parameter. Also the client-side cursor type is enforced by setting the CursorLocation property on the Recordset object. Again, contrast this with the HelloData example.