Step 1: Open a Connection (ADO Tutorial)

See Also   

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. Following is the syntax for each method in Visual Basic:

connection.Open ConnectionString, UserID, Password, OpenOptions
recordset.Open Source, ActiveConnection, CursorType, LockType, Options

It's helpful to compare these two methods to highlight some useful characteristics of ADO method operands in general.

ADO provides several convenient ways to specify an operand. For example, Recordset.Open takes an ActiveConnection operand, which could be the literal string, a variable representing that string, or a Connection object representing an open connection.

Most methods on an object have properties that can provide an argument if a method operand is omitted. In the case of Connection.Open, you could omit the explicit ConnectionString operand and supply the information implicitly by setting the ConnectionString property to "DSN=pubs;uid=sa;pwd=;database=pubs".

Conversely, the uid and pwd keyword operands in a connection string can set the Connection object UserID and Password parameters.

This tutorial invokes the Connection.Open method with an explicit connection string. The data source will be the Open Database Connectivity (ODBC) pubs database, which ships as a test database with Microsoft SQL Server. (The actual location of the data source—such as a local drive or remote server—is specified when you define the Data Source Name (DSN).)

connection.Open "DSN=pubs;uid=sa;pwd=;database=pubs"

Next   Step 2