Connecting to a Database

Microsoft® Windows® 2000 Scripting Guide

« Previous | Next »   

To connect to a database by using ADO, you follow the same standard procedure regardless of the type of database to which you are connecting. Your script must:

1. Create instances of the Connection and Recordset objects.
2. Use the Connection object Open method to connect to the DSN for the database.
3. Use the Recordset object Open method to retrieve data from the desired table within the database.

When using the Recordset object Open method, include both a standard SQL command (for example, "SELECT * FROM Inventory") and parameters specifying CursorLocation, CursorType, and LockType. These parameters are described in more detail in Table 17.8.

Table 17.8   Parameters for the Recordset Object Open Method

Property	Description
CursorLocation	Data structure that holds the results of any query you make. Cursors can be stored either on the server or on the client. In general, it is better to store the cursor on the client; this tends to improve performance (because the data is stored locally) and places less strain on the database server. To set the CursorLocation to the client, set the value of the constant adUseClient to 3.
CursorType	Allows you to browse a recordset in different ways: some cursors allow you to move backward and forward in a recordset, while other cursors limit you to moving forward only. If you set the CursorLocation to the client, you must set the CursorType to adOpenStatic (value = 3). This type supports scrolling forward and backward in the recordset but does not show changes made by other users. This is because the cursor is operating on data cached on the client computer rather than directly from the database.
LockType	Determines how (and if) a recordset can be updated. Recordsets can be set to read-only, or they can be configured to allow updates. For most scripts, the LockType can be set to adLockOptimistic (value = 3). With this setting, the record being edited is not locked (that is, no restrictions are placed on another user accessing that record) until you call the Update method.

When you are finished with the database connection and the recordset, use the Close method to close both objects.

Scripting Steps

Listing 17.15 contains a script that creates an ADO connection. To carry out this task, the script must perform the following steps:

1. Create three constants adOpenStatic, adLockOptimistic, and adUseClient and set the value of each to 3.

   These constants will be used to configure the CursorLocation, CursorType, and LockType for the connection.

2. Create an instance of the ADO Connection object (ADODB.Connection).

   The Connection object makes it possible for you to issue queries and other database commands.

3. Create an instance of the ADO Recordset object (ADODB.Recordset).

   The Recordset object stores the data returned from your query.

4. Use the Connection object Open method to open the database with the DSN Inventory.

   Be sure to append a semicolon (;) to the DSN name.

5. Set the CursorLocation to 3 (client side) by using the constant adUseClient.
6. Use the Recordset object Open method to retrieve all the records from the Hardware table.

   The Open method requires four parameters:

   • The SQL query ("SELECT * FROM Hardware")
   • The name of the ADO connection being used (objConnection)
   • The cursor type (adOpenStatic)
   • The lock type (adLockOptimistic)
7. Close the recordset.

   In an actual production script, of course, you would probably do something with a recordset besides just opening and closing it.

8. Close the connection.

Listing 17.15   Connecting to an ADO Database

1 2 3 4 5 6 7 8 9 10 11 

Const adOpenStatic = 3 Const adLockOptimistic = 3 Const adUseClient = 3 Set objConnection = CreateObject("ADODB.Connection") Set objRecordset = CreateObject("ADODB.Recordset") objConnection.Open "DSN=Inventory;" objRecordset.CursorLocation = adUseClient objRecordset.Open "SELECT * FROM Hardware" , objConnection, _     adOpenStatic, adLockOptimistic objRecordset.Close objConnection.Close

---

 Send us your feedback

« Previous | Next »