Data Access

Table of contents:

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.

Table 13-1: ADO Versions
VERSION	HOW TO INSTALL	FEATURES
1.0	IIS 3.0/Active Server Pages	General data access features: query, update, delete
1.5	IIS 4.0, Internet Explorer 4.0, Windows 98
2.0	Downloadable installation, Visual Studio 6.0	Hierarchical Recordsets-data shaping
2.1	Downloadable installation, Microsoft Explorer 5.0/Office 2000	ADO Extensions (ADOX)
2.5	Windows 2000	Provides access to data streams

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.

Note

Extensive documentation is included with the ADO 2.x installation. It's located in Program FilesCommon FilesSystemADO. This Help file includes a general ADO reference as well as a number of examples.You can find other ADO-related information on Microsoft's Universal Data Access (UDA) site (http://www.microsoft.com/data). This site contains the latest information on ADO, OLE DB, and ODBC, as well as articles and reference information.

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:

Select Start menu > Control Panel.
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.
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.
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.
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.

Table 13-2: *Open* Method Arguments
PARAMETER	DESCRIPTION
ConnectionString	String. Contains connection information. See Table 13-3 for a list of connection string syntax.
UserID	Optional string. Specifies the user name to use when establishing the connection.
Password	Optional string. Specifies the user password to use when establishing the connection.

Table 13-3: Connection String Syntax
PARAMETER	DESCRIPTION
Provider	Identifies the data provider. The default provider is MSDASQL, which specifies that the data source you are using is an ODBC data source. Therefore, its use is optional when specifying ODBC data sources. See the DSN connection section for its use.
DSN	The name of the ODBC definition. This is the name defined in the Control Panel ODBC Data Source Administrator. If you're just specifying the data source name, you do not need to specify the DSN= keyword. Therefore, the connection strings StrConnect = "Northwind" and strConnect = "DSN=Northwind" are the same.
FileDSN	Specifies a file that contains the connection information. See Solution 13.2 for more information.
DATABASE	Specifies the database to access. For database servers such as SQL Server, you are required to specify the database name you want to work with.
UID	User ID.
PWD	Password. Due to the low level of WSH security (WSH scripts can be read in normal text editors), it is wise to avoid storing passwords in the actual WSH files. It might be prudent to create low-security access user IDs that provide the minimal required access to the data source in question.

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:

Select Start menu > Control Panel.
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.
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.
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.
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
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.

Table 13-4: Data Interfaces
DATA SOURCE	PROVIDER NAME
ODBC	MSDASQL
Access	Microsoft.Jet.OLEDB.3.51 Microsoft.Jet.OLEDB.4.0 (Office 2000/IE 5.0)
Oracle	MSDAORA
MS Index Server	MSIDXS
SQL Server	SQLOLEDB
ADSI	AdsDSOObject
Microsoft IIS Index Server	MSIDXS

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.

Table 13-5: Connection String Arguments
PARAMETER	DESCRIPTION
Provider	Identifies the data provider. The default is MSDASQL, which is the ODBC provider.
Driver	Required for ODBC connection strings. Specifies the ODBC driver to use (e.g., DRIVER={Microsoft Access Driver (*.mdb)}).
Database	Specifies the database to access. For database servers like SQL Server, you are required to specify the database name you want to work with.
UID	User ID.
PWD	Password.

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:

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.
Give the new UDL file a descriptive name.
Right-click the file and select Properties from the menu. Alternatively, double-click the file.
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.
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:

Create a .udl connection for your data source using the Data Link Properties dialog box.
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.
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:

Create a file DSN connection using the ODBC Data Source Administrator.

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

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

Note

For more information, read "Howto Use Data Link Files with ADO" (http://support.microsoft.com/support/kb/articles/Q189/6/80.ASP) and "Connection String Syntax" (http://msdn.microsoft.com/library/en-us/oledb/htm/oledbconnectionstringsyntax.asp).

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.

click to expand
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.

Note

The Script Encoder provides security against casual access, but it can be cracked by a determined user using publicly available tools.

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.

Table 13-6: Connection String Security Properties
PROPERTY	DESCRIPTION
User ID	Valid Access user ID
Password	Password for the user ID specified by the User ID property
Jet OLEDB:System Database	Path to Access security file
Jet OLEDB:Database Password	Access database password

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.

Table 13-7: Jet Engine Version Values
VALUE	DESCRIPTION
1	Jet version 1.0
2	Jet version 1.1
3	Jet version 2.x
4	Jet version 3.x
5	Jet version 4.x

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.

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.

Table 13-8: *Execute* Method Arguments
PARAMETER	TYPE	DESCRIPTION
CommandText	String	Contains the provider-specific command to execute. This may be in the form of a SQL statement, a table, a stored procedure, or special command text.
RecordsAffected	Long	Optional. Returns the number of records affected. For example: 'return the number of items deleted from customers. Set rst = objconn.Execute ("Delete * from Customers", _ NumDel) Wscript.Echo NumDel & " items were deleted" You may find that certain providers do not provide an accurate count of records processed.
Options	Long	Optional. Identifies what type of command the CommandText parameter contains. Table 13-9 lists the possible values. Providing this parameter speeds up the initial processing, because the provider does not have to determine what CommandText represents.

Table 13-9: Values of the *Execute* Method's *Option* Parameter
CONSTANT	VALUE	DESCRIPTION
adCmdText	1	Identifies the command text as a raw command string, such as a SQL statement.
adCmdTable	2	Identifies the command text as referencing a table in the provider.
adCmdStoredProc	4	Identifies the command text as referencing a stored procedure in the provider.
adCmdUnknown	8	Default. Identifies the command text as unknown. The provider attempts to identify what the source string is.
adCmdFile	256	Opens data from a persistent file-based recordset.

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

Table 13-10: Recordset *Move* Methods
METHOD	DESCRIPTION
MoveFirst	Attempts to move to the first record in the Recordset
MoveLast	Attempts to move to the last record in the Recordset
MoveNext	Attempts to move to the next record in the Recordset
MovePrevious	Attempts to move to the previous record in the Recordset

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.

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.

Table 13-11: *Open* Method Optional Parameters
NAME	TYPE	DESCRIPTION
Source	Variant	Provider-specific text, such as a SQL statement, a table name, a stored procedure name, special command text, or a Command object.
ActiveConnection	Variant	Either a valid Connection object or a connection string.
CursorType	Integer	Optional. The cursor type. See Table 13-12 for a list of available cursor types. The default is forward-only.
LockType	Integer	Optional. The lock type. See Table 13-14 for list of available lock types. The default is read-only.
Options	Integer	Optional. Identifies the Source type. Table 13-9 lists the different options. Specifying the Source type helps speed the opening of the recordset, because the provider doesn't have to figure out what the Source text represents.

Table 13-12: Different ADO Recordset Cursors
TYPE	VALUE	DESCRIPTION
adOpenForwardOnly	0	Allows only forward movement through records (the MoveNext method). Does not allow the MoveFirst, MovePrevious, or MoveLast methods, and doesn't provide a record count. Any additions, deletions, or updates made by other users to the data are not reflected in the recordset.
adOpenKeyset	1	Allows all navigation methods (all Move methods and bookmarks). Deletions and additions made by other users are not reflected in the cursor, so if another user adds a new record it will not be visible in the recordset. Changes made by other users to records in the recordset are visible.
adOpenDynamic	2	Allows you to view additions, changes, and deletions by other users, and allows all types of movement through the recordset. Does not support a record count.
adOpenStatic	3	Makes a local copy of requested data. Allows all navigation methods (all Move methods and bookmarks). Deletions and additions made by other users are not reflected in the cursor, so if another user adds a new record, it will not be visible in the recordset.

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.

Table 13-13: Recordset Cursor Pros and Cons
CURSOR	PROS	CONS
Forward	Least expensive in terms of memory and performance.	Limited navigation abilities: can only perform MoveNext operations.
Keyset	Provides full navigational functionality: all Move methods. More economical than a dynamic cursor.	Additions and deletions made by other users are not automatically reflected in the recordset. This is most important for deleted records, because an error will occur if a record deleted by another user is referenced.
Dynamic	All navigation	Most expensive in terms of memory and performance. Any changes made by other users are reflected in the recordset.
Static	Good performance on small sets of data that are continuously traversed, because a temporary local copy is made that does not require additional fetches from the data source.	Lower performance on larger sets of data.

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.

Table 13-14: Recordset Lock Types
CONSTANT	VALUE	DESCRIPTION
adLockReadOnly	1	Read-only is the default value. You cannot delete or modify data.
adLockPessimistic	2	Pessimistic locking locks the record for the duration of the edit, until the Update method is called or the current record is moved. No other users can make changes to the record while it is locked.
adLockOptimistic	3	Optimistic locking only locks a record for the time required to perform the updates on a record.
adLockBatchOptimistic	4	Optimistic batch updates. Required for batch update mode, as opposed to immediate update mode.

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

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.

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:

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
Select the range.
Choose Insert > Name >Define. The Define Name dialog box appears, as shown in Figure 13-10.

Figure 13-10: The Define Name dialog box
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

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.

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:

Create a Recordset object that is not read-only (its LockType property is not adLockReadOnly) and invoke the AddNew method.
Assign the new values to the record.
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.

Table 13-15: *AddNew* Method Arguments
PARAMETER	DESCRIPTION
Fields	Either a single string value for an individual field or an array of field names for the fields you want to add
Values	Either a single variant value for an individual field or an array of values for multiple fields

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.

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.

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

Atable
ProductName	UnitPrice	QuantityPerUnit
Konbu	6.5	2 kg box
Tofu	25.20	40 - 100 g pkgs.
Wasabi	34.	50 - 500 g pkgs.

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.

Table 13-16: *Find* Method Arguments
PARAMETER	TYPE	DESCRIPTION
criteria	String	Search expression criteria composed of the field name followed by comparison operator and comparison value (e.g., Name = 'Fred').
skiprows	Long	Specifies the number of rows from the current row to start searching. Default value is 0, which will start from the current row.
searchdirection	Integer	Direction the search will go. Possible parameters are as follows: adSearchForward = 1 adSearchBackward =-1 If searching in a forward direction and the criteria is not found, the search will end at the end of the file. If searching in a backward direction and the criteria is not found, the search will end at the beginning of the file.
start	Variant	Bookmark to start the search from.

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.

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.

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.

Table 13-17: OLE DB Provider for Internet Publishing Connection String Parameters
PARAMETER	DESCRIPTION
Source	URL to reference.
Mode	Determines the level of access to be available. Combination of Read, ReadWrite, Share Deny None, Share Deny Read, Share Deny Write, Share Exclusive, and Write.
User ID	Required if read/write operations are to be performed.
Password	Password for the specified user.

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.

Table 13-18: Internet Publishing Provider Fields
FIELD NAME	DESCRIPTION
RESOURCE_STREAMSIZE	Size of file. If a folder, returns Null.
RESOURCE_LASTWRITETIME	Last time the file was written to/updated.
RESOURCE_DISPLAYNAME	Filename.
RESOURCE_ABSOLUTEPARSENAME	Full URL for the file.

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.

Table 13-19: *RecordTypeEnum* Property Values
VALUE	DESCRIPTION
adSimpleRecord	Simple record, no child nodes. In the case of the OLE DB Internet provider, this would be a file.
adCollectionRecord	Collection record, contains child nodes. In the case of the OLE DB Internet provider, this would be a directory.
adStructDoc	Structured document record. No Record objects of this type are exposed through the Internet OLE DB provider.

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:

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.

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.

Table 13-20: *CopyTable* Object Properties
PROPERTY	DESCRIPTION
Destination	ADO Connection object for the destination data source to copy data to
Source	ADO Recordset representing the source to copy data from
Table	Name of table to copy data to

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:

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
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.
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.
Select the Northwind database as the destination database, as shown in Figure 13-15.

Figure 13-15: Choose a destination.
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.
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.
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
In the bottom portion of the dialog box, click the Save DTS Package option to select it, but don't run the package immediately.
Type ProductsImport in the Name field as shown in Figure 13-19. Don't set any passwords.

Figure 13-19: Save DTS package
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:

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
Click the OK button after you've added the variable.
From the main DTS design screen, double-click the transformation arrow between Connection 1 and Connection 2. This displays the Data Transformation properties.
Select the Transformations tab.
Select the Supplier ID field from the Destination table column.
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.
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
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.
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
Click OK to exit the Data Transformation Properties dialog box.
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.

Table 13-21: *LoadFromServer* Method Parameters
PARAMETER	DESCRIPTION
strServer	SQL Server the DTS package resides on.
strUserID	SQL user ID.
strPassword	Password to use for user specified by the SQL user ID.
nFlag	Determines if security access is to be provided by SQL Server-defined user ID and password (the default) or if NT authentication is used. Specify UseTrustedConnection, which has a value of 256, to use NT authentication.
strPackagePassword	Package password.
strPackageGUID	Package identifier—not required if package name is passed.
strVersionGUID	Version identifier if not using the most recent package version.
strPackageName	Name of the package.

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

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.

Table 13-22: Advantages of Using Stored Procedures
ADVANTAGE	DESCRIPTION
Security	A database server may be configured to allow no or minimal direct access to the database data.
Performance	Precompiled and optimized stored procedures.
Integrity	Stored procedures can contain logic that performs parameter checking and prevents incorrect data from being added to tables.

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.

Table 13-23: *Execute* Method Parameters
PARAMETER	TYPE	DESCRIPTION
RecordsAffected	Long	Returns the number of records that the operation affected. This value is returned when delete/update/create operations are performed, but not with SELECT operations.
Parameters	Variant array	An array of parameter values to be used for the CommandText operation.
Options	Long	Specifies how the CommandText property should be evaluated. This is the same as the Command object's Execute method's Options property. See Table 13-9 for parameter values.

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"))

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.

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]));

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

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.

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.

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.

