Visual Basic 6 Black Book
(Publisher: The Coriolis Group)
Author(s): Steven Holzner
ISBN: 1576102831
Publication Date: 08/01/98

ADO: Opening A Connection

The Testing Department is calling again. The company is switching to using ActiveX Data Objects—how about setting up an ADO database-editing program? Already on it, you say.

The first step in editing an ADO database is to open that database, which is called a data source in ADO terminology, by setting up a Connection object. To use that and other ADO objects in code, you use the Project|References item, select the Microsoft ActiveX Data Objects Library item, and click on OK, adding the ADO Object Library to your program.

Now we’re free to create a new ADO Connection object with the Connection object’s Open method:

connection.Open ConnectionString [,UserID [, Password [, OpenOptions]]]

Here are the arguments for this method:

•  ConnectionString—String containing connection information.

•  UserID—String containing a username to use when establishing the connection.

•  Password—String containing a password to use when establishing the connection.

•  OpenOptions—If set to adConnectAsync, the connection will be opened asynchronously.

Let’s see an example. When we start our ADO code example, the adocode example (see “A Full-Scale ADO Example” in this chapter), we’ll establish a connection, db, to the database we built in the previous chapter, db.mdb:

Private Sub Form_Load()
    Dim db As Connection
    Set db = New Connection

    db.Open "PROVIDER=Microsoft.Jet.OLEDB.3.51;Data _
        Source=C:\vbbb\adocode\db.mdb;"
...
End Sub

And that’s it—now we have a connection to the data source. To actually work with the data in that data source, we’ll create an ADO record set in the next topic.

ADO: Creating A Record Set From A Connection

Now that you’ve created an ADO connection, you can open a record set from that connection using the Recordset object’s Open method:

recordset.Open [Source, [ActiveConnection, [Type, [LockType, [Options]]]]

Here are the arguments for this method:

•  Source—A valid Command object variable name, an SQL statement, a table name, a stored procedure call, or the file name of a Recordset.

•  ActiveConnection—A valid Connection object variable name or a string containing ConnectionString parameters.

•  Type—Sets the Recordset type (see the following list).

•  LockType—A value that determines what type of locking (concurrency) the provider should use when opening the Recordset (see the following list).

•  Options—A Long value that indicates how the provider should evaluate the Source argument if it represents something other than a Command object, or that the Recordset should be restored from a file where it was previously saved (see the following list).

Here are the possible values for the Type argument:

•  dbOpenKeyset—Opens a dynaset-type Recordset object, which is like an ODBC keyset cursor.

•  dbOpenDynamic—Opens a dynamic-type Recordset object, which lets the application see changes made by other users.

•  dbOpenStatic—Opens a static-type Recordset object.

•  dbOpenForwardOnly—Opens a forward-only-type Recordset object, where you can only use MoveNext to move.

Here are the possible values for the LockType argument:

•  adLockReadOnly—The default; read-only.

•  adLockPessimistic—Pessimistic locking, record by record.

•  adLockOptimistic—Optimistic locking, record by record.

•  adLockBatchOptimistic—Optimistic batch updates.

Here are the possible values for the Options argument:

•  adCmdText—Provider should evaluate Source as a definition of a command.

•  adCmdTable—ADO should generate an SQL query to return all rows from the table named in Source.

•  adCmdTableDirect—Provider should return all rows from the table named in Source.

•  adCmdStoredProc—Provider should evaluate Source as a stored procedure.

•  adCmdUnknown—Type of command in the Source argument is not known.

•  adCommandFile—Record set should be restored from the file named in Source.

•  adExecuteAsync—Source should be executed asynchronously.

•  adFetchAsync—After the initial quantity specified in the CacheSize property is fetched, any remaining rows should be fetched asynchronously.

Let’s see an example. In our ADO code example, the adocode example (see “A Full- Scale ADO Example” in this chapter), we create a record set, adoRecordset, by first declaring it as a form-wide variable:

Dim adoRecordset As Recordset

Next, we select all the records in the students table this way when the form loads, using the Open method:

Private Sub Form_Load()
    Dim db As Connection
    Set db = New Connection

    db.Open "PROVIDER=Microsoft.Jet.OLEDB.3.51;Data _
        Source=C:\vbbb\adocode\db.mdb;"
    Set adoRecordset = New Recordset
    adoRecordset.Open "select Grade, Name from students", _
        db, adOpenStatic, adLockOptimistic
...
End Sub

Now that we’ve opened our result set, we can bind that result set to various controls, like text boxes, as we’ll do in the next topic.