| < Day Day Up > |
|
The Connection object describes the connection between the application program and the database server. Coupled with the Errors collection and the Error object, the Connection object controls the logical connection to the database.
Table 23-1 lists some of the key properties and methods associated with the Connection object.
Property/Method | Description |
---|---|
Close | Method: closes the connection to the database. |
ConnectionString | Property: defines how to connect to the database. |
CursorLocation | Property: specifies where the current record pointer is maintained. Can be: adUseClient or adUseServer. |
Errors | Property: pointer to an Errors collection object containing the list of individual Error objects that describe the most recent error encountered on the connection. |
Mode | Property: determines how the database is opened. Can be adModeRead, adModeWrite, adModeReadWrite, adModeShareDenyRead, adModeShareDenyWrite, adModeShareExclusive, or adModeShareDenyNone. |
Open ConnectionString, | Method: establishes a connection to the database using the optionally supplied ConnectionString, UserId, and Password values. |
State | Property: describes the state of the connection. Can be adStateClosed or adStateOpen. |
The Open method opens a connection to the database using the optional information. If not supplied, the information from the ConnectionString property will be used. The Close method terminates an open connection to the database server. The State property allows you to determine if the connection is open or closed to the database.
The CursorLocation property determines where the cursor is kept. Typically, you’ll set this property to adUseServer to simplify the programming involved, especially if you’re using Access. However, if you’re using SQL Server, Oracle, DB2, or some other shared database server, you might want to check with your database administrator to determine what value is appropriate.
The Mode property determines what level of data access you require. Specifying adModeRead means that your program only reads data from the database and that your program can coexist with other applications that specified adModeRead. If you plan only to write to the database (which isn’t very common), you can use the adModeWrite value. However, if you plan to read and write information from the database, you should choose the adModeReadWrite value.
The adModeShareDenyRead, adModeShareDenyWrite, and adModeShareExclusive properties all control how other programs will share the database with yours. In general, if you have multiple users sharing the same database, see your database administrator for the appropriate values for the Mode property.
The ConnectionString property is the most important property in the Connection object. The exact information in the connection string varies depending on the type of database you want to use.
Connection strings are composed of a series of keywords and values separated by semicolons that provide the information necessary to locate the data provider and the database, along with any security information that might be required.
Access databases rarely require little more than the name of the provider and the location of the database file in a connection string, like this:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Excel2k3\Customer.mdb
The Provider keyword selects the data provider for Access. For Access 2000 and newer databases, you should use the Microsoft.Jet.OLEDB.4.0 provider. Older Access databases might require this provider: Microsoft.Jet.OLEDB.3.51.
Note | The database engine typically used to power an Access database is known as Microsoft Jet. In addition to Access, several other products rely on the Jet database engine. However, because most people access Jet only through Access, it’s common to refer to the database engine as Access as well. |
The Data Source keyword points to the location of the database on disk. Typically, this is a file path as shown in the earlier example, although it can reference a file using a file share format (\\athena\d\Excel2k3\Customer.mdb).
Connecting to SQL Server databases involves a somewhat more complicated connection string, although it’s not as bad as you might expect.
Provider=SQLOLEDB.1;Data Source=athena.justpc.com;
Initial Catalog=Music;User ID=Wayne;Password=TopSecret
The Provider keyword references SQLOLEDB.1, which is the SQL Server data provider.
The Data Source keyword points to the database server that you want to access, and the Initial Catalog keyword indicates the name of the database on the particular database server that you want to open.
Finally, the User ID and Password keywords present authentication information to the database server. If you wish to use your Windows authentication information—the User ID that you gave when you logged onto Windows—you can replace the User Id and Password keywords with the Integrated Security keyword, as in the following connection string:
Provider=SQLOLEDB.1;Data Source=athena.justpc.com;
Initial Catalog=Music;Integrated Security=SSPI
As long as you can find the proper data provider you can connect to many different types of databases. The main requirement is that the database supports OLE DB. Some of these databases and their providers are listed in Table 23-2.
Database | Data Provider(s) |
---|---|
DB2 | IBM OLE DB Provider for DB2 |
Oracle | Microsoft OLE DB Provider for Oracle Oracle Provider for OLE DB |
In case the database you wish to use doesn’t support OLE DB, but supports only ODBC, Microsoft includes a special data provider named Microsoft OLE DB Provider for ODBC drivers, which translates OLE DB calls into ODBC. However, you should use this provider only if you can’t find an OLE DB provider because performance will suffer.
The Errors collection contains information about the errors that occur while executing a database command. Because it’s quite possible that one error might trigger several others when executing a command, you can’t always rely on the VBA Err object to reveal the true cause of the error. Therefore, the ADO Errors collection contains the full set of errors that occur while executing a command. (See Table 23-3.)
Property/Method | Description |
---|---|
Clear | Method: Removes all the items from the collection. |
Count | Property: Returns the number of items in the collection. |
Item(index) | Property: returns the Error object at the location specified by index. |
Tip | Clearing the Err |
The Errors collection object is a typical collection object containing a Count property and an Item property that returns an Error object. You can use a For…Each loop to process all the items in the collection, and then use the Clear method to erase all the Error objects in the collection.
Note | The Errors collection is populated only when an error occurs. Use the Clear method before you execute a command to avoid detecting an error that has already been handled. |
The Error object contains a number of properties that describe a specific error condition. (See Table 23-4.) For the most part, these properties provide information about the error from several different perspectives, which might be necessary depending on the exact cause of the error.
Property | Description |
---|---|
Description | Contains a short text description of the error. |
NativeError | Contains a provider-specific error code. |
Number | Contains the OLE DB error code. |
SQLState | Contains the standard five-character ANSI SQL error code. |
| < Day Day Up > |
|