Data Access

Windows Script Host (WSH) doesn't provide any built-in database access. It relies on parts of a new database strategy that Microsoft refers to as Universal Data Access (UDA). This strategy consists of three elements: Open Database Connectivity (ODBC), Object Linking and Embedding Database (OLE DB), and ActiveX Data Objects (ADO).

ODBC

Open Database Connectivity (ODBC) provides database access to any database source that has a driver available for it. It has been available since versions of Windows 3.x.

ODBC is considered to be a "standard," and there are hundreds of drivers available that allow access to data sources from ASCII text files to high-end database servers that run on mini and mainframe computers. However, there is no way to use ODBC drivers directly in WSH.

OLE DB

Microsoft has changed the way data is accessed via the new Object Linking and Embedding Database (OLE DB). OLE DB will provide access to data sources in a similar way to ODBC: A client will connect to a data source via a driver.

OLE DB goes beyond the capabilities of what is possible with ODBC. OLE DB provides access to traditional database sources, such as SQL Server, Oracle, Access, and FoxPro, as well as nondatabase-type applications. These nondatabase data sources include any application or environment that can represent data in a tabular format. This could include graphical applications, spreadsheet data, and traditional relational databases.

It's with regard to nondatabase-type data access where OLE DB provides more capabilities than ODBC. Examples of nonrelational data providers that exist today are the IIS Index Server, Internet Provider, and ADSI interfaces.

Another way that OLE DB differs from ODBC is that OLE DB is implemented as a COM interface. Any environment that can interface with COM objects can use OLE DB.

The following terminology is used to describe the various components of the OLE DB architecture:

• Data consumer: A data consumer is a client environment that "consumes" or uses the data. This can be any application that is able to create COM objects, such as VB, C++, Delphi, Office applications or, in our case, WSH.
• Data provider: An OLE data provider is a provider that exposes its data in a tabular form by using its native data format. A data provider such as ADSI exposes an OLE DB interface to a data consumer. Examples of data providers include relational DBMSs, spreadsheets, and e-mail systems.
• Service provider: A service provider is both a data consumer and a data provider. A service provider doesn't own its data, but rather it encapsulates some service by producing and consuming data through OLE DB interfaces. For example, a query processor might serve to join two tables in two different data sources. The query processor retrieves data from the base tables and then returns the resultant join of the tables to the data consumer.

SQL Server 7.0 is an example of a service provider. It provides transparent linking of diverse data sources using its linked server capability.

OLE DB will not replace ODBC anytime in the near future, mainly due to the huge existing base of ODBC drivers and the fact that ODBC still works.

There is an OLE DB provider that provides access to ODBC drivers, so existing investments in ODBC drivers and applications are not lost.

Using ADO

You don't natively access OLE DB through WSH. A COM interface, ActiveX Data Objects (ADO), provides access and interfaces to data providers. OLE DB is the framework for Universal Data Access in Microsoft environments. How do you get at that data? ADO is the interface that provides access to the data. As the name implies, ADO is implemented as a COM object.

History of ADO

ADO has been in use since late 1996 in Microsoft's Internet Information Server (IIS) 3.0 and later versions. IIS uses ADO 1.0 in Active Server Pages (ASP) to provide data manipulation capabilities to Web pages. With ADO and IIS 3.0, ASP Web pages could query, update, delete, and manipulate data from any ODBC data source.

ADO 1.5 is available in Internet Explorer 4.0 and IIS 4.0, and it is also available as a stand-alone download known as Microsoft Data Access Components.

Table 13-1 lists the various ADO versions and the differences among them.

If clients on your network are using Internet Explorer and/or Windows 98, they have ADO installed.

Unless otherwise specified, this chapter concentrates on core database access capabilities that are available in all versions of ADO.

Identifying Your Data

Before you can start working with some data, you need to know how to identify to ADO what data source you want to work with. This is accomplished with a connection string. The connection string identifies the data source and can supply optional parameters such as a user ID and a password. These optional parameters depend upon the data source-for example, some data sources may not support a user ID and password.

There are two types of (similar) connection strings: a data source name (DSN) connection and a DSN-less connection.

Data Source Name Connection Strings

ADO can access ODBC data sources through an ODBC OLE DB interface. This means that any database that can be accessed through an ODBC driver can be accessed through ADO, so you don't lose any investment in existing ODBC driver software and configurations.

The disadvantage of using a data source name (DSN) to specify a data source is that the DSN must exist either on the local machine or alternatively reference a file DSN. A file DSN consists of a file containing data source connection information and provides a more centralized way of managing connections because you do not require configuration of the DSN on each machine (see Solution 13.3).

A DSN connection specifies an existing defined ODBC data source. The data source can be defined using the Control Panel's ODBC Data Source Administrator (under Windows NT 4.0/ME/9x) or Data Sources (Windows 2000/XP) applet, which allows the creation of data sources via a user interface.

To create an ODBC data source, follow these steps:

1. Select Start menu > Control Panel.
2. For Windows NT/9x/ME, double-click the ODBC Data Sources icon. For Windows 2000/XP, double-click Data Sources (ODBC) under the Control Panel's Administrative Tools entry.

   A dialog box similar to the one shown in Figure 13-1 appears.

   Figure 13-1: ODBC Data Source Administrator dialog box

   The ODBC Drivers tab lists the available driver and version information. Looking at this tab is a good way of determining what ODBC drivers are installed on your system.

3. Select either the User DSN or System DSN tab. The System DSN tab only applies to Windows NT. Under Windows NT, a System DSN is available to all logons, including services, while a User DSN is available to the user currently logged on.
4. Click the Add button.

   You are now presented with a list of drivers, as shown in Figure 13-2. If a particular driver does not appear in the list, it's not installed on your system.

   Figure 13-2: The Create New Data Source dialog box lists drivers.

5. Select the driver you want to use and click the Finish button.

   A dialog box appears for the driver you specified. Figure 13-3 shows the setup dialog box for a text file.

   Figure 13-3: ODBC Text Setup dialog box

Settings will vary between drivers, but all drivers use the Data Source Name and Description fields. The Data Source Name field is used to identify the data source and is what is used when connecting to the data source via ADO.

Once you have created a data source, you can connect to it in a script. To perform a connection to a data source you need a Connection object.

The Connection object represents the physical connection to the data provider. It is required in order to perform data access operations. While all ADO objects can be created independently of each other, they all need one thing in order to function (or at least in order to do anything useful): a connection to data provider.

The connection process can be implemented in a couple of ways. You can first create a Connection object that you can reuse among other ADO objects, or you can create an ADO object such as a Recordset, which handles the process of creating a Connection object and opening a connection to the data source.

To connect to a data source, supply the connection string for the data source to the Open method of a Connection object. Its syntax is as follows:

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

Table 13-2 lists the Open method's arguments.

The format of the connection string is as follows:

strConn = "[Provider=providername;] { DSN=name | FileDSN=filename } ; [DATABASE=database;] UID=user; PWD=password"

Table 13-3 lists the connection string syntax.

File DSN Connection

File-based DSNs can ease the distribution and centralize the maintenance of data sources, because they can be maintained in a central location, such as a network file server.

File DSNs can be created from the ODBC Data Source Administrator (under the File DSN tab). The following steps demonstrate the creation of a file DSN used in the Solution:

1. Select Start menu > Control Panel.
2. For Windows NT/9x/ME, double-click the ODBC Data Sources icon. For Windows 2000/XP, double-click Data Sources (ODBC) under the Control Panel's Administrative Tools entry.
3. Select the File DSN tab.

   A dialog box appears, as shown in Figure 13-4.

   Figure 13-4: File DSN tab

   From the File DSN tab you can add new file DSNs and configure the location of the files.

4. Click the Add button.

   You are now presented with a list of drivers. If a particular driver does not appear in the list, it's not installed on your system.

5. Select the driver you want to use and click the Next button.

   A dialog box similar to the one shown in Figure 13-5 appears, prompting you to type a filename. This filename represents the name of the file where the connection settings will be stored. The filename is also used to identify the data source in the connection string.

   Figure 13-5: Create New Data Source dialog box

6. Click the Next button.

   The dialog box shown in Figure 13-6 appears, listing information about the file DSN you have created.

   Figure 13-6: File DSN information

   The files are stored in a locally defined location, but they can be moved to a central, shared location such as a file server.

DSN Less Connection Strings

OLE DB data providers allow access to a wide variety of sources. Some of these OLE DB data providers are shown in Table 13-4. The string in the Provider Name column is supplied as an argument to the Provider= parameter of the connection string.

DSN-less connections also allow for connections without having a preexisting ODBC connection or file-based DSN.

The syntax of a DSN-less connection string is as follows:

strConn = "[Provider=MSDASQL;] DRIVER=driver; SERVER=server; DATABASE=database; UID=user;PWD=password"

Table 13-5 lists the connection string arguments.

Because you can omit the Provider= parameter when building connection strings for the ODBC provider, you can compose a connection string that is identical to an ODBC connection string for the same data source, using the same parameter names (DRIVER=, DATABASE=, DSN=, and so on) as well as the same values and syntax you would when composing an ODBC connection string. You can connect with or without a predefined data source name (DSN) or file DSN.

strConn = "driver={SQL Server};server=Odin;" & _ "database=pubs;uid=pubreado;pwd=pubpass"

OLE DB providers can support a great number of parameters to fine-tune the connection to a database provider. The only problem with these parameters is that it can be hard to remember the possible syntax and variations. It can be hard enough to remember the provider name, let alone any additional parameters. What would be helpful is an interface similar to ODBC Data Source configuration option under the Control Panel that provides a visual interface to set connection parameters.

To address this limitation, Microsoft introduced Microsoft Data Link files (files with an extension of .udl) in ADO version 2.0. These files contain OLE DB connection information.

To create a UDL file to connect to an OLE DB data source, follow these steps:

1. Right-click within an Explorer window or from your desktop and select Microsoft Data Link from the New menu.

   This method of creating a UDL was removed from ADO 2.5. To create a UDL under ADO 2.5, create a text file and change the file extension from .txt to .udl.

2. Give the new UDL file a descriptive name.
3. Right-click the file and select Properties from the menu. Alternatively, double-click the file.
4. A tabbed dialog box like the one shown in Figure 13-7 appears. You can now set the properties of the UDL file.

   Figure 13-7: The Data Link Properties dialog box

   The Provider tab enables you to select the provider type. Looking at this tab is a good way of determining what OLE DB providers are installed on your system. If a particular provider does not appear on the list, it's not installed.

   The Connection and Advanced tabs enable you to set provider-specific information. The parameters you can set here vary from provider to provider. For example, Figure 13-7 shows a Connection tab that has been customized for the Microsoft Jet OLE DB provider.

   The All tab alphabetically lists all property settings from the Advanced and Connection dialog boxes.

5. Once you are finished configuring your data link, click the OK button.

You can modify the UDL file at any time by double-clicking it from Explorer.

To use a UDL file, reference it in the connection string using the File Name parameter. For example, you would reference a .udl file named Northwind.udl with the following statement:

strConn = "File Name=d:Northwind.udl"

Using a .udl file or .dsn file still depends on the existence of a file. The ODBC Data Source Administrator and the Data Link Properties dialog box makes creating these files easier. You can use the files the interfaces create to build connection strings.

Both .udl and .dsn files are text files that can be read by text editors such as Notepad. The parameters for connecting to a data source are contained in the files in user-readable format and can be used to build connection strings.

To create a connection string using the Data Link Properties dialog box, follow these steps:

1. Create a .udl connection for your data source using the Data Link Properties dialog box.
2. Open the .udl file using an ASCII text editor such as Notepad.

   You will see contents similar to the following:

   [oledb] ; Everything after this line is an OLE DB initstring Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:inventory.mdb; Persist Security Info=False

   The connection strings will vary depending on the provider you specified in the .udl file.

