Step 1: Open a Connection (ADO Tutorial)
You are Here...
- Make a connection to a data source.
- Optionally, create an object to represent an SQL query command.
- Optionally, specify values in the SQL command as variable parameters.
- Execute the command. If the command is row-returning, store the rows in a storage object.
- Optionally, navigate, examine, manipulate, and edit the data.
- If appropriate, update the data source with changes from the storage object. Optionally, embed the update in a transaction.
- If you used a transaction, accept or reject the changes made during the transaction. End the transaction.
Discussion
You require a means to establish the conditions necessary to exchange data; that is, a connection. The data source you connect to is specified in a connection string, although the parameters specified in a connection string may differ for each provider and data source.
The main way ADO opens a connection is with the Connection.Open method. Alternatively, you can invoke the shortcut method, Recordset.Open, to both open a connection and issue a command over that connection in one operation. The following is the syntax for each method in Visual Basic:
connection.Open ConnectionString, UserID, Password, OpenOptions
recordset.Open Source, ActiveConnection, CursorType, LockType, Options
Comparing these two methods will highlight some useful characteristics of ADO method parameters in general.
A method parameter can be specified in several ways. For example, Recordset.Open takes an ActiveConnection parameter, which could be a literal string, a variable representing that string, or a Connection object representing an open connection.
This tutorial uses the literal connection string, "DSN=Pubs;uid=sa;pwd=;". (The data source is indirectly specified by the "DSN=" keyword. See the "Typical Connection String" section in Microsoft OLE DB Provider for ODBC for more information.)
Many objects have properties that can provide an argument if a method parameter is omitted. For example, supply the connection string information for the Connection.Open method by setting the Connection object ConnectionString property, then omit the ConnectionString parameter from the Open method.
This tutorial uses the following Connection object declaration and Open method:
Dim cnn As New ADODB.Connection
cnn.Open "DSN=Pubs;uid=sa;pwd=;"