| Downloads |
|---|
| 99418.zip |
ActiveX Data Objects (ADO) is useful for accessing various types of databases. For example, you can use ADO within VBScript to connect to and access data contained in Microsoft Excel spreadsheets. In "Using ADO to Access Excel, Part 1," (InstantDoc ID 99123), I presented the script ADOExcel.vbs that demonstrates how to use ADO to access Excel. Now, I'll expand on that basic information to explain ADO cursor types, describe how to sort a recordset, and show you how to use a universal data link (.udl) file as your ADO connection.
Cursor Types
The ADO CursorType property specifies how you move through a recordset and whether changes made to a database are visible after you retrieve the recordset. The four main cursor types are adOpenDynamic, adOpenForwardOnly, adOpenKeyset, and adOpenStatic.
adOpenDynamic. This cursor type allows all types of movement through the recordset. In addition, it lets you view additions and changes made by other users. Use adOpenDynamic if multiple users will need to modify rows in the database at the same time. The adOpenDynamic constant value is 2.
adOpenForwardOnly. This cursor type behaves like a dynamic cursor type, except that you can only scroll forward through a recordset. In other words, you can only use the MoveNext method within a script. The adOpenForwardOnly cursor type is very efficient and can improve performance when carrying out sequential reads of a data source. Use this cursor type if you need to quickly run through a recordset from top to bottom—but realize that you're limited to a single pass through the recordset. The adOpenForwardOnly constant value is 0.
adOpenKeyset. This cursor type also behaves like a dynamic cursor type, except that you can't see records that other users add—nor can you access records that other users delete. Data changed by other users is still visible. The adOpenKeyset constant value is 1.
adOpenStatic. This cursor type returns a static copy of a recordset and allows for all types of movement through the recordset. However, additions, changes, and deletions by other users aren't visible. The adOpenStatic constant value is 3.
Not all cursor types are supported by all providers. In some cases, certain providers might even change the cursor type depending on the initial CursorLocation and CursorType property settings. As you experiment with various combinations of settings, you might want to set up a simple MsgBox function that tells you various properties' settings after you call your Open method. For instance, you could immediately follow your Recordset object Open call with the following statements to see the actual CursorLocation and CursorType values:
MsgBox "CursorLocation" & _
oRS.CursorLocation
MsgBox "CursorType" & oRS.CursorType
Sorting a Recordset
The cursor types will be especially important if you decide to sort recordsets within your scripts. To use the Sort method of a Recordset object, either the Connection object's CursorLocation property or the Recordset object's CursorLocation property must be set to client-side. By default, CursorLocation is set to server-side, which doesn't allow sorting. The CursorLocation property defines where a recordset is created when it's opened. Using client-side cursor locations can be faster if you're simply reviewing data, because the script doesn't have to access the server after the recordset is created, except when the Update method is called. But using server-side cursor locations can be faster if you have poor network performance and you're making a lot of updates, because most of the processing takes place on the server (although you can't use the Sort method).
When you use a client-side cursor location, data is actually disconnected from the source database. ADO retrieves the selection query and copies the recordset into the client's memory. Then when you issue a Recordset object Update call, ADO interprets your changes into an action query and sends the query to update the database.
To use a client-side cursor location in VBScript, set up a Const statement like this:
Const adUseClient=3
Then, refer to the constant name when you set the CursorLocation property.
Just before you call the Recordset object's Open method, set the CursorLocation property with a statement like this:
oRS.CursorLocation=adUseClient
As I mentioned previously, the default cursor location is server-side. To specifically set the cursor location to server-side, you'd use a statement such as:
Const adUseServer=2 oRS.CursorLocation=adUseServer
Note that if you use a client-side cursor location, the cursor type is changed to adOpenStatic even if you previously set it to a different cursor type. In my experience, using a client-side cursor location changes the cursor type to adOpenStatic even if I've previously set it to another cursor type. I don't know whether different providers generate different behavior (e.g., automatically adjusting the cursor type, throwing an error message).
To actually sort a recordset, download the script ADOExcelPart2.vbs. (Click the Download the Code button at the top of the page.) This script is a modified version of the ADOExcel.vbs script from "Using ADO to Access Excel, Part 1," it includes additions to incorporate the CursorLocation property change, as well as code to demonstrate sorting a recordset. After you modify the cursor location to client-side, you can use ADOExcelPart2.vbs to perform a simple sort on a recordset.
Call the Recordset object's Sort method followed by an equal sign; within double quotes, specify the field name you want to sort on. If you want to sort the field in descending order, follow the field name with a space and the keyword DESC, as in the following code:
oRS.Sort = "Header0 DESC"
If you don't specify descending order, the default of ascending order will be followed. Alternatively, you can explicitly specify ascending order by using the keyword ASC, as in the following code:
oRS.Sort = "Header0 ASC"
You aren't limited to sorting on just one field. You can sort on multiple fields and specify ascending or descending order for each field. For instance, you could perform a sort like this:
oRS.Sort = "Header0 DESC, Header3 ASC"
The primary sort in this statement is on Header0 in descending order; the secondary sort is on Header3 in ascending order. Be sure to separate your sort definitions with a comma, as shown.
Listing 1 contains the ADOExcelPart2.vbs code that sorts and filters a recordset. Callout A shows how you can step through a recordset after calling the Sort method and display the sorted Header0 field values.
Universal Data Link
Rather than manually keying in all the ConnectionString properties within a script, you can set up a connection string outside of the script as a .udl file and reference it within your code. The script ADOExcelUDL.vbs demonstrates how to use this method. (To download this script, click the Download the Code button at the top of the page.) Callout A in Listing 2 shows the code for setting up a .udl file and creating an ADO connection. You must create a blank text file in the C:\temp folder and name it xls.txt. Then, rename the file with a .udl extension so that the name is xls.udl. Double-click the file to open it. The Data Link Properties screen will appear.
Select the Provider tab, and click Microsoft Jet 4.0 OLE DB Provider (if you're using a version of Excel prior to 2007) or Microsoft Office 12.0 Access Database Engine OLE DB Provider (if you're using Excel 2007), as Figure 1 shows. As Callout B in Listing 2 shows, the providers that I used in "Using ADO to Access Excel, Part 1" have been commented out of ADOExcelUDL.vbs.
Click Next to navigate to the Connection tab, where you can enter your Excel spreadsheet's path and filename in the Data Source text box, as Figure 2 shows. Leave the other sections blank, and select the Advanced tab.
In the Advanced tab, verify that the Share Deny None check box is selected, as Figure 3 shows. Then, select the All tab.
In the All tab, double-click Extended Properties (or highlight Extended Properties and click Edit Value). The Edit Property Value dialog box will open. In the Property Value text box, enter Excel 12.0;HDR=YES; for Excel 2007, as Figure 4 shows, or Excel 8.0;HDR=YES; for Excel versions prior to 2007. Click OK.
Now, you can test the connection that you've created. Select the Connection tab and click the Test Connection button near the bottom of the dialog box. If everything is set up correctly, you'll see a popup that says Test connection succeeded. Click OK to finish creating the .udl file.
In addition to using a .udl file as an ADO connection file, you can also use a UDL to generate a provider connection string. After you have a working .udl file, simply open a blank text file and drag the .udl file into it. This action will give you a textual provider string that you can plug into your code as a provider string. To achieve the same result, you could make a copy of the .udl file and rename it with a .txt extension, then open the file.
Note that if you edit the Extended Properties value, you must add a second set of quotations marks to the string. See the sample text file in Figure 5 as an example. You'd need to modify the Provider string in this text file to look like this:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\Temp\~Test~Spread~Sheet.xlsx;Extended Properties=""Excel 12.0;HDR=YES;"";Persist Security Info=False
(Notice the double quotes around the Extended Properties parameter.)
Then, you'd need to copy this line and insert it into the code, like so:
oConn.connectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=c:\Temp\~Test~Spread~Sheet.xlsx;" & _
"Extended Properties=""Excel 12.0;HDR=YES;"";Persist Security Info=False"
This technique provides an easy method for ensuring that your provider strings are correct. You can use this method for setting up provider strings in other providers as well—not just in Excel.
Take Advantage of ADO
Using ADO to access Excel spreadsheets provides numerous benefits. In effect, your spreadsheets become databases that you can programmatically access, query, sort, search, and modify.