3. Copy the string from the second line onward. You can paste this string directly into your script as a connection string.

To create a connection string using the ODBC Data Source Administrator to create a file DSN, do the following:

1. Create a file DSN connection using the ODBC Data Source Administrator.
2. Open the .dsn file using an ASCII text editor, such as Notepad.

   The contents of the following DSN file contain connection information for an Excel file:

   [ODBC] DRIVER=Microsoft Excel Driver (*.xls) UID=admin UserCommitSync=Yes Threads=3 SafeTransactions=0 ReadOnly=1 PageTimeout=5 MaxScanRows=8 MaxBufferSize=512 ImplicitCommitSync=Yes FIL=excel 5.0 DriverId=790 DefaultDir=H: DBQ=H:xldata.xls

3. Combine all lines after the [ODBC] entry in the file into one line. Separate each entry with a semicolon (;).

   DRIVER=Microsoft Excel Driver (*.xls);UID=admin;UserCommitSync=Yes;Threads=3; SafeTransactions=0;ReadOnly=1;PageTimeout=5;MaxScanRows=8;MaxBufferSize=512; ImplicitCommitSync=Yes;FIL=excel 5.0;DriverId=790;DefaultDir=d:;DBQ=d:xldata.xls

This creates quite a long connection string. You will notice that a large number of parameters are being set. To shorten the connection string, remove all items that you have not explicitly set in the ODBC Data Source Administrator. The following connection string is a shortened version of the previous sample:

DRIVER=Microsoft Excel Driver (*.xls);DBQ=d:xldata.xls

The following code snippets provide examples of connection strings.

For example, the following code creates a connection to the Northwind database using the Jet 3.51 provider:

'Opens a Access MDB using the Access 3.51 data provider Set objConn = CreateObject("ADODB.Connection") objConn.Provider = "Microsoft.Jet.OLEDB.3.51"

The following example opens a connection to SQL Server and sets the default database to Pubs:

Set objConn = CreateObject("ADODB.Connection") objConn.ConnectionString = "Provider=SQLOLEDB;User ID=sa;Data Source=Odin" objConn.Open objConn.DefaultDatabase = "pubs"

The following code fragment opens an Access MDB using ODBC:

objConn.ConnectionString = "DBQ=D:DataSamplesNorthwind.MDB;" & _ "DRIVER={Microsoft Access Driver (*.mdb)};" & _ "UID=admin;PWD=;" objConn.Open

Opening a Secure Database

Problem

You want to open a secure database.

Solution

You can use the following script:

Dim objConn, objRst Set objConn = CreateObject("ADODB.Connection") 'open a connection and provide a user id and password objConn.Open "Provider=SQLOLEDB.1;Initial Catalog=pubs;Data Source=ODIN" _ , "freds", "sderf" 'execute a query Set objRst = objConn.Execute("Select Sum(ytd_sales) As TotalSales From titles") 'display the value Wscript.Echo objRst("TotalSales").Value objRst.Close objConn.Close

Discussion

Many databases, especially server-based ones such as SQL Server, provide sophisticated security mechanisms to limit access to data. They can require authentication before access to the data is possible.

The problem with scripts is that they are plain text and easily readable. There are a couple of ways to eliminate passwords from being sent. The security mechanisms will vary from provider to provider.

The level of security required should be determined by the sensitivity of the data being accessed.

SQL Server provides different levels of security. SQL Server can provide an internal user database that is maintained using SQL Enterprise Manager, or NT authentication, where access to the database is determined by the user's NT ID. The advantage of using NT authentication is it requires no passwords to be stored in the script file. These security mechanisms are determined by how your SQL Server is configured: You may have a user database, NT security, or both.

For any user's account that requires a password, try to limit the access the user has to the database to the absolute minimum for the operations required.

Microsoft Access also provides database security. Users are stored in a systems database and can be assigned different levels of access to a database. By default, when a user connects to an Access database, he or she is automatically assigned the admin user, who by default has a blank password.

The security database information is stored in a workgroup security (MDW) file. The workgroup file included at installation is called system.mdw. This file contains user and group security information. You can change or create new security files using a Microsoft Access Workgroup Administrator file. If you are connecting to an Access database that was secured locally, you do not need to specify what MDW file to use. Figure 13-8 shows a dialog box from the Workgroup Administrator application.

Figure 13-8: Microsoft Access Workgroup Administrator

If you have secured a database and want to provide remote access to it, you must make the .mdw file available to the users and specify it in the connection string. This is identified with the Jet OLEDB:System Database keyword:

'open a Access MDB file using a different system.mdw file specifying 'a user name and password objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\odindata" & _ " system.mdw:data.mdb;Jet OLEDB:System Database=\odindatasystem.mdw", _ "freds", "sderf"

An alternative is to encrypt the script file. The Microsoft Script Encoder provides a method of securing script files so they cannot be read. The Script Encoder encrypts the file and makes it appear unreadable to a user.

To encrypt a script, download and install the Script Encoder from http://msdn.microsoft.com/scripting/vbscript/download/x86/sce10en.exe. The Script Encoder is a command-line application and no Windows user interface is provided.

The syntax of the Script Encoder is as follows:

screnc source destination

The source parameter is the source file you are encoding, while the destination parameter is the name of the encrypted file.

Example: screnc swpwd.vbs swpwd.vbe

The file extensions for the encrypted files are .vbe and .jse for VBScript and JScript, respectively. Once the files are encrypted, they are executed in the same way as normal script files-from Explorer or the command prompt using cscript.exe. No special command is required to execute them. Another reason to encode scripts is to protect intellectual property-you might not want the contents of your scripts to be read.

You do not need to install the Microsoft Script Encoder on the machine you are running the encrypted file on. You need at least version 5.0 of the JScript or VBScript scripting engine.

If you are distributing scripts that you do not want read for professional reasons, the Script Encoder provides a means of protecting the scripts.

The Script Encoder has no built-in method of reverse encrypting.

See Also

