Databases Reference
In-Depth Information
where all parameters are optional. The
ConnectionString
parameter is the tricky
one here. We will discuss connection strings at length later in the chapter. Note
that the
Connection
object has a ConnectionString property that can be used to set
the connection string as well. However, the
ConnectionString
parameter will
override any setting of the ConnectionString property.
Microsoft warns that we should not pass UserID and password values in
both
the
ConnectionString property and the
ConnectionString
parameter of the
Open
method, for this may lead to unpredictable results. (And here I thought that
computers did not produce unpredictable results.)
Note that it is important to close a connection using the
Close
method when the
connection is no longer required. However, closing the connection does not
remove the
Connection
object from memory, so its properties may still be
accessed or altered. In order to remove the
Connection
object from memory, we
must set the variable that references the
Connection
object to
Nothing
.
The
Options
parameter can assume one of the following values:
adConnectUnspecified
The default value. Opens the connection synchronously. Code execution pauses
until the connection is made.
adAsyncConnect
Opens the connection asynchronously. The ConnectComplete event is fired when
the connection is complete.
OpenSchema
Gets database information from the data provider. The simplest syntax for this
method is:
ConnectionObject.OpenSchema(
QueryType
)
where
QueryType
can be one of several constants specifying the type of
information to retrieve. The method returns a
Recordset
object with the requested
data.
For instance, the following code lists the tables in a Jet database:
' Get list of tables
Set rs = cn.OpenSchema(adSchemaTables)
Do While Not rs.EOF
Debug.Print rs!TABLE_NAME & " Type: " & rs!TABLE_TYPE
rs.MoveNext