Table 13-24: *Parameter* Object Arguments
NAME	STRING	PARAMETER NAME
Type	Long	Data type for the parameter being passed. See Table 13-26 for a list of available types and their corresponding numeric values.
Direction	Long	Parameter "direction." A parameter may be input-only, a return output value, or both. The default is input-only, adParamInput. Table 13-25 lists the direction types.
Size	Long	Length of the parameter. This only applies to variable-length data types, such as string values. For fixed-length types, such as numbers and dates, it is not required.
Value	Variant	Value of the parameter.

Table 13-25: Parameter Direction Types
CONSTANT	VALUE	DESCRIPTION
adParamInput	1	Default
adParamOutput	2	Output parameter-return value
adParamInputOutput	3	Can be both an input and output parameter
adParamReturnValue	4	Only returns a value, not used as input

Table 13-26: Parameter Data Types
CONSTANT	VALUE
adBigInt	20
adBinary	128
adBoolean	11
adBSTR	8
adChapter	136
adChar	129
adCurrency	6
adDate	7
adDBDate	133
adDBFileTime	137
adDBTime	134
adDBTimeStamp	135
adDecimal	14
adDouble	5
adEmpty	0
adError	10
adFileTime	64
adGUID	72
adIDispatch	9
adInteger	3
adIUnknown	13
adLongVarBinary	205
adLongVarChar	201
adLongVarWChar	203
adNumeric	131
adPropVariant	138
adSingle	4
adSmallInt	2
adTinyInt	16
adUnsignedBigInt	21
adUnsignedInt	19
adUnsignedSmallInt	18
adUnsignedTinyInt	17
adUserDefined	132
adVarBinary	204
adVarChar	200
adVariant	12
adVarNumeric	139
adVarWChar	202
adWChar	130

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 SQL Server provider will return a parameter called RETURN_VALUE for any stored procedure, which is not required when calling stored procedures. The Refresh method's action of updating the Parameters collection is provider specific and does not work with all providers, including the Jet provider.

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.

ODBC

OLE DB

Using ADO

History of ADO

Identifying Your Data

Data Source Name Connection Strings

File DSN Connection

DSN Less Connection Strings

Opening a Secure Database

Problem

Solution

Discussion

See Also

Compacting Access Databases

Problem

Solution

Discussion

See Also

Querying a Table

Problem

Solution

Discussion

See Also

Opening a Table for Writing

Problem

Solution

Discussion

See Also

Manipulating a Text File

Problem

Solution

Discussion

See Also

Accessing Excel Data

Problem

Solution

Discussion

See Also

Querying the Index Server

Problem

Solution

Discussion

See Also

Adding Data

Problem

Solution

Discussion

See Also

Updating Data

Problem

Solution

Discussion

See Also

Accessing HTML Data

Problem

Solution

Discussion

See Also

Deleting Data

Problem

Solution

Discussion

See Also

Accessing Internet Resources

Problem

Solution

Discussion

See Also

Exporting Data

Problem

Solution

Discussion

See Also

Importing Data

Problem

Solution

Discussion

See Also

Executing a Stored Procedure