For more information, read the MSDN Library article "Microsoft Jet 4.0 OLE DB Properties Reference" (http://msdn.microsoft.com/library/en-us/dndao/html/daotoadoupdate_topic14.asp).

Compacting Access Databases

Problem

You want to compact an Access database.

Solution

You can use the Jet Replication Objects (JRO) object that is included with the ADO installation to compact a database. The JRO object exposes a JetEngine object with which you can compact a database using the CompactDatabase method:

'compact.vbs 'compacts a database Dim objJRO, objFSO,strSource, strTemp strSource = "D: est.mdb" strTemp = "D: emp.mdb" Set objFSO = CreateObject("Scripting.FileSystemObject") 'check if temporary file exists from previous operation, if so delete it If objFSO.FileExists(strTemp) Then objFSO.DeleteFile strTemp 'create Jet Replication Object.. Set objJRO = CreateObject("JRO.JetEngine") On Error Resume Next 'compact data objJRO.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & strSource & ";Jet OLEDB:Engine Type=4", _ "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strTemp & - ";Jet OLEDB:Engine Type=4" 'check if error occurred. . . If Not Err Then 'double check the file was compacted If objFSO.FileExists(strTemp) Then 'copy compacted temporary file to original objFSO.CopyFile strTemp, strSource, True objFSO.DeleteFile strTemp End If Else End If

Discussion

Space used by deleted Access data is not freed up, so an Access database can grow quickly in size. This has the obvious disadvantage of using excess disk space, but it also increases the chances of database corruption.

The JRO object is primarily used for managing Access database replicas, but the JetEngine object exposed through this object exposes the useful CompactDatabase object. The syntax is as follows:

objJRO.CompactDatabase strSource, strDestination

The database being compacted is represented by the strSource parameter. The compacted file is copied to the strDestination table. Compacting a database does not result in the source database file automatically being compacted-the temporary strDestination database must be renamed or copied to the original filename.

Because the routine requires a temporary file, you need at most (worst-case scenario) twice the disk space as the original file. Whenever an Access table is opened, the space that would be freed by a compact procedure is calculated. This information can be retrieved through the Connection object's Jet OLEDB:Compact Reclaimed Space Amount property:

Set objConn = CreateObject("ADODB.Connection") 'open an Access database objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:utl.mdb " Wscript.Echo "Space that would be freed up after compact:" & _ objConn.Properties("Jet OLEDB:Compact Reclaimed Space Amount")

The value returned is the freed space in bytes. This number is an approximation, but it is usually accurate within a few thousand bytes.

The source and destination files specified through the strSource and strDestination parameters are OLE DB connection strings (see Solution 13.3 for more information).

The minimum requirement for the connection strings is the Data Source property, which specifies the path to the files. If the Provider is not specified, it assumes the MS Jet 4.0 provider.

If your source database implements any security features, such as user IDs and passwords, you must set the additional connection string properties listed in Table 13-6.

The User ID, Password, and Jet OLEDB:System Database properties only apply to the source database. The destination database inherits the security settings of the original. You can set or change the database password for the destination database using the Jet OLEDB:Database Password property.

The following snippet compacts a database, specifies a different system.mdw security file, and uses the admin user ID:

objJRO.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=d: est.mdb" & _ & ";Jet OLEDB:System Database=d:system.mdw;User "& _ & "ID=admin;Password=admin", "Provider=Microsoft.Jet.OLEDB.4.0;" & _ & "Data Source=d: emp.mdb"

When you compact a database, the destination database is converted to the database engine version that is distributed with the ADO release, which for ADO 2.5 is Jet 4.0, which is the underlying database engine for Access 2000.

So if the source database was created by an older Access/Jet engine version, and you want to keep it that way, you need to specify the Jet OLEDB:Engine Type property in the destination or source connection string. Table 13-7 lists valid Jet OLEDB:Engine Type values and the associated Jet engine version.

You cannot specify a Jet engine version less than the version of the current source database, so it cannot be used to convert to an older version.

An Access database cannot be compacted when other users have it open. It can be difficult to determine who is currently accessing an Access database. Access creates an LDB file that tracks the current access to the database, but this can be tricky to read.

The Jet OLE DB provider exposes properties that can be read through the Properties collection available through a number of ADO objects, such as Connection and Recordset. However, some provider-specific information is not accessible through these collections, such as the JET_SCHEMA_USERROSTER property, which contains a list of connected users.

This information is accessible through the Connection object's OpenSchema method. OpenSchema is a general method to enumerate information about a database provider and its underlying structure. It allows database information to be enumerated, but it cannot be used to modify the database structure.

OpenSchema requires a QueryType parameter and can take optional Criteria and SchemaID parameters. QueryType determines what provider information is to be returned, Criteria provides constraints for the QueryType, and SchemaID is a unique GUID identifier for provider properties that are not accessible through the OLE DB provider.

OpenSchema returns a RecordSet containing provider information related to the query specified. The Jet database engine tracks the current active users, and this information can be returned by querying the Jet database engine-specific property using OpenSchema:

'list all connected users to an Access database Const JET_SCHEMA_USERROSTER = _ "{947bb102-5d43-11d1-bdbf-00c04fb92675}" Const adSchemaProviderSpecific = -1 Dim objConn,objRst,objField, nValue Set objRst = CreateObject("ADODB.Recordset") Set objConn = CreateObject("ADODB.Connection") objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:data.mdb " 'create a RecordSet using OpenSchema, returning the connected users. 'OpenSchema queries the JET specfic property JET_SCHEMA_USERROSTER, 'which returns a list of connected users Set objRst = objConn.OpenSchema(adSchemaProviderSpecific, , _ JET_SCHEMA_USERROSTER) Do While Not objRst.EOF Wscript.Echo objRst("COMPUTER_NAME") & ""& objRst("LOGIN_NAME") objRst.MoveNext Loop objRst.Close objConn.Close

The preceding routine will always return at least one connected user, which is whoever is running the script, because that opens the database. Querying the JET_SCHEMA_USERROSTER property returns a RecordSet containing all connected users. The RecordSet contains a COMPUTER_NAME field, which identifies the connected computer; LOGIN_NAME, which is the Access user ID; and CONNECTED, which is a Boolean value that if True indicates the user has a lock on the file.

See Also

For more information, read "Compact Microsoft Access Database via ADO" (http://support.microsoft.com/support/kb/articles/Q230/5/01.ASP).

Querying a Table

Problem

You want to return all records from a table.

Solution

You can create an ADO Connection object and open the data source to query. Execute a query against the open connection and iterate through the returned results:

Discussion

The Connection object provides the Execute method for executing requests against the data provider In the case of relational database providers, this is in the form of SQL statements. While the Execute method is mostly used for updating, deleting, and adding data (as you will see later on), it can also be used to return data from a provider. Its syntax is as follows:

Set objRecordset = connection.Execute (CommandText, [RecordsAffected], [Options])

The Execute method's arguments are listed in Table 13-8.

The recordset created using the Execute method is limited in the operations that can be performed on it. It is a read-only, forward-scrolling cursor. With this type of recordset, you can only start at the beginning of the data and move forward. You cannot move backward to previous records. The advantage of this type of recordset is that it's the "cheapest" in terms of resources and provides the best performance. Table 13-9 lists the possible values.

The most common way of processing a recordset is looping through and processing each record encountered until the end of the recordset is reached. Navigation of a recordset is provided by a number of Move methods, which are shown in Table 13-10. Not all Recordset types support all navigation methods. For example, the read-only, forward-scrolling cursor created by the Connection object's Execute method does not allow for the MovePrevious method. The general syntax of the Move methods is as follows:

recordset.MoveFirst | MoveLast | MoveNext | MovePrevious

To determine the current position in a recordset, you can use the BOF (beginning of file) and EOF (end of file) properties. These properties return True if the current position in the recordset is at the beginning or the end, respectively. If the Recordset is empty (i.e., it contains no records), both BOF and EOF are set to True.

The end of file occurs when the current record is the last record and the MoveNext method is invoked, or there are no records in the recordset. (The end of file is not the last record in the Recordset.) If you invoke the MoveNext method when at the end of the file, an error will occur.

The beginning of file is similar to the end of file: It is reached when the current record is the first record and the MovePrevious method is invoked. The beginning of the file is not the first record in the recordset. If you invoke the MovePrevious method when at the beginning of file, an error will occur.

Recordset objects contain a Fields collection that contains references to each field in the recordset. You can reference field values by either specifying the field name or number. For example:

Wscript.Echo objRecordSet.Fields("Field Name").Value 'any field name 'reference the field by index - faster but not very 'practical to use. Wscript.Echo objRecordSet.Fields(1).Value

The Fields collection is the default member property of the recordset, so you can omit a specific reference to the Fields collection when referencing a field. The following code snippet is equivalent to the previous statements:

Wscript.Echo objRecordSet("Field Name") Wscript.Echo objRecordSet(1)

The Solution script uses the Northwind.mdb sample database that's included with Access. It's implemented using a WSF file to take advantage of the WSF file to reference object type libraries-in this case, ADODB.Recordset. This saves the redeclaration of constants used by the ADO functions.

See Also

For more information, read the MSDN Library articles "Using the Connection Object" (http://msdn.microsoft.com/library/en-us/ado270/htm/mdconusingconnection.asp) and "MoveFirst, MoveLast, MoveNext, and MovePrevious Methods" (http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthmovefirst.asp).

Opening a Table for Writing

Problem

You want to open a table for reading and writing.

Solution

The following script creates a Recordset from the Northwind sample database that can be read and written:

Dim objRst Const adOpenDynamic = 2 Const adLockPessimistic = 2 Const adCmdText = 1 Set objRst = CreateObject("ADODB.Recordset") 'get all products objRst.Open "Select UnitPrice From Products", _ "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=d:data wind orthwind.mdb", _ adOpenDynamic, adLockPessimistic, adCmdText 'loop through and update all product prices by 2% Do While Not objRst.EOF objRst("UnitPrice") = objRst("UnitPrice") * 1.02 objRst.MoveNext Loop objRst.Close

Discussion

The Connection object's Execute method provides a quick way of creating a recordset. But the recordset it creates is limited in scope, because it only allows forward movement and doesn't allow additions, updates, or deletions. The Recordset object's Open method provides the ability to create recordsets with more functionality. Its syntax is as follows:

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

Table 13-11 lists the optional parameters available for the Open method. Each of these parameters, except for Options, can be set as an individual property of a Recordset object before invoking the Open method, as illustrated in the Solution.

Recordsets support four different cursor types: forward-only, keyset, static, and dynamic. Table 13-12 describes different cursors and their capabilities.

Recordset cursor types and their pros and cons are listed in Table 13-13.

If you want to add or modify data, you need to specify what type of record locking is to be used when modifying the data. Record locking is required to prevent problems when more than one user is working with the same data. For example, you may want to prevent any user editing the same data that you are working on to prevent any conflicts. You might have just modified a record and then updated it, only to have another user who was editing it simultaneously wipe out your modifications.

Table 13-14 lists the recordset lock types available with the Recordset object's Open method. If you want to guarantee that nobody else modifies the record while you are working on it, use pessimistic locking. Otherwise, use optimistic locking. And if you don't want to do any modifications at all, make it read-only.

Recordsets can be created independently of a Connection object by passing the connection string as a parameter to the Open method, as in the following code fragment:

Const adOpenDynamic =2 Const adLockPessimistic = 2 Const adCmdText = 1 Set objRst = CreateObject("ADODB.Recordset") 'create a recordset from a SQL statement against the Northwind database. objRst.Open "Select * From Customers", _ "Provider=Microsoft.Jet.OLEDB.3.51; Data Source=C:data orthwind.mdb", _ adOpenDynamic, adLockPessimistic, adCmdText

It is also possible to open a recordset by using an already established connection, as the following code fragment illustrates:

Const adOpenDynamic =2 Const adLockPessimistic = 2 Const adCmdTable = 2 objConn.Open "Provider=SQLOLEDB;User ID=sa;Data Source=Odin" objConn.DefaultDatabase = "pubs" objRst.Open "publishers", objConn, adOpenDynamic, adLockPessimistic, _ adCmdTable

See Also

For more information, read the MSDN Library article "Using the Connection Object" (http://msdn.microsoft.com/library/en-us/ado270/htm/mdconusingconnection.asp).

Manipulating a Text File

Problem

You want to open a text file and perform calculations on the values.

Solution

The following example opens a comma-delimited text file with the following format and totals the SalesTotal column:

Products,SalesTotal "Dairy",4543.45 "Meat",2344.34 "Produce",1347.54

The ODBC Text file driver is used to access ASCII delimited text file tables:

Dim objConn, objRst, nTotal Set objConn = CreateObject("ADODB.Connection") 'open a connection to a text file using a DSN objConn.Open "SalesCSV" 'select all data from the text file Orders data.txt Set objRst = objConn.Execute("Select * From [Orders data.txt]") 'loop through and total the contents While Not objRst.EOF nTotal = nTotal + objRst("SalesTotal").Value objRst.MoveNext Wend Wscript.Echo "Total sales is:" & nTotal 'close the connection and recordset object objRst.Close objConn.Close

Discussion

You can find the Microsoft Text driver used in this Solution in the Microsoft ODBC installation. The driver provides read-only access to text files; you cannot modify the data in the data source. To configure the driver, specify in the data source definition what directory the text files will reside in. You can open as many text files from this directory as required.

The Text driver by default assumes that the data file is in comma-delimited format and that the first row contains the field names. If you need to import different text file formats, such as tab-delimited text, or you need more exact descriptions of the data types in the file, you require a schema.ini file. A schema.ini file is a text file that describes the layout of a delimited text file. A single schema.ini file can contain layout details on as many files as you want. The schema.ini file resides in the same directory as the data files. An entry is made in the schema file for each text file you need to describe. For example, the following schema.ini file contains the layout of two text files: datatab.txt is tab delimited and datafixed.txt is a fixed-length text file.

[DATATAB.TXT] ColNameHeader = False Format = TabDelimited CharacterSet = ANSI Col1=ProductName char width 40 Col2=UnitPrice currency Col3=SupplierId long Col4=QuantityPerUnit char width 20 Col5=Discontinued bit [DATAFIXED.TXT] ColNameHeader = False Format = FixedLength CharacterSet = ANSI Col1=ProductName char width 40 Col2=UnitPrice currency width 10 Col3=SupplierId long width 10 Col4=QuantityPerUnit char width 20

For each entry, you specify the file type (TabDelimited, CSVDelimited, Delimited(*), or FixedLength) and define each column. For each column, you define the data type and optionally its width. The width is required for FixedLength text files where the driver needs to know the position of the data.

See Also

For more information, read "Use RDO and ODBC Text Driver to Open a Delimited Text" (http://support.microsoft.com/support/kb/articles/Q187/6/70.ASP?LN=EN-US&SD=gn&FR=0&qry=Q187670&rnk=1&src=DHCS_MSPSS_gn_SRCH&SPR=WIN2000).

Accessing Excel Data

Problem

You want to connect to an Excel data file.

Solution

The following Solution is executed at logon. It connects to an Excel spreadsheet using an ODBC Excel driver and checks if the user has logged on for the current day. If not, it updates the spreadsheet with the current logon time. This spreadsheet can be used to determine the initial logon time of the user.

Dim objConn, objRst, objNetwork, strDay 'get the current name of day e.g. Monday strDay = WeekDayName(Weekday(Date)) Set objNetwork = CreateObject("WScript.Network") Set objConn = CreateObject("ADODB.Connection") 'open a connection using the ExcelUserData file DSN objConn.Open "FileDSN=\ThorFileDSN$ExcelUserData.dsn" 'get the record for todays date for the current user Set objRst = objConn.Execute("Select "& strDay & _ "From UserList Where UserID='" & objNetwork.UserName & "'") 'check if the time column for the current day. If it is empty, then 'update the time. If IsNull(objRst(strDay).Value) Then objConn.Execute "UPDATE UserList Set "& strDay & "='" & Time & _ "' Where UserID='" & objNetwork.UserName & "'" End If objRst.Close objConn.Close

Discussion

The Excel data ODBC driver provides access to Microsoft Excel spreadsheets. The data is defined within the spreadsheets as named ranges, so you can have as many data areas as required defined in named ranges.

To create a spreadsheet named range for use as a data source, follow these steps:

1. In an Excel spreadsheet, create the table structure you want to use. The top row in the structure should contain the field names you want to use. Figure 13-9 shows the spreadsheet layout used by the Solution script.

   Figure 13-9: Excel data source

2. Select the range.
3. Choose Insert > Name >Define. The Define Name dialog box appears, as shown in Figure 13-10.

   Figure 13-10: The Define Name dialog box

4. Enter a name for the range and click the OK button.

The range name is the source for your queries. The data areas can have mixed table structures in the spreadsheet. Spreadsheet ranges in the format worksheetname$range can also be used to identify data, but only for spreadsheet files created with Excel 5.0 and later.

All data within the ranges except formulas can be modified. Data can be added to a range, but not deleted. If data is added to a named range, the named range will automatically expand accordingly to take advantage of the new row.

By default, the driver assumes the first row of a range contains headers, and these headers can be used when referencing fields. The following code fragment adds a row of data to the spreadsheet range LogData:

Const adOpenForwardOnly = 0 Const adLockOptimistic = 3 Dim objConn,objRst Set objConn = CreateObject("ADODB.Connection") Set objRst = CreateObject("ADODB.Recordset") 'open the datasource objConn.Open "FileDSN=ExcelData.dsn" 'open the named range LogData objRst.Open "LogData", objConn, adOpenForwardOnly, adLockOptimistic 'add a new row to the range objRst.AddNew objRst("LogTime") = Now objRst("UserID") = "Fred Smith" objRst("Description") = "An event has occurred" 'update and close data source objRst.Update objRst.Close objConn.Close

See Also

For more information, read "Use ADO with Excel Data from Visual Basic or VBA" (http://support.microsoft.com/support/kb/articles/Q257/8/19.ASP).

Querying the Index Server

Problem

You want to search for files using the IIS Index Server engine.

Solution

You can use the Index server ODBC driver to execute queries against the IIS Index server:

Dim objRst, strQuery, strContents, strKeyWord, strCriteria Set objRst = CreateObject("ADODB.Recordset") 'check if there is either one or two parameters passed If WScript.Arguments.Count = 0 Or WScript.Arguments.Count >2 Then ShowUsage Wscript.Quit End If 'if only one argument has been passed, then set the search type to 'contents If WScript.Arguments.Count = 1 Then strKeyWord = "Contents" strCriteria = WScript.Arguments(0) Else strKeyWord = WScript.Arguments(0) strCriteria = WScript.Arguments(1) End If 'build the query for the search engine. Replace all single quotes in the 'command line parameter with double quotes since the Index server wants 'double quotes. strQuery = "SELECT Path, DocLastSavedTm FROM Scope() WHERE CONTAINS("_ & strKeyWord & ",'" & Replace(strCriteria, "'", chr(34)) & "')>0" 'build the query for the search engine objRst.Open strQuery, "PROVIDER=MSIDXS" 'display each item While Not objRst.EOF Wscript.StdOut.WriteLine objRst(0) objRst.MoveNext Wend objRst.Close objRst.ActiveConnection.Close Sub ShowUsage WScript.Echo "Idxsrv executes a query against Microsoft Index Server" _ & vbCrLf & "Syntax:" & vbCrLf & _ "idxsrv [Type] IndexQuery" & vbCrLf & _ "Type (optional) Server propery to search against. Default is Contents" & _ vbCrLf & "IndexQuery query to execute" End Sub

Discussion

The Solution uses the IIS Index Service provider to execute queries against a Microsoft Index Server. The Microsoft Index Server provides indexing capabilities for many types of document formats, such as text, HTML, and Microsoft Office. Directories that are indexed are configured using the IIS Internet Service Manager interface. The directories you index do not have to be Web folders, so you can index document directories and use the Index Service provider to quickly query documents based on their content.

The Index Server provider allows a query to be executed against the indexed document database. The queries are constructed using a SQL query syntax. A limitation of the MSIDXS provider is that it can only be executed on the machine the Index Server resides on—it does not allow for remote querying.

The Solution is a command-line script that outputs the path of all documents that meet specified search criteria.

The following is the syntax for the command line:

idxsrv [Type] IndexQuery

The query Type is an optional parameter that specifies what Index Server property to search against. The default is Contents, which indicates the body of the documents will be searched. The following example returns all Microsoft Office documents where the document's author property has been set to Fred Smith:

cscript idxsrv.vbs DocAuthor "'Fred Smith'"

IndexQuery is the criteria executed against the provider. This criteria is passed as a Boolean expression.

The following sample returns all documents that contain the words "data" and "office":

cscript idxsrv.vbs "data AND office"

If any of the criteria contains spaces, surround them with single quotes:

cscript idxsrv.vbs "'sales figures' OR 'northern region'"

The results are output to the console standard output. The results can be piped to other applications that can process the standard input stream. Because it uses the standard input/output, WSH version 2.0 or later is required and the script must be run using the command-line interpreter cscript.exe.

See Also

For more details on the Index provider, read "Idxadovb.exe: Using Index Server OLE DB Provider and ADO in Visual Basic" (http://support.microsoft.com/support/kb/articles/Q179/3/26.ASP).

Adding Data

Problem

You want to add a new record to a database.

Solution

There are two ways of adding data. Each has advantages and disadvantages.

• You can use the Recordset object's AddNew method. The advantage of AddNew is that it produces relatively simple code. The disadvantage is a performance hit. This performance penalty is provider dependent, and it is negligible for a few additions, but if you're attempting a large number of additions, it will become noticeable.
• The alternative is executing the appropriate command statement against a data source using the Command object's Execute method. In the case of a relational database source, this would be via the SQL INSERT statement. The advantage is better performance over the AddNew method, while its disadvantage is that more code is required to build the appropriate statements.

Also note that not all providers allow for the addition of data:

Const adCmdTable = 2 Const adLockPessimistic = 2 Const adOpenForwardOnly = 1 Set objRst = CreateObject("ADODB.Recordset") 'open the data source objRst.Open "Customers", _ "Provider=Microsoft.Jet.OLEDB.3.51; Data Source=C:data orthwind.mdb", _ adOpenForwardOnly, adLockPessimistic, adCmdTable objRst.AddNew objRst("CompanyName") = "Fred's Food Company" objRst("CustomerID") = "MNOPQ" objRst.Update objRst.Close

Discussion

The AddNew method provides a straightforward way of adding new records to a data source. To use the AddNew method, follow these steps:

1. Create a Recordset object that is not read-only (its LockType property is not adLockReadOnly) and invoke the AddNew method.
2. Assign the new values to the record.
3. Invoke the Recordset object's Update method.

Note that if you execute any Move methods after adding new items, the new data is automatically updated. It is a good programming habit to explicitly invoke the Update method.

If you are using ADO version 1.5 or greater, you can specify the fields and values you want to add via optional parameter arrays. The syntax of the AddNew method is as follows:

objRst.AddNew Fields, Values

Table 13-15 lists the AddNew method's arguments.

For example, the following code fragment makes use of arrays to add information to four fields of a new record of a Customers table:

'adds a new value to the Customers table objRst.AddNew(_ Array("CustomerID"," CompanyName"," ContactName"," ContactTitle"), _ Array("ABCDE", "Fred's Food", "Fred", "President")

The alternative to the AddNew method is to use the provider's native method for inserting data, which in most cases is the SQL INSERT INTO statement. In the following example, a record is added to the SQL Server's Pubs sample database using a SQL statement:

Const adCmdText = 1 'create a connection objConn.Open "Provider=SQLOLEDB;User ID=sa;Initial Catalog=pubs;Data Source=Odin" objConn.Execute "INSERT INTO authors (au_id, au_lname, au_fname, phone, contract) _ VALUES ('323-43-4333', 'Smith', 'Fred','604-555-1234',1)", , adCmdText objConn.Close

The advantage to using the SQL statement over the AddNew method is performance. The native statement is generally faster, so if you need to add a lot of data to a database, you should use it rather than AddNew.

The Solution code uses the Access Northwind sample database to add a new record.

See Also

For more information, read "AddNew Method" (http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthaddnew.asp).

Updating Data

Problem

You want to update records.

Solution

There are a couple of different ways to update data. The easiest way is to create a Recordset object and open a data source with any lock type except read-only. Once the data source is opened, you can proceed to modify the data. Invoke the Update method after modifying each record. The other option is to build a SQL query statement and execute it with the Command object's Execute method.

In the following code snippet the price of all nondiscontinued items in the Products table is increased by 7 percent:

Const adCmdTable = 2 Const adLockOptimistic = 3 Const adOpenForwardOnly = 1 Dim objConn, objRst Set objConn = CreateObject("ADODB.Connection") objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=e:Nwind.mdb" Set objRst = CreateObject("ADODB.Recordset") 'open the recordset objRst.Open "Products", objConn, _ adOpenForwardOnly, adLockOptimistic, adCmdTable 'loop through all records Do While Not objRst.EOF 'only update if item is not discontinued If Not objRst("Discontinued") Then objRst("UnitPrice") = objRst("UnitPrice") * 1.07 objRst.Update End If objRst.MoveNext Loop

Discussion

To update data in a recordset, create a Recordset object that is not read-only (its LockType property is not adLockReadOnly). That's it. As long as you've opened the recordset as not read-only, and no one else has locked the record, you can modify the underlying data.

However, the data provider must allow for the modification of data. Some providers, such as the Index server and Active Directory OLE DB interfaces, do not allow delete/update/create operations. Even if the provider allows for updates, such as SQL Server or Oracle, the underlying data source might implement security that prevents you from updating it.

After you've updated a value, you can invoke the Update method to save the changes. If you do not invoke the Update method and move from the current record, any changes made to the record are automatically saved.

If you want to cancel any changes you have made to the record, you can invoke the CancelUpdate method, which undoes any changes made to the current record. CancelUpdate does not work after you've invoked the Update method or moved to another record.

You can also update data using the provider's native method for updating data, which in most cases is the SQL UPDATE statement. Apart from being faster than the Update method, you can easily modify large quantities of data with a single statement. The following example updates the prices of all business titles from the Pubs database by 5 percent and shows the number of records affected:

Const adCmdText = 1 Set objConn = CreateObject("ADODB.Connection") objConn.Open "Provider=SQLOLEDB;User ID=sa;Initial Catalog=pubs;Data Source=Odin" objConn.Execute _ "Update titles Set price = price * 1.05 where type='business'" _ , nCount, adCmdText Wscript.Echo nCount & "records were updated" objConn.Close

The CancelUpdate method does not work with the SQL Update statement. You can, however, use transaction processing to "roll back" any update transaction if your provider supports it. See Solution 13.23 for a transaction sample.

See Also

For more information, read the MSDN Library article "Update Method" (http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthupdate.asp).

Accessing HTML Data

Problem

You want to open a remote HTML table as a recordset.

Solution

The following code sample opens an HTML table stored in the following HTML listing and compares each product with the items stored in the Northwind sample database Products table. If the product does not exist in the local table, it is added, and if it does exist, the price is updated. The script uses the HTML ODBC driver to open the table.

The following table is the source HTML table used in the script:

Product List

Discussion

The Solution script uses an HTML ODBC driver to open an HTML file for reading. The data in the HTML file is stored in a table format. This table is constructed using the standard HTML table definition tags: and .

You can have multiple data tables in the HTML source. The tables can be identified in a number of ways. An HTML caption can be used as the table source identifier. If there is no table caption and only one table in the file, the filename is used as the data source identifier. If there are multiple tables in the file and no captions, the tables are identified using the sequential table number (e.g., table1, table2, and so on).

The HTML driver is similar to the Text and Excel drivers in that it depends upon the Access Jet database engine. In order to connect to an HTML table, you must create a connection string using the Jet provider and specify an Extended Properties parameter. The Extended Properties keyword is used to specify additional extended properties of a given provider.

The HTML import driver has an HDR parameter that specifies if the table(s) being accessed has headers or not. If it is set to Yes, the first row of HTML will be read as field names. By default, this value is No and it assumes that the first row of HTML does not include field names.

objHTMLRst.Open "Atable", _ "Provider=Microsoft.Jet.OLEDB.4.0" & _ ";Data Source=http://www.acme.com/data/products.htm;" & _ "Extended Properties='HTML Import;HDR=YES'", _ adOpenForwardOnly, adLockReadOnly

The Solution also uses the Find method, which provides a search ability with an open Recordset object. The Find method provides the ability to search a recordset in any direction without having to re-execute a query. Its syntax is as follows:

objRst.Find (criteria, [skiprows,] [searchdirection] [, start])

Table 13-16 lists the Find method's arguments.

The comparison operator for the criteria string can be any mathematical comparison operator such as =, <, >, <=, >=, and <>. You can use a Like operator to perform comparisons using the * wildcard operator.

The Find method starts the search from the current record. If the item you are searching for is not found, the current record is set to end of file (EOF). If you attempt to find a record that occurs before the current record, the record will not be found. If you are doing repeated Find operations, you must move to the beginning of the table before each Find operation is executed.

String values must be surrounded by single quote (') delimiters, while dates must use the number sign (#) delimiter.

See Also

For more information, read the MSDN Library articles "Using the Connection Object" (http://msdn.microsoft.com/library/en-us/ado270/htm/mdconusingconnection.asp) and "Find Method" at (http://msdn.microsoft.com/library/en-us/his/thorref4_11ia.asp).

Deleting Data

Problem

You need to delete a record.

Solution

To delete a record from a table, simply invoke the Delete method on the record you want to delete:

Set objConn = CreateObject("ADODB.Connection") objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:dataAccessSamplesNorthwind.mdb" Set objRst = CreateObject("ADODB.Recordset") 'open the Products table and delete a record objRst.Open "Products", objConn, adOpenForwardOnly, adLockOptimistic objRst.Delete objRst.Close

The alternative to the Delete method is to use the provider's native method, which is usually the SQL DELETE statement. The following example attempts to delete all business titles from the Titles table in the Pubs database:

Const adCmdText = 1 Set objConn = CreateObject("ADODB.Connection") objConn.Open "Provider=SQLOLEDB;User ID=sa;Initial Catalog=pubs;Data Source=Odin" objConn.Execute "Delete From titles where type='business'" _ , nCount, adCmdText Wscript.Echo nCount & "records were deleted" objConn.Close

Discussion

Records can be deleted by either calling the Delete method on the current record of an open Recordset or executing a SQL DELETE statement against the data source.

See Also

For more information, read the MSDN Library article "Delete Method" (http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthrstdelete.asp).

Accessing Internet Resources

Problem

You want to download an image from a Web site.

Solution

You can use the Stream object to open and save the resource. The following script writes the contents of a remote Web file to a local file:

Const adSaveCreateOverWrite = 2 Const adModeRead = 1 Const adTypeBinary = 1 Dim objRec, objStream Set objStream = CreateObject("ADODB.Stream") objStream.Open "URL=http://www.microsoft.com/library/toolbar/images/mslogo.gif" _ , adModeRead 'set stream type to Binary and save file objStream.Type = adTypeBinary objStream.SaveToFile "e:datamslogo.gif", adSaveCreateOverWrite objStream.Close

Discussion

The Microsoft OLE DB Provider for Internet Publishing is an OLE DB provider that provides access to Web server file resources. It is included as part of Internet Explorer 5.0 and Office 2000 and later.

The OLE DB Provider for Internet Publishing requires the Web Distributed Authoring and Publishing (WebDAV) protocol to be enabled on the host server that is being accessed. The Internet Publishing provider can also use the FrontPage Web Extender Client (WEC), which allows Web authoring on older IIS Web servers that don't support WebDAV.

WebDAV is a standard protocol that allows clients to access resources on remote servers and perform administrative tasks, such as creating, copying, moving, and deleting files.

Because WebDAV is a standard, you are not dependent on Microsoft IIS servers to provide WebDAV access. Other popular Web servers such as Apache are capable of providing WebDAV services.

WebDAV is enabled on Microsoft IIS 5.0, which comes with Windows 2000.

The connection string requires that a valid URL path is specified as the source, together with an optional access mode, user ID, and password. Table 13-17 details the elements of a connection string for the Internet Publishing provider.

Once a connection has been made, you can iterate the records and perform deletion operations. Table 13-18 lists a number of provider field names that are available to Web objects.

Although ADO 2.0 and 2.1 can be used to enumerate and delete Web files, to take full advantage of WebDAV features you need ADO 2.5 or later. ADO 2.5 provides an additional object, the Record object, which allows for additional resource manipulation.

The RecordSet object can be used to return results in a fixed table format from a wide variety of sources, such as databases, text files, and Excel spreadsheet data. What the Recordset can't do is represent unstructured data. A Web site is an example of unstructured data, where there is no fixed format.

The Record object allows ADO to access "unstructured" data, such as a directory hierarchy from a Web server or file system, or a mailbox structure from an e-mail service. A Record object can represent an individual Web resource, such as a file, or a resource collection, which represents a directory under a Web server.

To access a Record object, use the Record object's Open method to reference the remote resource:

objRecord.Open Source, Connection [, nConnectMode] [,nCreateOptions]

The Open method requires at least a Source and Connection parameter. Source represents a file object within the directory specified by the Connection parameter. The Connection parameter can be either a connection string or an existing ADO Connection object that points to the Web resource you want to access.

The following two code snippets open the page http://www.acme.comdefault.htm:

'method one Set objRecord = CreateObject("ADODB.Record") objRecord.Open "default.htm", "URL=http://www.acme.com" 'method two, use existing Connection object Set objConn = CreateObject("ADODB.Connection") Set objRecord = CreateObject("ADODB.Record") 'open a connection to a Web server using Internet Publishing 'OLE DB provider objConn.Open "Provider=MSDAIPP.DSO;Data "& _ "Source=http://www.acme.com;Mode=Read|Write" objRecord.Open "default.htm", objConnection

The nConnectMode parameter identifies how the file is to be accessed, such as adModeRead (1), adModewrite (2), and adModeReadWrite (3).

If you want to open a directory, pass an empty string as the Source parameter to the Open method:

Set objRecord = CreateObject("ADODB.Record") 'open the data directory under the www.acme.com site objRecord.Open "", "URL=http://www.acme.com/data"

If you attempt to open a nonexistent resource, an error will occur.

The Open method can also be used to create a file or directory. By default, when you open a URL the URL assumes you are attempting to access an existing directory or file. Use the nCreateOptions parameter to indicate you want to create a new file or directory.

If you specify adCreateCollection (8192) as the nCreateOptions parameter, a new directory will be created, and adCreateNonCollection (0) will create a new file. Adding adCreateOverwrite (67108864) to either of these values will overwrite the existing file or directory if it already exists.

'create a new file Const adCreateNonCollection =0 Const adCreateOverwrite = &H4000000 Set objRecord = CreateObject("ADODB.Record") 'create a new data.txt file, overwriting the any file with the same name objRecord.Open "data.txt", "URL=http://www.acme.com/" _ , , adCreateNonCollection + adCreateOverwrite

Once you have a reference to a Web resource, you can perform a number of operations on it, such as deleting it. To delete an object, simply call the Delete method:

Set objRecord = CreateObject("ADODB.Record") 'open the data directory under the www.acme.com site objRecord.Open "", "URL=http://www.acme.com/data"

Deleting resources cannot be undone. If you delete a directory, all files and directories below it will be deleted.

The following script uses the Internet Publishing provider to delete any files that are older than 30 days on a Web server:

Dim objRst, objConn Set objConn = CreateObject("ADODB.Connection") Set objRst = CreateObject("ADODB.Recordset") 'open a connection to a Web server using Internet Publishing 'OLE DB provider objConn.Open "Provider=MSDAIPP.DSO;Data "& _ "Source=http://odin/data;Mode=Read|Write;" & _ "User ID=Administrator;Password=we56oi90" 'list all files from the folder objRst.Open "*", objConn 'loop through all files While Not objRst.EOF 'check if the size of file is a numeric value - indicates 'a file If Not IsNull(objRst("RESOURCE_STREAMSIZE")) Then 'checks if file is older than 30 days and if it is a htm file If DateDiff("d", objRst("RESOURCE_LASTWRITETIME"), Date) < 30 _ And Right(objRst("RESOURCE_DISPLAYNAME"), 3) = "htm" Then objRst.Delete End If End If objRst.MoveNext Wend objRst.Close objConn.Close

Web resources can be copied or moved. The Record object exposes the CopyRecord and MoveRecord methods, which can be used to copy and move resources. The syntax is as follows:

objRecord.CopyRecord | MoveRecord strSource, strDest,[ strUser,][ strPassword,] [nOptions]

The strSource and strDest parameters represent the URL to the source and the destination of the object you want to copy or move. If the Record object is a Web directory, the URL for strSource can represent the relative path to an object in this directory.

'create ADO record object Set objRecord = CreateObject("ADODB.Record") 'open a Record object to the root of Acme.com objRecord.Open "", "URL=http://www.acme.com" 'copy data.htm from www.acme.com root to backup.htm objRecord.CopyRecord "data.htm", "URL=http://www.acme.com/backup.htm" 'move history.htm from www.acme.com root to data directory objRecord.MoveRecord "history.htm", "http://www.acme.com/data/history.htm" 'move data.htm from www.acme.com root to dataold.htm in same directory, 'this is the same as a rename objRecord.MoveRecord "data.htm", " dataold.htm"

If the strSource parameter is blank, the operation applies to the Record object, either copying or moving it to the specified strDestination:

'create ADO record object Set objRecord = CreateObject("ADODB.Record") 'open a Record object to the root of Acme.com objRecord.Open "data.htm", "URL=http://www.acme.com" 'copy data.htm from www.acme.com root to backup.htm objRecord.CopyRecord "", "URL=http://www.acme.com/backup.htm"

The strUser and strPassword parameters represent an optional user name and password for the destination location, if the security requirement is different from the source.

If a file or directory exists with the same name of the destination file you are attempting to copy or move, an error will occur. The optional nCopyOptions parameter can take an adCopyOverWrite value that overwrites the destination if it already exists:

Const adCopyOverWrite = 1 'create ADO record object Set objRecord = CreateObject("ADODB.Record") 'open a Record object for the file data.htm in the root of Acme.com objRecord.Open "", "URL=http://www.acme.com" 'copy data.htm from www.acme.com root to backup.htm and overwrite if 'file already exists objRecord.CopyRecord "data.htm", "backup.htm", , , adCopyOverWrite

Where a Record object is different from a RecordSet is that it can represent an individual item, such as a file, or a collection of items, such as a directory. The Record object's RecordTypeEnum property identifies the type of Record object. Table 13-19 lists RecordTypeEnum's values.

If the Record object is a collection you may want to enumerate the contents. Use the GetChildren method, which returns a RecordSet containing the contents of the container. In the case of a Web server directory, this would contain a list of all files and directories the next level under the directory represented by the Record object.

Perhaps one of the most interesting new features of ADO 2.5 is the introduction of the Stream object. Streams provide access to the contents of Record objects, which in the case of the Internet provider are the files on the Web server.

To use streams, create a Stream object and invoke the Open method:

objStream.Open [strSource,][nConnectMode,] [nType]

The strSource parameter represents the path to the source, which is a URL to the file to access. If no strSource parameter is specified, any operations are stored in memory.

nConnectMode determines if the file is to be opened for read and/or write access. Valid values for the connect mode are adModeRead (1), adModewrite (2), and adModeReadWrite (3). Use a WSF file and the element to reference the type libraries for the Stream object.

You can pass an existing open Record object as the strSource parameter when opening a Stream, but if you do you must specify the nType parameter as adOpenStreamFromRecord (4).

If you are going to manipulate the Stream, you should set the object's Type property. This defaults to adTypeText (2), but if you are working with a binary type file, such as an image or executable, you should change it to adTypeBinary (1).

The Stream object exposes the LoadFromFile and SaveToFile methods, which allow easy transfer of files from a Web server to local storage.

LoadFromFile allows for local files to be loaded into a Stream object. The syntax is as follows:

objStream.LoadFromFile strPath

strPath represents the local path or UNC to the file you want to load into the Stream.

SaveToFile allows the contents of a Stream to be saved to a local file:

objStream.SaveToFile strPath [,nSaveOptions]

strPath represents the local path or UNC to the file you want to save the stream to. If the destination file you are attempting to save to already exists, an error will occur. Setting the nSaveOptions to adSaveCreateOverWrite (2) will overwrite the existing file with the contents of the Stream object.

The following code sample copies a local file to a Web server:

'copy a local file to a Web server Option Explicit Const adTypeBinary = 1 Const adCreateNonCollection = 0 Const adCreateOverwrite = &H4000000 Const adModeReadWrite = 3 Const adOpenStreamFromRecord=4 Dim objRst, objConn, objRecord, objRec, objStream Set objStream = CreateObject("ADODB.Stream") Set objRecord = CreateObject("ADODB.Record") 'create a new data.dat file, overwriting the any file with the same name objRecord.Open "data.zip", "URL=http://www.acme.com/" _ , adModeReadWrite, adCreateNonCollection + adCreateOverwrite 'open the Stream object using the objRecord object objStream.Open objRecord, adModeReadWrite, adOpenStreamFromRecord objStream.Type = adTypeBinary 'load local file in Stream object objStream.LoadFromFile "d:datadata.dat" objStream.Close objRecord.Close

Streams can be written to and read from in a similar fashion to the File Scripting Object.

Data can be read by using either the Read or ReadText method. Both methods can take an optional nChars parameter that returns only the number of bytes specified. If this parameter is omitted, all data from the stream is returned. The ReadText method can pass a special nChars, adReadLine, that will read a single line of text up to an end-of-line character sequence controlled by the LineSeperator property. By default, the LineSeperator property is carriage return/linefeed combination.

When you work with text it can be important to change the character set being used to read and write the files. By default, the character set type is set to Unicode, which is not appropriate for a lot of Web operations that only understand 8-bit ASCII text. To change the character set, set the charset property to the appropriate value. These values are determined by the entries under the HKEY_CLASSES_ROOTMIMEDatabaseCharset entry in the Windows registry.

To determine if the end of the stream has been reached, check the Stream's EOS property. If it is True, the end of the stream has been reached; otherwise, it is False.

The following code snippet reads a text file on a Web server and outputs the contents to the screen:

Const adReadLine = -2 Const adTypeText = 2 'read the contents of a Web file Set objStream = CreateObject("ADODB.Stream") 'open to log.txt file on www.acme.com site objStream.Open "URL=http://www.acme.com/log.txt" 'set type to text and character set to Ascii objStream.Type = adTypeText objStream.charset = "ascii" 'read contents of file and output Do While Not objStream.EOS 'read the next line of text strLine = objStream.ReadText(adReadLine) Wscript.Echo strLine Loop objStream.Close

To write to a Stream, use either the Write or WriteText method. The Write method requires a variant array of bytes to write and is used for writing binary data.

WriteText requires a string parameter that is the text that will be written to the Stream. It can also take an optional parameter that determines if an end-of-line character is added to the data. By default, no end-of-line character is added, but if the parameter is adWriteLine (1) it is.

You may want to append to the stream or start at the beginning of the stream. To change the location of where the data will be written to, set the Position property. The Position property returns the current location in the stream and determines where the next data will be written.

To append data, set the Position property to the Size property of the Stream object, which will set it at the end of the stream:

See Also

For more information, read the MSDN Library articles "Platforms Supported by the OLE DB Provider for Internet Publishing" (http://msdn.microsoft.com/library/en-us/ipubsdk/ipub01_0t42.asp), "Internet Publishing Scenario" (http://msdn.microsoft.com/library/en-us/ado270/htm/mdhowinternetpublishingscenario.asp), "Stream Object" (http://msdn.microsoft.com/library/en-us/ado270/htm/mdobjstream.asp), "Record Object" (http://msdn.microsoft.com/library/en-us/ado270/htm/mdobjrecord.asp), and "Microsoft OLE DB Provider for Internet Publishing" (http://msdn.microsoft.com/library/en-us/ado270/htm/mdrefproviderrosebud.asp).

Exporting Data

Problem

You want to be able to export a recordset to a CSV text file.

Solution

You can open a Recordset and use the Fields collection to enumerate all fields for each record, outputting the results to a text file:

Const adVarWChar = 202 Const adWchar = 130 Dim objConn, strDestinationFile Dim objRst Set objConn = CreateObject("ADODB.Connection") objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=d:dataaccessSamplesNorthwind.mdb;" Set objRst = objConn.Execute("Select * From Products") CreateCSVFile "d:output.txt", objRst, "," objRst.Close objConn.Close Sub CreateCSVFile(strDestinationFile, objRst, strDelimiter) Dim objField, strLine , objFileSystem, objTextFile 'create a file scripting object Set objFileSystem = CreateObject("Scripting.FileSystemObject") 'create the output file.. Set objTextFile = objFileSystem.CreateTextFile(strDestinationFile, True) 'loop through each record in the recordset Do While Not objRst.EOF strLine = "" 'loop through each field in the record, building the output string. For Each objField In objRst.Fields Select Case objField.Type Case adVarWChar, adWchar strLine = strLine & """" & objField.Value & """" & "," Case Else strLine = strLine & objField.Value & "," End Select Next 'write the line to the file objTextFile.WriteLine Left(strLine, Len(strLine) - 1) objRst.MoveNext Loop objTextFile.Close End Sub

Discussion

When a recordset is created it exposes a Fields collection. This collection contains all fields in the recordset.

The full syntax for referencing a field value by name is as follows:

Wscript.Echo objRst.Fields(strFieldName).Value

The following code displays the value of the Description field for the objRst recordset:

Wscript.Echo objRst.Fields("Description").Value

The Fields collection is the default property of a Recordset object, so you are not required to specify the Fields keyword in a statement:

Wscript.Echo objRst("Description").Value

The Field object's default property is the Value property, so it is not required when referencing a value. The following statement is the same as the previous statement:

Wscript.Echo objRst("Description")

Wscript.Echo and other methods used to display values such as MsgBox know how to display a value even if the Value property is not specified. This is because they can only display a nonobject value, so when the object is specified as a parameter its default property is referenced instead.

The Name property identifies the name of the field. These properties can be used to process all the fields in a recordset where the field names are not known or explicitly specified.

The following code snippet lists each field and its field type in a recordset:

Dim objField, objRst objRst.Open "Select CompanyName From Companies", "Northwind" 'display the name and type of each field For Each objField In objRst.Fields Wscript.Echo objField.Value, objField.Type Next objRst.Close Set objRst.Close

The CreateCSVFile subroutine in the Solution script accepts a recordset as a parameter. It loops through each Field and checks its Type property. If the Type property is a string data type, the output value is surrounded in double quotes, otherwise it assumes it's a numeric value.

See Also

For more information, read the MSDN Library article "Fields Collection" (http://msdn.microsoft.com/library/en-us/ado270/htm/mdcolfields.asp).

Importing Data

Problem

You want to import the data from a comma-delimited text file (such as the one that follows) into a table:

ProductName,UnitPrice,SupplierId,QuantityPerUnit, Discontinued "Swiss Chocolate Bunny",$10.00,22," Case of 20", 0 "White Chocolate Bar",$10.00,22," Case of 20", 0

Solution

The following WSHENT.CopyTable WSC component uses the ODBC Text import driver to process text files and builds SQL statements to insert the information into a database:

Discussion

While more sophisticated mechanisms for portable data exchange, such as XML, are being introduced, importing data using a delimited text file is still commonplace. With the use of data warehousing on the increase, scheduled bulk data imports from various enterprise platforms is common. WSH and ADO can be a practical way of integrating this data into one source.

The WSHENT.CopyTable WSC component copies data from a source Recordset to a specified destination table recordset. It's useful for copying data from different data sources.

The properties for the CopyTable object are listed in Table 13-20.

Once you have set the properties, invoke the CopyTable method. This will add the data from the source recordset to the table in the Destination connection. The routine does not provide any mechanism for trapping errors in the case of duplicate data that violates key integrity or referential integrity constraints.

The following example copies data from a text file recordset to an Access table:

Const adCmdText = 1 Dim objConn, objRst Dim objConnDestination, objCopyData Set objConnDestination = CreateObject("ADODB.Connection") Set objConn = CreateObject("ADODB.Connection") Set objRst = CreateObject("ADODB.Recordset") 'open the destination data file objConnDestination.Open _ "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:data Nwind.mdb;" objConn.Open _ "DRIVER={Microsoft Text Driver (*.txt; *.csv)};DBQ=D:datawshado;" Set objRst = objConn.Execute("Select * From [Products.csv]") Set objCopyData = CreateObject("WSHENT.CopyTable") objCopyData.Source = objRst 'set the destination connection objCopyData.Destination = objConnDestination objCopyData.Table = _ "Products" 'set the destination table Call objCopyData.CopyTable objRst.Close objConn.Close

For large quantities of data, WSH/ADO might not be able to provide sufficient performance and reliability. An option is to use Data Transaction Services (DTS), which ships with SQL Server 7.0. DTS provides a powerful mechanism to transfer data between different data sources.

DTS allows for the importing and exporting of data between various data sources using a DTS package, which is a set of rules governing what information is to be copied or moved. Even though DTS is a part of SQL Server 7.0, neither the source nor the destination have to be SQL Server tables—you can import/export between any ODBC or OLE DB data source. You could even purchase SQL Server just for the DTS capability.

DTS also provides detailed logging of package execution, as well as transaction support, which allows unsuccessful transfers to be "rolled back," ensuring the data is returned to its original state before the execution of the package.

DTS also provides mechanisms to "massage" the data on import, so you can add VBScript or JScript code to manipulate the data as it's being imported.

DTS is also exposed as a COM object, so any packages you create can be invoked from WSH.

The following example creates a DTS package that imports a delimited text file into the SQL Server Northwind sample database's Products table. It demonstrates how to manipulate DTS packages from WSH.

The DTS solution will be used to import a delimited text file containing a product description, package size, and price:

Nigiri Sushi,250G, 4.50 Tempura Batter,Package 500g,3.95 Soya Sauce,500ml,2.95 Green Tea,500g, 3.25

To create a DTS package, follow these steps:

1. Start SQL Server 7.0 Enterprise Manager and connect to the SQL Server you want to create the package on. Right-click any database on the server and select All Tasks > Import Data. The DTS Import Wizard dialog box appears, as shown in Figure 13-11. This starts the Import Wizard, which guides you through the steps of creating a DTS import package.

   Figure 13-11: DTS Import Wizard

2. Select Text File as the source. Use importproducts.txt as a sample file, as shown in Figure 13-12.

   Figure 13-12: Choose a data source.

3. Accept the defaults selected by the Wizard: delimited text file and comma delimiters (shown in Figure 13-13 and Figure 13-14).

   Figure 13-13: Select a file format.

   Figure 13-14: Specify a column delimiter.

4. Select the Northwind database as the destination database, as shown in Figure 13-15.

   Figure 13-15: Choose a destination.

5. Select the Products table as the destination and select the Transform button to map the columns, as shown in Figure 13-16.

   Figure 13-16: Select a destination table.

6. Map the columns as shown in the Figure 13-17, and select the "Append rows to destination table" radio button.

   Figure 13-17: Control the column mappings.

7. Click the "Enable identity insert" option to deselect it. A dialog box similar to the one shown in Figure 13-18 appears.

   Figure 13-18: Save and schedule

8. In the bottom portion of the dialog box, click the Save DTS Package option to select it, but don't run the package immediately.
9. Type ProductsImport in the Name field as shown in Figure 13-19. Don't set any passwords.

   Figure 13-19: Save DTS package

10. The package is now complete. Click the Finish button. A dialog box similar to the one shown in Figure 13-20 appears.

    Figure 13-20: DTS Wizard completion dialog box

The finished package can be used to import the importproducts.txt text file, but you are going to modify the package to add some additional functionality to it. These features will be manipulated from the WSH scripting. To modify the package, follow these steps:

1. From the SQL Server Enterprise Manager, design the package. View the package properties, and go to the Global Variables tab. Add a new variable called SupplierID, as shown in Figure 13-21.

   Figure 13-21: DTS Package Properties dialog box

2. Click the OK button after you've added the variable.
3. From the main DTS design screen, double-click the transformation arrow between Connection 1 and Connection 2. This displays the Data Transformation properties.
4. Select the Transformations tab.
5. Select the Supplier ID field from the Destination table column.
6. Select an empty column from the Source table column on the left. Do not select any of the source fields (Col1, Col2, or Col3) from the Source table column.
7. From the New transformations drop-down box, select ActiveX Script. Select the New button to create a new ActiveX transformation script. The ActiveX Script Transformation Properties dialog box appears, as shown in Figure 13-22.

   Figure 13-22: ActiveX Script Transformation Properties dialog box

8. Add the following line:

   DTSDestination("SupplierID") = DTSGlobalVariables("SupplierID").Value

   Figure 13-22 shows the location of this statement.

   This code passes the value stored in the DTS Global variable Supplier ID to the SupplierID field. This value will be set from the WSH script before execution of the package.

9. Click OK to exit the ActiveX Script Transformation Properties dialog box. The Data Transformation Properties dialog box should now display an arrow that is connected to the SupplierID field but not linked to a Source table field, as shown in Figure 13-23.

   Figure 13-23: Data Transformation Properties dialog box

10. Click OK to exit the Data Transformation Properties dialog box.
11. Save and exit from the package designer screen.

To access the package from WSH, you require DTS COM objects, which are installed with SQL Server. You do not require the SQL Server engine to reside on the machine you are executing the script from. The easiest way to ensure the COM components are installed is to install the SQL Server Enterprise Manager on the computer you want to execute the script from.

A DTS Package object can be created using the DTS.Package ID. The Package object exposes a number of DTS-related collection properties, such as Connections, Tasks, and Steps.

To load a DTS package, use the Package object's LoadFromSQLServer method. The syntax is as follows:

objDTS.LoadFromSQLServer strServer, [strUserID], [strPassword], _ [nFlag], [strPackagePassword], [strPackageGUID], [strVersionGUID],[strPackageName]

The parameters for the LoadFromSQLServer method are listed in Table 13-21.

The following code snippet shows how to load and execute a DTS. The DTS package is opened and the data source for the text connection is set to the file you want to import. The DTS global variable SupplierID is set to the value for the Tokyo Traders supplier, which is determined from the Northwind Supplier table:

Const DTSSQLStgFlag_UseTrustedConnection = 256 Dim objDTS Set objDTS = CreateObject("DTS.Package") 'open the ProductImport package from the Odin server using NT 'authentication objDTS.LoadFromSQLServer "Odin", , , _ DTSSQLStgFlag_UseTrustedConnection, , , , "ProductImport" 'set the data source for the text connection. objDTS.Connections("Connection 1").DataSource = "d:importproducts.txt" 'enable writing of the completion status to event logs objDTS.WriteCompletionStatusToNTEventLog = True 'set the supplier ID global variable to 4, which is the Tokyo Traders 'supplier objDTS.GlobalVariables("SupplierID") = 4 objDTS.Execute

See Also

Solution 13.1. For more information, read "DTS Package Development, Deployment and Performance" (http://support.microsoft.com/support/kb/articles/Q242/3/91.ASP).

Executing a Stored Procedure

Problem

You want to execute a SQL Server stored procedure and return the result(s) as a Recordset.

Solution

You can create an ADO Command object and specify an open Connection object to the server you want to execute the stored procedure on. Call the Execute method and specify any parameters you want to pass to the procedure.

The following code sample executes the byroyalty stored procedure from the SQL Server Pubs sample database, passing the parameter value of 50:

Const adCmdStoredProc = 4 Set objCmd = CreateObject("ADODB.Command") Set objConn = CreateObject("ADODB.Connection") ' open the pubs data source objConn.Open "Provider=SQLOLEDB.1;User ID=sa;Initial Catalog=pubs;Data Source=Odin" 'set the active connection Set objCmd.ActiveConnection = objConn 'set the stored procedure to execute objCmd.CommandText = "byroyalty" 'execute the stored procedure, passing parameters to it. Set objRst = objCmd.Execute(,Array(50), adCmdStoredProc) 'loop through and display the results While Not objRst.Eof Wscript.Echo objRst(0) objRst.MoveNext Wend objConn.Close

Discussion

You can execute a query in Access or a stored procedure in SQL Server to return data. The advantage of using stored procedures or prebuilt queries is performance: They have already been optimized and "compiled," which saves time when they execute.

In addition to performance advantages, database servers may implement security that prevents queries from executing directly against a database's underlying tables, so you may find that performing certain operations against your database server might not be possible. Instead, all access is provided by stored procedures. A number of the advantages of using stored procedures are listed in Table 13-22.

A stored procedure can perform anything from simple queries to sophisticated data manipulation. But what if you want to execute a stored procedure on SQL Server that requires a parameter? To pass parameters to the data source, you need to use a Command object.

The Command object allows the execution of commands against your data source. If you don't pass any parameters, it functions much like the Connection object's Execute method.

Before you can execute a Command object, you must first set the ActiveConnection and CommandText properties. Then you can invoke the Command object's Execute method. Unlike the Recordset object's Open method, you cannot pass the connection and query information as parameters to the Execute method. Its syntax is as follows:

Set objRecordset = cmd.Execute ([RecordsAffected,] [Parameters,] [Options])

The results of the stored procedure are returned as a recordset. When executing action queries and stored procedures that do not return any data, you do not need to return a Recordset:

cmd.Execute [RecordsAffectfed,] [Parameters,] [Options]

The optional parameters for the Execute method are listed in Table 13-23.

The ActiveConnection property defines what data source the Command object is to use. You can pass either an existing Connection object or a valid connection string. The following example uses a Connection object:

' open the pubs data source objConn.Open "Provider=SQLOLEDB.1;User ID=sa;Initial Catalog=pubs;Data Source=Odin" objConn.ActiveConnection = objConn

This example passes a connection string:

objConn.ActiveConnection = _ "Provider=SQLOLEDB.1;User ID=sa;Initial Catalog=pubs;Data Source=Odin"

The CommandText property defines the command text used to query the data source. This may be a query or a database stored procedure, as illustrated by the following code fragments that assign a value to the CommandText property:

objConn.CommandText = "Select * From Suppliers" objConn.CommandText = "byroyalty" ' SQL Server stored procedure, from Pubs database objCmd.CommandText = "Select * From Suppliers Where CompanyName = ?" ' Access query from Northwind - note the square brackets, 'this is because of spaces in the query name objConn.CommandText = "[Ten Most Expensive Products]"

You can execute a query or stored procedure that may not return values, such as delete/update/create operations. To return data from a Command object, assign the results of the Execute method to a Recordset object. The Recordset created by the Command object's Execute method is a forward-only, read-only cursor. For example, the following code fragment creates a forward-only, read-only Recordset:

objCmd.CommandText = "Select * From Suppliers Where CompanyName = ?" Set objRst = objCmd.Execute(, Array("Grandma Kelly's Homestead"))

See Also

For more information, read the MSDN Library article "Calling a Stored Procedure with a Command" (http://msdn.microsoft.com/library/en-us/ado270/htm/mdconcommandstoredproc.asp).

Executing an Access Parameter Query

Problem

You want to execute an Access parameter query and return the results as a Recordset.

Solution

You can create an ADO Command object and set CommandText to the name of the Access query. The name of the query must be in square brackets:

Set objCmd = CreateObject("ADODB.Command") 'check if script is run using Wscript or Cscript If StrComp(Right(Wscript.Fullname,11)," cscript.exe", vbTextCompare) <>0 Then Wscript.Echo "This script is best run using Cscript.exe" Wscript.Quit End If objCmd.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=D:dataAccessSamplesNorthwind.mdb" objCmd.CommandText = "[Employee Sales by Country]" 'set the query 'execute the command, passing an array of parameters to it. Set objRst = objCmd.Execute(, Array(#1/1/1996#, #12/12/1996#)) While Not objRst.EOF Wscript.Echo objRst("ShippedDate") & " " & objRst("SaleAmount") objRst.MoveNext Wend objRst.Close objConn.Close

Discussion

While a great deal of emphasis is put on dedicated database servers and stored procedures, Access is fully capable of using parameter queries. Access queries provide the same performance enhancements as stored procedures: The query is precompiled and optimized in the Access database. It's also a good habit to build parameter queries in the event the database is upsized to a dedicated database server, because the client logic can remain intact while the queries are converted to stored procedures on the database server.

If the query name contains spaces, surround the query in square brackets ([]) or the grave accent character (`), which is ASCII character 96.

To add a parameter to an Access query, create the query in Access and insert the name of the parameter you want to query in square brackets.

Note that ADO 2.0 and Jet 3.51 OLE DB provider do not provide the ability to execute parameter queries. You can do this from either Access ODBC drivers with ADO 2.1 and later and the Jet 4.0 OLE DB provider. The following example creates a parameter query.

1. In the Access Northwind sample database, create the following query, qryListProducts:

   PARAMETERS category Text, company Text; SELECT Products.* FROM Suppliers INNER JOIN (Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID) ON Suppliers.SupplierID = Products.SupplierID WHERE (((Categories.CategoryName)=[category]) AND ((Suppliers.CompanyName)=[company]));

2. Save the query as qryListProducts.

The following script will execute the query and return the results to a Recordset:

objCmd.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=D:dataAccessSamplesNorthwind.mdb" objCmd.CommandText = "qryListProducts" 'set the query 'execute the command, passing an array of parameters to it. Set objRst = objCmd.Execute(, Array("Beverages", "Exotic Liquids")) While Not objRst.EOF Wscript.Echo objRst("ProductName") & ""& objRst("Unitprice") objRst.MoveNext Wend objRst.Close objConn.Close

See Also

For more information, read "How to Use Parameters with ActiveX Data Objects (ADO) and Jet" (http://support.microsoft.com/support/kb/articles/Q225/8/97.ASP).

Processing Multiple Recordsets

Problem

You need to access all of the results from a SQL Server query that returns multiple recordsets.

Solution

You can execute the stored procedure against the server and use the NextRecordset method to process each Recordset returned.

The following sample uses the reptq3 stored procedure that comes with the SQL Server Pubs sample database:

Const adCmdStoredProc = 4 Const adStateClosed = 0 Dim objCmd, objConn, objRst Set objCmd = CreateObject("ADODB.Command") Set objConn = CreateObject("ADODB.Connection") ' open the pubs data source objConn.Open "Provider=SQLOLEDB.1;User ID=sa;Initial Catalog=pubs;Data Source=Odin" objCmd.CommandText = "reptq3" Set objCmd.ActiveConnection = objConn Set objRst = objCmd.Execute(,Array(0, 1000, "business"), adCmdStoredProc) 'loop through each Recordset Do While objRst.State <> adStateClosed Do While Not objRst.Eof Wscript.Echo objRst(0) objRst.MoveNext Loop Set objRst = objRst.NextRecordset Loop objConn.Close

Discussion

Database server stored procedures offer a great deal of flexibility and power. In the preceding Solution, the reptq3 stored procedure from the Pubs database is executed and returns all titles within a certain price range that are of a certain type. It also returns the number of books for each book type that meets the criteria. This represents two recordsets: One shows all the books for each category that meet the criteria, and the other subtotals the count of the books.

In order to process multiple Recordsets, invoke the NextRecordset method to get the next Recordset in the set. Use the Recordset's State property to determine when no more Recordsets are available.

See Also

For more information, read the MSDN Library article "Generating Multiple Recordsets" (http://msdn.microsoft.com/library/en-us/adosql/adoprg02_30tv.asp).

Modifying Command Results

Problem

You want to be able to modify the results of a query that is executed through the Command object.

Solution

You can use a Command object as the source parameter to a RecordSet's Open method. Specify an updateable Recordset type when opening the Recordset.

The Solution uses the Ten Most Expensive Products query from the Access Northwind sample database as the source for the Command object and updates the price of each record returned by the query by 5 percent:

Discussion

A Recordset created by the Command object's Execute method is a forward-only, read-only cursor. This is a "cheap" recordset: It offers good performance using low resources, but it doesn't provide much navigational functionality or any ability to modify the data.

To create a more functional recordset, create a Command object and set the appropriate properties, including ActiveConnection and CommandText. Create a Recordset object and invoke its Open method, passing it the Command object as an argument to the Source parameter.

You do not have to set the Connection object for the Recordset object. It will inherit the Connection object from the Command object. Make sure you specify cursor and lock options that allow for adding or updating information in the resulting Recordset.

See Also

For more information, read the MSDN Library article "Open Method (ADO Recordset)" (http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthrstopen.asp).

Returning a Parameter Value from a Stored Procedure

Problem

You need to execute a stored procedure that returns a value.

Solution

In SQL Server, create a stored procedure under the Pubs database and call it AddNewStore. This procedure will add a new store record to the Stores table in the Pubs database and return the new store ID:

CREATE PROCEDURE AddNewStore @StoreName varChar(40), @Address varChar(40), @City varChar(20),@StoreID integer OUTPUT AS DECLARE @newid int Select @newid=max(stor_ID) from stores Set @newid = @newid + 1 Insert into stores (stor_id,stor_name,stor_address,city) Values(@newid,@StoreName,@Address,@city) Select @StoreID =@newid

Executing the following script will create a Command object and add parameters to it that will be passed to the stored procedure upon execution. One parameter is created as an output parameter to store a return value from the stored procedure:

Discussion

Parameters can be passed to a stored procedure by calling the Command object's Execute method and passing an array containing parameter values. There are a few drawbacks to calling stored procedures using this method.

• When passing parameters via arrays, the provider has to figure out what data type the parameter being passed is. This is really only a problem where you're executing the Command object multiple times (hundreds or thousands of times) with varying parameter sets.
• Stored procedures can return values, and to get access to returned parameters you must use a Command object. But you cannot return values when using an array to pass parameters.
• When using the Command object as the Source for a Recordset object's Open method, you can't pass parameters when opening the recordset.

The Command object has a Parameters collection. This collection contains all parameters passed to the Command object. When you pass an array of parameters to the Command object's Execute method, the Parameter objects are automatically created and added to the Command object's Parameters collection.

To return a value from a stored procedure, declare one of the stored procedure parameters as an output parameter. You can add the parameters to the Parameters collection by calling the Command object's CreateParameter method to create a separate Parameter object and then append it to the Parameters collection. The syntax of the CreateParameter method is as follows:

Set objParam =objCmd.CreateParameter([Name],[ Type], [Direction], [Size], [Value])

The CreateParameter method returns a Parameter object and takes the optional arguments listed in Table 13-24. The parameter direction types and data types are listed in Table 13-25 and Table 13-26, respectively.

You can then append the Parameter object to the Parameters collection by calling the latter's Append method and passing it the parameter to be appended to the collection. For example, the following code fragment first creates a Parameter object and then appends it to the Parameters collection:

Set objParam = objCmd.CreateParameter("paramname", _ adVarChar, , 40, "value") objCmd.Parameters.Append objParam

You can also do this in a single step by appending the Parameter directly to the collection:

objCmd.Parameters.Append objCmd.CreateParameter("paramname", _ adVarChar, , 40, "value")

One problem when passing parameters to SQL Server stored procedures is knowing exactly what ADO data type to specify. There are a large number of data types available and it can be difficult to know what data type maps to what stored procedure/SQL server type (e.g., Is it adSmallInt, adTinyInt, or adUnsignedTinyInt for a byte?).

The Parameters collection exposes a Refresh method. If a Command object is set to call a SQL Server stored procedure and the Refresh method is called on that object's Parameters collection, the Parameters collection is updated with all the correct parameter information for the stored procedure.

The Refresh method takes the guesswork out of determining the number of parameters to pass and the corresponding data types and lengths. You only need to set the parameters you require. If there are any optional parameters, they do not need to be set. The following code sample performs the same operation as the Solution script:

Const adCmdStoredProc = 4 Set objCmd = CreateObject("ADODB.Command") Set objConn = CreateObject("ADODB.Connection") 'open the pubs data source objConn.Open _ "Provider=SQLOLEDB.1;User ID=fred;Initial Catalog=pubs;Data Source=Odin" 'need to specify the command is a stored procedure objCmd.CommandType = adCmdStoredProc Set objCmd.ActiveConnection = objConn 'set the stored procedure name objCmd.CommandText = "AddNewStore" 'refresh the parameters, this will update the Parameters collection objCmd.Parameters.Refresh 'set the appropriate parameters objCmd.Parameters("@StoreName").Value = "Acme Ltd." objCmd.Parameters("@Address").Value = "123 Main Street" objCmd.Parameters("@City").Value = "Somewhere" objCmd.Execute Wscript.Echo "The store id is" & objCmd.Parameters("@Storeid") objCmd.ActiveConnection.Close

One requirement for the Refresh method to work is to specify that the CommandType is adCmdStoredProc. Parameter values can be read and set using the name specified in the stored procedure.

Unfortunately, using the Refresh method to get stored procedure parameters has a slight drawback. It requires an additional call to the server to refresh the Parameters collection. If performance is an issue, manually adding the parameters is the best method.

The Refresh method can be used to assist the building of these Parameter statements, which is demonstrated in the following command-line program, buildparams.wsf:

The buildparams.wsf program requires two parameters: a valid connection string to the source and the name of a stored procedure to build the parameters for. It builds the statements required to build parameters for a Command object. buildparams.wsf uses the adoinc.vbs support library's GetDataType function to return the field type names:

'GetDataType 'Returns ADO data type. 'Parameter 'nType ADO Data type value 'Returns string value representing data type name Function GetDataType(nType) Dim strRet Select Case nType Case 20 strRet = "adBigInt" Case 128 strRet = "adBinary" Case 11 strRet = "adBoolean" Case 8 strRet = "adBSTR" Case 136 strRet = "adChapter" Case 129 strRet = "adChar" Case 6 strRet = "adCurrency" Case 7 strRet = "adDate" Case 133 strRet = "adDBDate" Case 137 strRet = "adDBFileTime" Case 134 strRet = "adDBTime" Case 135 strRet = "adDBTimeStamp" Case 14 strRet = "adDecimal" Case 5 strRet = "adDouble" Case 0 strRet = "adEmpty" Case 10 strRet = "adError" Case 64 strRet = "adFileTime" Case 72 strRet = "adGUID" Case 9 strRet = "adIDispatch" Case 3 strRet = "adInteger" Case 13 strRet = "adIUnknown" Case 205 strRet = "adLongVarBinary" Case 201 strRet = "adLongVarChar" Case 203 strRet = "adLongVarWChar" Case 131 strRet = "adNumeric" Case 138 strRet = "adPropVariant" Case 4 strRet = "adSingle" Case 2 strRet = "adSmallInt" Case 16 strRet = "adTinyInt" Case 21 strRet = "adUnsignedBigInt" Case 19 strRet = "adUnsignedInt" Case 18 strRet = "adUnsignedSmallInt" Case 17 strRet = "adUnsignedTinyInt" Case 132 strRet = "adUserDefined" Case 204 strRet = "adVarBinary" Case 200 strRet = "adVarChar" Case 12 strRet = "adVariant" Case 139 strRet = "adVarNumeric" Case 202 strRet = "adVarWChar" Case 130 strRet = "adWChar" End Select GetDataType = strRet End Function

The following command-line statement would build the parameter code for the AddNewStore stored procedure:

buildparams "Provider=SQLOLEDB;User Id=sa;Initial Catalog=pubs; Data Source=THOR "addnewstore

Note the following about passing Parameter objects to stored procedures:

• The stored procedure reads the parameters in the order they are added, not alphabetically by parameter name.
• You do not need to create parameter names that mirror the parameter names in the stored procedures you are calling. The parameter name is for reference and readability.

Transaction Processing

Problem

You want to perform database operations in which you are guaranteed data integrity is maintained during any operation that is performed.

Solution

You can use the Recordset object's BeginTrans and CommitTrans methods to start and complete transactions, and optionally you can use RollbackTrans to "roll back" any transactions.

The Solution script demonstrates the transaction commands by creating a history file from old orders in the Access Northwind sample database. Any order over a certain age is copied to the History tables. The order details are copied from the Order Details table into the Order Details History table, while the order master record is copied from the Orders table into the Orders History table.

Both Orders History and Order Details History do not exist in the Northwind database and must be created by using a copy of the structure from the Orders and Order Details History tables.

The script uses the WSHCB.CopyTable script from Solution 13.17 to copy the data to the History tables:

Const adCmdText = 1 Dim objConn Dim objDestConn, objRst, objCopy, dDate Set objCopy = CreateObject("ENTWSH.CopyTable") Set objConn = CreateObject("ADODB.Connection") Set objDestConn = CreateObject("ADODB.Connection") Set objRst = CreateObject("ADODB.Recordset") objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=d:dataaccesssamplesNorthwind.mdb;" objDestConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=d:dataaccesssamplesNorthwind.mdb;" dDate = #1/1/1996# 'start transactions objConn.BeginTrans objDestConn.BeginTrans objCopy.DESTINATION = objDestConn 'set the destination 'first get the order line items Set objRst = objConn.Execute("SELECT [Order Details].* " & _ "FROM Orders INNER JOIN [Order Details] ON " & _ "Orders.OrderID =[Order Details].OrderID " & _ "WHERE (ShippedDate<#" & dDate & "#)", , adCmdText) objCopy.SOURCE = objRst objCopy.Table = "[Order Details History]" If Not objCopy.CopyTable() Then objConn.RollbackTrans objDestConn.RollbackTrans End If objConn.Execute "DELETE [Order Details].*, Orders.ShippedDate " & _ "FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = " & _ "[Order Details].OrderID " & _ "WHERE (((Orders.ShippedDate)<#" & dDate & "#));", , adCmdText 'get the details from order master Set objRst = objConn.Execute("Select * From Orders Where ShippedDate<#" _ & dDate & "#", , adCmdText) objCopy.SOURCE = objRst objCopy.Table = "OrderHist" If Not objCopy.CopyTable() Then Debug.Print objCopy.Error objConn.RollbackTrans objDestConn.RollbackTrans Exit Sub End If Set objRst = objConn.Execute("Delete * From Orders Where ShippedDate<#" _ & dDate & "#", , adCmdText) objDestConn.CommitTrans objConn.CommitTrans objConn.Close objDestConn.Close

Discussion

Transaction processing is a vital part of any mission-critical database system. It guarantees the integrity of data during the data transactions. Transaction processing is crucial especially when operations are performed on more than one table and the state of the tables must balance before and after the operation.

In the Solution, the order detail items are first copied and deleted, and then the order master records are copied and deleted. If the operation aborts before all steps are completed, you are left with tables in an incomplete state.

In this Solution, if there's a failure within any database operations while the data is being archived, the transaction is "rolled back." None of the operations take effect until the CommitTrans statements are executed. So if you set a breakpoint in the code halfway through the procedure and view the tables, it will appear as though nothing has actually changed in them.

There is error checking that will catch problems with the data transfer and roll back the data. But there is also a nonexplicit rollback, which guarantees if the database operations are interrupted by a critical failure, such as a power failure, system crash, or act of God, the database will be restored to the state before the transaction was started.

To start a transaction, invoke the BeginTrans method on the Connection object you want to perform the transaction on. Any operations performed with the Connection object, such as creation of recordsets or execution of commands, will be included in the transaction.

If a trappable error occurs during the transaction, the transaction can be rolled back by invoking the RollbackTrans method. If a critical system failure such as a power failure occurs, the database(s) in the transaction will be returned to its original state before the beginning of the transaction.

Transaction processing is provider specific. Some providers may not provide support for it.

See Also

For more information, read the MSDN Library article "BeginTrans, CommitTrans, and RollbackTrans Methods" (http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthbegintrans.asp).