WORKING WITH QTP

TestEveryThinG

ADO Component Reference

Posted by rajivkumarnandvani on February 6, 2009

ADO
Component Reference


Sun ONE Active Server Pages includes
ActiveX Data Objects (ADO) for connecting ASP applications to databases. ADO is a set of objects
that provide a mechanism to access information from ODBC-compliant data
sources.

This section provides ADO reference information.

In this section:

ADO
Overview

ADO
Objects

ADO Collections

ADO
Overview


The implementation of ADO used with Sun ONE ASP is called ADODB. ADO enables client
applications to access and manipulate data in a database server from a variety
of different vendors in the same manner. With ADO, data is updated and retrieved using a
variety of existing methods (including SQL). In the context of ASP, using ADO typically involves
writing script procedures that interact with a database and use HTML to display
information from data sources.

In ADO,
the Recordset object is the main interface to data. An example of the
minimal VBScript code to generate a recordset from an ODBC data source is as
follows:

set rstMain = CreateObject("ADODB.Recordset")
rstMain.Open "SELECT * FROM authors", _
"DATABASE=pubs;UID=sa;PWD=;DSN=Publishers"

This generates a forward-only, read-only Recordset

object useful for scanning data. A slightly more functional recordset can be
generated as follows:

set rstMain = CreateObject("ADODB.Recordset")
rstMain.Open "SELECT * FROM authors", _
"DATABASE=pubs;UID=sa;PWD=;DSN=Publishers",
adOpenKeyset, adLockOptimistic

This creates a fully scrollable and
updateable recordset.

Note iconNote

Adovbs.inc & Adojavas.inc: For applications that use
VBScript (for example, Active Server Pages), you must include the Adovbs.inc
file in your code in order to call ADO
constants by name (use Adojavas.inc for JScript). Always refer to constants
by name rather than by value since the values may change from one version to
the next.

Note iconNote

Updatable Cursor support: Microsoft and Sun use the
Positioned Update and Positioned SQL features of ODBC to implement the AddNew,
Update, and Delete methods of the ADO Recordset
Object
. For some of the supplied ODBC drivers these features are
not implemented at all (MySQL, PostgreSQL). For these drivers, Sun uses the
implementation of updatable cursors in the ODBC Manager to supply the missing
functionality. This works well for recordsets whose fields contain string or
numeric data as well as a primary key, auto-increment, or timestamp fields.
However, in recordsets containing binary fields or recordsets with duplicate
rows, updates, inserts and deletes should be done using the Execute
method of the Connection object. Connection.Execute will
execute any SQL statement recognized by the database regardless of the
capabilities of the ODBC driver.

Note iconNote

Linux and multiple SELECT statements: On Linux, ADO does not support
stored procedures with multiple SELECT statements.

In ADO,
the object hierarchy is de-emphasized. Unlike Data Access Objects (DAO) or
Remote Data Objects (RDO), you do not have to navigate through a hierarchy to
create objects, because most ADO

objects can be independently created. This allows you to create and track only
the objects you need. This model also results in fewer ADO objects, and thus a smaller working set.

ADO
supports the following key features for building client/server and Web-based
applications:

·
Independently created objects.

·

Support for stored procedures with in/out
parameters and return values.

·
Different cursor types, including the potential
for support of back-end-specific cursors.

·
Advanced recordsetcache management.

·
Support for limits on number of returned rows
and other query goals.

ADO Objects


ADO provides
two objects for managing connections with data sources (Connection and Command),
two objects for managing the data returned from a data source (Field and

Recordset) and three secondary objects (Parameters, Properties,
and Errors) for managing information about ADO.

Note iconNote

ADO
objects cannot be stored in application variables.

Object

Description

Command

Defines a specific command to execute
against a data source.

Connection

Represents an open connection to a
data source.

Error

Provides specific details about each ADO error.

Field

Represents a column of data with a
common data type.

Parameter

Represents a parameter or argument
associated with a Command object based on a parameterized query or
stored procedure.

Property

Represents a dynamic characteristic
of an ADO

object that is defined by the provider. This object is not currently
supported on UNIX.

Recordset

Represents the entire set of records
from a database table or the results of an executed command.

In this section:

ADO
Command Object

ADO
Connection Object

ADO
Error Object

ADO
Field Object

ADO
Parameter Object

ADO
Property Object

ADO
Recordset Object

ADO Command
Object


The Command object defines a
specific command to execute against a data source.

In this section:

ADO
Command Object Collections

ADO
Command Object Methods

ADO
Command Object Properties

ADO
Command Object Remarks

ADO Command
Object Collections


Collection

Description

Parameters

Contains all the Parameter
objects of a Command object.

Properties

Contains all the Property
objects for a specific instance of a Command object. This
collection is not currently supported on UNIX.

ADO Command
Object Methods


Method

Description

CreateParameter

Creates a new Parameter object
with the specified properties.

Execute

Executes the query, SQL statement, or
stored procedure specified in the CommandText property.

In this section:

ADO
Command Object CreateParameter Method

ADO
Command Object Execute Method

ADO Command
Object CreateParameter Method

Creates a new Parameter object with
the specified properties.

CreateParameter Method Syntax (ADO Command Object)
Set parameter = command.CreateParameter (
 Name, Type, Direction, Size, Value)
CreateParameter Method Parameters (ADO Command Object)

parameter

The new ADO Parameter
Object
.

Name

An optional String representing the
name of the Parameter object.

Type

An optional Long value specifying
the data type of the Parameter object. See the ADO
Parameter Object Type Property
for valid settings.

Direction

An optional Long value specifying
the type of Parameter object. See the ADO
Parameter Object Direction Property
for valid settings.

Size

An optional Long value specifying
the maximum length for the parameter value in characters or bytes.

Value

An optional varValue specifying
the value for the Parameter object.

CreateParameter Method Return Value (ADO Command Object)

Returns a Parameter object.

CreateParameter Method Remarks (ADO Command Object)

Use the CreateParameter method to
create a new ADO
Parameter Object
with the specified name, type, direction, size, and value.
Any values you pass in the arguments are written to the corresponding Parameter
properties.

This method does not automatically append
the Parameter object to the ADO Parameters
Collection
of a Command object. This lets you set additional
properties whose values ADO

will validate when you append the Parameter object to the collection.

If you specify a variable-length data type
in the Type argument, you must either pass a Size argument or
set the ADO
Parameter Object Size Property
of the Parameter object before
appending it to the Parameters collection; otherwise, an error occurs.

CreateParameter Method Examples (ADO Command Object)

See the ADO Collections
Append Method
example.

ADO Command
Object Execute Method

Executes the query, SQL statement, or
stored procedure specified in the CommandText property.

Object Execute Method Syntax (ADO Command Object)

For a row-returning Command:

Set recordset = command.Execute(
 RecordsAffected, Parameters, Options )

For a non-row-returning Command:

command.Execute RecordsAffected, Parameters, Options
Object Execute Method Parameters (ADO Command Object)

RecordsAffected

An optional Long variable to which
the provider returns the number of records that the operation affected.

Parameters

An optional Variant array of parameter
values passed with an SQL statement. (Output parameters will not return correct
values when passed in this argument.)

Options

An optional Long value that
indicates how the provider should evaluate the CommandText property of
the Command object:

Constant

Description

adCmdText

The provider should evaluate CommandText

as a textual definition of a command, such as a SQL statement.

adCmdTable

The provider should evaluate CommandText
as a table name.

adCmdStoredProc

The provider should evaluate CommandText
as a stored procedure.

adCmdUnknown

The type of command in CommandText

is not known.

See the ADO
Command Object CommandType Property
for a more detailed explanation of the
four constants in this list.

Object Execute Method Remarks (ADO Command Object)

Using the Execute method on a Command
object executes the query specified in the CommandText property of the
object. If the CommandText property specifies a row-returning query, any
results the execution generates are stored in a new ADO Recordset
Object
. If the command is not a row-returning query, the provider returns a
closed Recordset object. Some application languages allow you to ignore
this return value if no recordset is desired.

If the query has parameters, the current
values for the Command object’s parameters are used unless you override
these with parameter values passed with the Execute call. You can
override a subset of the parameters by omitting new values for some of the
parameters when calling the Execute method. The order in which you
specify the parameters is the same order in which the method passes them. For example,
if there were four (or more) parameters and you wanted to pass new values for
only the first and fourth parameters, you would pass Array(var1,,,var4) as the Parameters
argument.

Note iconNote

Output parameters will not return correct values when
passed in the Parameters argument.

Object Execute Method Return Values (ADO Command Object)

Returns a Recordset object
reference.

Object Execute Method Examples (ADO Command Object)

This VBScript example demonstrates the Execute
method when run from both a Command object and an ADO Connection
Object
. It also uses the ADO
Recordset Object Requery Method
to retrieve current data in a recordset,
and the ADO
Collections Clear Method
to clear the contents of the ADO Errors
Collection
. The ExecuteCommand and PrintOutput procedures are required for
this procedure to run.

<!-- #Include file="ADOVBS.INC" -->
<HTML><HEAD>
<TITLE>ADO 1.5 Execute Method</TITLE></HEAD>
<BODY>
<FONT FACE="MS SANS SERIF" SIZE=2>
<Center><H3>ADO Execute Method</H3><H4>Recordset Retrieved Using Connection Object</H4>
<TABLE WIDTH=600 BORDER=0>
<TD VALIGN=TOP ALIGN=LEFT COLSPAN=3><FONT SIZE=2>
<!--- Recordsets retrieved using Execute method of Connection and Command Objects-->
<%
Set OBJdbConnection = Server.CreateObject("ADODB.Connection")
OBJdbConnection.Open "AdvWorks"
SQLQuery = "SELECT * FROM Customers"
'First Recordset RSCustomerList
Set RSCustomerList = OBJdbConnection.Execute(SQLQuery)
Set OBJdbCommand = Server.CreateObject("ADODB.Command")
Set OBJdbCommand.ActiveConnection = OBJdbConnection
SQLQuery2 = "SELECT * From Products"
OBJdbCommand.CommandText = SQLQuery2
Set RsProductList = OBJdbCommand.Execute
%>
<TABLE COLSPAN=8 CELLPADDING=5 BORDER=0>
<!-- BEGIN column header row for Customer Table-->
<TR><TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Company Name</FONT>
</TD>
<TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Contact Name</FONT>
</TD>
<TD ALIGN=CENTER WIDTH=150 BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>E-mail address</FONT>
</TD>
<TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>City</FONT>
</TD>
<TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>State/Province</FONT>
</TD></TR>
<!--Display ADO Data from Customer Table-->
<% Do While Not RScustomerList.EOF %>
<TR>
<TD BGCOLOR="f7efde" ALIGN=CENTER>
<FONT STYLE="ARIAL NARROW" SIZE=1>
<%= RSCustomerList("CompanyName")%>
</FONT></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER>
<FONT STYLE="ARIAL NARROW" SIZE=1>
<%= RScustomerList("ContactLastName") & ", " %>
<%= RScustomerList("ContactFirstName") %>
</FONT></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER>
<FONT STYLE="ARIAL NARROW" SIZE=1>
<%= RScustomerList("ContactLastName")%>
</FONT></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER>
<FONT STYLE="ARIAL NARROW" SIZE=1>
<%= RScustomerList("City")%>
</FONT></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER>
<FONT STYLE="ARIAL NARROW" SIZE=1>
<%= RScustomerList("StateOrProvince")%>
</FONT></TD>
</TR>
<!-Next Row = Record Loop and add to html table-->
<%
RScustomerList.MoveNext
Loop
RScustomerList.Close
%>
</TABLE><HR>
<H4>Recordset Retrieved Using Command Object</H4>
<TABLE COLSPAN=8 CELLPADDING=5 BORDER=0>
<!-- BEGIN column header row for Product List Table-->
<TR><TD ALIGN=CENTER BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Product Type</FONT>
</TD>
<TD ALIGN=CENTER BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Product Name</FONT>
</TD>
<TD ALIGN=CENTER WIDTH=350 BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Product Description</FONT>
</TD>
<TD ALIGN=CENTER BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Unit Price</FONT>
</TD></TR>
<!-- Display ADO Data Product List-->
<% Do While Not RsProductList.EOF %>
<TR>
<TD BGCOLOR="f7efde" ALIGN=CENTER>
<FONT STYLE="ARIAL NARROW" SIZE=1>
<%= RsProductList("ProductType")%>
</FONT></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER>
<FONT STYLE="ARIAL NARROW" SIZE=1>
<%= RsProductList("ProductName")%>
</FONT></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER>
<FONT STYLE="ARIAL NARROW" SIZE=1>
<%= RsProductList("ProductDescription")%>
</FONT></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER>
<FONT STYLE="ARIAL NARROW" SIZE=1>
<%= RsProductList("UnitPrice")%>
</FONT></TD>
<!-- Next Row = Record -->
<%
RsProductList.MoveNext
Loop
'Remove objects from memory to free resources
RsProductList.Close
OBJdbConnection.Close
Set ObJdbCommand = Nothing
Set RsProductList = Nothing
Set OBJdbConnection = Nothing
%>
</TABLE></FONT></Center></BODY></HTML>

ADO Command
Object Properties


Property

Description

ActiveConnection

The Connection object to which
the specified Command object currently belongs.

CommandText

The text of a command that you want
to issue against a provider.

CommandTimeout

How long to wait while executing a
command before terminating the command and issuing an error.

CommandType

The type of Command object.

Name

The name of a specific Command

object. This property is not currently supported on UNIX.

Prepared

Whether or not to save a compiled
version of a command before execution. This property is not currently
supported on UNIX.

State

The current state of the Command
object. This property is not currently supported on UNIX.

In this section:

ADO
Command Object ActiveConnection Property

ADO
Command Object CommandText Property

ADO
Command Object CommandTimeout Property

ADO
Command Object CommandType Property

ADO
Command Object Name Property

ADO
Command Object Prepared Property

ADO
Command Object State Property

ADO Command
Object ActiveConnection Property

Specifies to which Connection
object the specified Command object currently belongs.

ActiveConnection Property Return Values (ADO Command Object)

Sets or returns a String containing
the definition for a connection or a Connection object. Default is a Null
object reference.

ActiveConnection Property Remarks (ADO Command Object)

Use the ActiveConnection property
to determine the Connection object over which the specified Command
object will execute.

For Command objects, the ActiveConnection

property is read/write. If you attempt to call the ADO
Command Object Execute Method
on a Command object before setting
this property to an open ADO Connection
Object
or valid connection string, an error occurs. Setting the ActiveConnection
property to Nothing disassociates the Command object from the
current Connection and causes the provider to release any associated
resources on the data source. You can then associate the Command object
with the same or another Connection object. Some providers allow you to
change the property setting from one Connection to another, without
having to first set the property to Nothing.

If the ADO Parameters
Collection
of the Command object contains parameters supplied by the
provider, the collection is cleared if you set the ActiveConnection
property to Nothing or to another Connection object. If you
manually create ADO Parameter
Object
objects and use them to fill the Parameters collection
of the Command object, setting the ActiveConnection property to Nothing

or to another Connection object leaves the Parameters collection
intact.

Closing the Connection object with
which a Command object is associated sets the ActiveConnection

property to Nothing. Setting this property to a closed Connection
object generates an error.

ActiveConnection Property Example (ADO Command Object)

This Visual Basic example uses the ActiveConnection,
ADO Command Object CommandText Property, CommandTimeout,
ADO
Command Object CommandType Property
, ADO
Field Object ActualSize Property
, and ADO
Parameter Object Direction Property
properties to execute a stored
procedure:

Public Sub ActiveConnectionX()
Dim cnn1 As ADODB.Connection
Dim cmdByRoyalty As ADODB.Command
Dim prmByRoyalty As ADODB.Parameter
Dim rstByRoyalty As ADODB.Recordset
Dim rstAuthors As ADODB.Recordset
Dim intRoyalty As Integer
Dim strAuthorID As String
Dim strCnn As String
` Define a command object for a stored procedure.
Set cnn1 = New ADODB.Connection
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
cnn1.Open strCnn
Set cmdByRoyalty = New ADODB.Command
Set cmdByRoyalty.ActiveConnection = cnn1
cmdByRoyalty.CommandText = "byroyalty"
cmdByRoyalty.CommandType = adCmdStoredProc
cmdByRoyalty.CommandTimeout = 15
` Define the stored procedure's input parameter.
intRoyalty = Trim(InputBox( _
"Enter royalty:"))
Set prmByRoyalty = New ADODB.Parameter
prmByRoyalty.Type = adInteger
prmByRoyalty.Size = 3
prmByRoyalty.Direction = adParamInput
prmByRoyalty.Value = intRoyalty
cmdByRoyalty.Parameters.Append prmByRoyalty
` Create a recordset by executing the command.
Set rstByRoyalty = cmdByRoyalty.Execute()
` Open the Authors table to get author names for display.
Set rstAuthors = New ADODB.Recordset
rstAuthors.Open "authors", strCnn, , , adCmdTable
` Print current data in the recordset, adding
` author names from Authors table.
Debug.Print "Authors with " & intRoyalty & _
" percent royalty"
Do While Not rstByRoyalty.EOF
strAuthorID = rstByRoyalty!au_id
Debug.Print , rstByRoyalty!au_id & ", ";
rstAuthors.Filter = "au_id = '" & strAuthorID & "'"
Debug.Print rstAuthors!au_fname & " " & _
rstAuthors!au_lname
rstByRoyalty.MoveNext
Loop
rstByRoyalty.Close
rstAuthors.Close
cnn1.Close
End Sub
ADO Command
Object CommandText Property

Contains the text of a command that you
want to issue against a provider.

CommandText Property Return Values

Sets or returns a String value
containing a provider command, such as an SQL statement, a table name, or a
stored procedure call. Default is “” (zero-length string).

CommandText Property Remarks

Use the CommandText property to set
or return the text of a Command object. Usually, this will be an SQL
statement, but can also be any other type of command statement recognized by
the provider, such as a stored procedure call. An SQL statement must be of the
particular dialect or version supported by the provider’s query processor.

If the ADO
Command Object Prepared Property
of the Command object is set to True
and the Command object is bound to an open connection when you set the CommandText
property, ADO prepares the query (that is, a compiled form of the query is
stored by the provider) when you call the ADO
Command Object Execute Method
or ADO
Connection Object Open Method
methods. The Prepared property
is not currently supported on UNIX.

Depending on the ADO
Command Object CommandType Property
setting, ADO may alter the CommandText
property. You can read the CommandText property at any time to see the
actual command text that ADO
will use during execution.

CommandText Property Example

See the ActiveConnection property.

ADO Command
Object CommandTimeout Property

How long to wait while executing a command
before terminating the attempt and generating an error.

CommandTimeout Property Return Values (ADO Command Object)

Sets or returns a Long value that
specifies, in seconds, how long to wait for a command to execute. Default is
30.

CommandTimeout Property Remarks (ADO Command Object)

Use the CommandTimeout property on
a Command object to allow the cancellation of an ADO
Command Object Execute Method
call due to delays from network traffic or
heavy server use. If the interval set in the CommandTimeout property
elapses before the command completes execution, an error occurs and ADO cancels the command.
If you set the property to zero, ADO

will wait indefinitely until the execution is complete. Make sure the provider
and data source to which you are writing code supports the CommandTimeout
functionality.

The CommandTimeout setting on a Connection
object has no effect on the CommandTimeout setting on a Command

object on the same Connection; that is, the Command object’s CommandTimeout
property does not inherit the value of the Connection object’s CommandTimeout
value.

CommandTimeout Property Examples (ADO Command Object)

See the ActiveConnection property.

ADO Command
Object CommandType Property

The type of a Command object.

CommandType Property Return Values (ADO Command Object)

Sets or returns one of the following CommandTypeEnum
values:

Constant

Description

adCmdText

Evaluates CommandText as a
textual definition of a command.

adCmdTable

Evaluates CommandText as a
table name.

adCmdStoredProc

Evaluates CommandText as a
stored procedure.

adCmdUnknown

(Default) The type of command in the
CommandText property is not known.

CommandType Property Remarks (ADO Command Object)

Use the CommandType property to
optimize evaluation of the ADO
Command Object CommandText Property
.

If the CommandType property value
equals adCmdUnknown (the default value), you may experience diminished
performance because ADO
must make calls to the provider to determine if the CommandText property
is an SQL statement, a stored procedure, or a table name. If you know what type
of command you’re using, setting the CommandType property instructs ADO to go directly to the
relevant code. If the CommandType property does not match the type of
command in the CommandText property, an error occurs when you call the ADO
Command Object Execute Method
.

CommandType Property Example (ADO Command Object)

See the ActiveConnection property.

ADO Command
Object Name Property

The name of an object. This property
is not currently supported on UNIX.

Name Property Return Values (ADO Command Object)

Sets or returns a String value. The
value is read/write.

Name Property Remarks (ADO Command Object)

Use the Name property to assign a
name to or retrieve the name of a Command object.

ADO Command
Object Prepared Property

Determines whether or not the provider
saves a compiled version of a command before execution. This property is
not currently supported on UNIX.

Prepared Property Return Values

Sets or returns a Boolean value.

Prepared Property Remarks

Use the Prepared property to have
the provider save a prepared (or compiled) version of the query specified in
the ADO
Command Object CommandText Property
before a Command object’s first
execution. This may slow a command’s first execution, but once the provider
compiles a command, the provider will use the compiled version of the command
for any subsequent executions, which will result in improved performance.

If the property is False, the
provider will execute the Command object directly without creating a
compiled version.

If the provider does not support command
preparation, it may return an error as soon as this property is set to True.
If it does not return an error, it simply ignores the request to prepare the
command and sets the Prepared property to False.

Prepared Property Example

This Visual Basic example demonstrates the
Prepared property by opening two Command objects: one prepared
and one not prepared.

Public Sub PreparedX()
Dim cnn1 As ADODB.Connection
Dim cmd1 As ADODB.Command
Dim cmd2 As ADODB.Command
Dim strCnn As String
Dim strCmd As String
Dim sngStart As Single
Dim sngEnd As Single
Dim sngNotPrepared As Single
Dim sngPrepared As Single
Dim intLoop As Integer
` Open a connection.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set cnn1 = New ADODB.Connection
cnn1.Open strCnn
` Create two command objects for the same
` command -- one prepared and one not prepared.
strCmd = "SELECT title, type FROM titles ORDER BY type"
Set cmd1 = New ADODB.Command
Set cmd1.ActiveConnection = cnn1
cmd1.CommandText = strCmd
Set cmd2 = New ADODB.Command
Set cmd2.ActiveConnection = cnn1
cmd2.CommandText = strCmd
cmd2.Prepared = True
` Set timer, then execute unprepared command 20 times.
sngStart = Timer
For intLoop = 1 To 20
cmd1.Execute
Next intLoop
sngEnd = Timer
sngNotPrepared = sngEnd - sngStart
` Reset the timer, then execute the prepared
` command 20 times.
sngStart = Timer
For intLoop = 1 To 20
cmd2.Execute
Next intLoop
sngEnd = Timer
sngPrepared = sngEnd - sngStart
` Display performance results.
MsgBox "Performance Results:" & vbCr & _
" Not Prepared: " & Format(sngNotPrepared, _
"##0.000") & " seconds" & vbCr & _
" Prepared: " & Format(sngPrepared, _
"##0.000") & " seconds"
cnn1.Close
End Sub
ADO Command Object
State Property

Describes the current state of an object.
This property is not currently supported on UNIX.

State Property Return Values (ADO Command Object)

Sets or returns a Long value that
can be one of the following constants:

Constant

Description

adStateClosed

The object is closed. Default.

adStateOpen

The object is open.

State Property Remarks (ADO Command Object)

You can use the State property to
determine the current state of a given object at any time.

ADO Command
Object Remarks


A Command object is used to query a
database, return records in a ADO Recordset
Object
, execute bulk operations, or manipulate the structure of a database.
It is a definition of a specific command that you intend to execute against a
data source.

The collections, methods, and properties of
a Command object are used to:

·
Define the executable text of the command (for
example, an SQL statement) with the ADO
Command Object CommandText Property
.

·
Define parameterized queries or stored procedure
arguments with ADO
Parameter Object
objects and the ADO Parameters
Collection
.

·
Execute a command and return a ADO Recordset
Object
if appropriate with the ADO
Command Object Execute Method
.

·
Specify the type of command with the ADO
Command Object CommandType Property
prior to execution to optimize
performance.

·
Set the number of seconds a provider will wait
for a command to execute with the CommandTimeout property.

·
Associate an open connection with a Command
object by setting its property.

·

Set the ADO
Command Object Name Property
to identify the Command object as a
method on the associated ADO Connection
Object
.

·
Pass a Command object to the ADO
Recordset Object Source Property
of an ADO Recordset
Object
in order to obtain data.

To execute a query without using a Command
object, pass a query string to the ADO
Connection Object Execute Method
of an ADO Connection
Object
or to the ADO
Recordset Object Open Method
of an ADO Recordset
Object
. However, a Command object is required when you want to
retain the command text and re-execute it, or use query parameters.

To create a Command object
independently of a previously defined Connection object, set its ActiveConnection
property to a valid connection string. ADO still creates a Connection object,
but it doesn’t assign that object to an object variable. However, if you are
associating multiple Command objects with the same connection, you
should explicitly create and open a Connection object; this assigns the Connection

object to an object variable. If you do not set the Command object’s ActiveConnection
property to this object variable, ADO
creates a new Connection object for each Command object, even if
you use the same connection string.

To execute a Command, simply call
it by its ADO
Command Object Name Property
on the associated Connection object.
The Command must have its ActiveConnection property set to the Connection

object. If the Command has parameters, pass values for them as arguments
to the method.

Depending on the functionality of the
provider, some Command collections, methods, or properties may generate
an error when referenced.

ADO
Connection Object


A Connection object represents an
open connection to a data source.

In this section:

ADO
Connection Object Collections

ADO
Connection Object Methods

ADO
Connection Object Properties

ADO
Connection Object Remarks

ADO
Connection Object Collections


Collection

Description

Errors

Contains all stored Error
objects that pertain to an ADO
operation.

Properties

All Property objects for a
specific instance of a Connection object. This collection is not
currently supported on UNIX.

ADO
Connection Object Methods


Method

Description

Close

Closes an open Connection

object and any dependent objects.

Execute

Executes the specified query, SQL
statement, stored procedure, or provider-specified text.

Open

Opens a connection to a data source.

OpenSchema

Obtains database schema information
from the provider. This method is not currently supported on UNIX.

BeginTrans, CommitTrans, and
RollbackTrans

Cancels any changes made during the
current transaction and ends the transaction. It may also start a new
transaction.

In this section:

ADO
Connection Object Close Method

ADO
Connection Object Execute Method

ADO
Connection Object OpenSchema Method

ADO
Connection Object Open Method

ADO
Connection Object BeginTrans, CommitTrans, and RollbackTrans Methods

ADO
Connection Object Close Method

Closes an open object and any dependent
objects.

Close Method Syntax (ADO Connection Object)
object.Close
Close Method Remarks (ADO Connection Object)

Use the Close method to close a Connection

object to free any associated system resources. Closing an object does not
remove it from memory; you may change its property settings and open it again
later. To completely eliminate an object from memory, set the object variable
to Nothing.

Using the Close method to close a Connection
object also closes any active Recordset objects associated with the
connection. An ADO
Command Object
associated with the Connection object you are closing
will persist, but it will no longer be associated with a Connection

object; that is, its ActiveConnection property will be set to Nothing.
Also, the Command object’s ADO Parameters
Collection
will be cleared of any provider-defined parameters.

You can later call the ADO
Connection Object Open Method
to reestablish the connection to the same or
another data source. While the Connection object is closed, calling any
methods that require an open connection to the data source generates an error.

Closing a Connection object while
there are open ADO
Recordset Object
objects on the connection rolls back any pending
changes in all of the Recordset objects. Explicitly closing a Connection
object (calling the Close method) while a transaction is in progress
generates an error. If a Connection object falls out of scope while a
transaction is in progress, ADO

automatically rolls back the transaction.

Close Method Examples (ADO Connection Object)

This VBScript example uses the Open
and Close methods on both Recordset and Connection objects
that have been opened.

<!-- #Include file="ADOVBS.INC" -->
<HTML><HEAD>
<TITLE>ADO 1.5 Open Method</TITLE>
</HEAD><BODY>
<FONT FACE="MS SANS SERIF" SIZE=2>
<Center><H3>ADO Open Method</H3>
<TABLE WIDTH=600 BORDER=0>
<TD VALIGN=TOP ALIGN=LEFT COLSPAN=3><FONT SIZE=2>
<!--- ADO Connection used to create 2 recordsets-->
<%
Set OBJdbConnection = Server.CreateObject("ADODB.Connection")
OBJdbConnection.Open "AdvWorks"
SQLQuery = "SELECT * FROM Customers"
'First Recordset RSCustomerList
Set RSCustomerList = OBJdbConnection.Execute(SQLQuery)
'Second Recordset RsProductist
Set RsProductList = Server.CreateObject("ADODB.Recordset")
RsProductList.CursorType = adOpenDynamic
RsProductList.LockType = adLockOptimistic
RsProductList.Open "Products", OBJdbConnection
%>
<TABLE COLSPAN=8 CELLPADDING=5 BORDER=0>
<!-- BEGIN column header row for Customer Table-->
<TR><TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Company Name</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Contact Name</FONT></TD>
<TD ALIGN=CENTER WIDTH=150 BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>E-mail address</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>City</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>State/Province</FONT></TD></TR>
<!--Display ADO Data from Customer Table-->
<% Do While Not RScustomerList.EOF %>
<TR><TD BGCOLOR="f7efde" ALIGN=CENTER>
<FONT STYLE="ARIAL NARROW" SIZE=1>
<%= RSCustomerList("CompanyName")%>
</FONT></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER>
<FONT STYLE="ARIAL NARROW" SIZE=1>
<%= RScustomerList("ContactLastName") & ", " %>
<%= RScustomerList("ContactFirstName") %>
</FONT></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER>
<FONT STYLE="ARIAL NARROW" SIZE=1>
<%= RScustomerList("ContactLastName")%>
</FONT></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER>
<FONT STYLE="ARIAL NARROW" SIZE=1>
<%= RScustomerList("City")%>
</FONT></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER>
<FONT STYLE="ARIAL NARROW" SIZE=1>
<%= RScustomerList("StateOrProvince")%>
</FONT></TD></TR>
<!-Next Row = Record Loop and add to html table-->
<%
RScustomerList.MoveNext
Loop
RScustomerList.Close
OBJdbConnection.Close
%>
</TABLE>
<HR>
<TABLE COLSPAN=8 CELLPADDING=5 BORDER=0>
<!-- BEGIN column header row for Product List Table-->
<TR><TD ALIGN=CENTER BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Product Type</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Product Name</FONT></TD>
<TD ALIGN=CENTER WIDTH=350 BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Product Description</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Unit Price</FONT></TD></TR>
<!-- Display ADO Data Product List-->
<% Do While Not RsProductList.EOF %>
<TR> <TD BGCOLOR="f7efde" ALIGN=CENTER>
<FONT STYLE="ARIAL NARROW" SIZE=1>
<%= RsProductList("ProductType")%>
</FONT></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER>
<FONT STYLE="ARIAL NARROW" SIZE=1>
<%= RsProductList("ProductName")%>
</FONT></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER>
<FONT STYLE="ARIAL NARROW" SIZE=1>
<%= RsProductList("ProductDescription")%>
</FONT></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER>
<FONT STYLE="ARIAL NARROW" SIZE=1>
<%= RsProductList("UnitPrice")%>
</FONT></TD>
<!-- Next Row = Record -->
<%
RsProductList.MoveNext
Loop
'Remove Objects from Memory Freeing
Set RsProductList = Nothing
Set OBJdbConnection = Nothing
%>
</TABLE></FONT></Center></BODY></HTML>
ADO
Connection Object Execute Method

Executes the specified query, SQL
statement, stored procedure, or provider-specific text.

Execute Method Syntax (ADO Connection Object)

For a non-row-returning command string:

connection.Execute CommandText, RecordsAffected, Options

For a row-returning command string:

Set recordset = connection.Execute (
 CommandText, RecordsAffected, Options)
Execute Method Parameters (ADO Connection Object)

CommandText

A String containing the SQL
statement, table name, stored procedure, or provider-specific text to execute.

RecordsAffected

An optional Long variable to which
the provider returns the number of records that the operation affected.

Options

An optional Long value that
indicates how the provider should evaluate the CommandText argument:

Constant

Description

adCmdText

The provider should evaluate CommandText
as a textual definition of a command.

adCmdTable

The provider should evaluate CommandText

as a table name.

adCmdStoredProc

The provider should evaluate CommandText
as a stored procedure.

adCmdUnknown

The type of command in the CommandText
argument is not known.

See the ADO
Command Object CommandType Property
for a more detailed explanation of the
four constants in this list.

Execute Method Return Values (ADO Connection Object)

Returns an ADO Recordset
Object
reference.

Execute Method Remarks (ADO Connection Object)

Using the Execute method on a Connection

object executes whatever query you pass to the method in the CommandText
argument on the specified connection. If the CommandText argument
specifies a row-returning query, any results the execution generates are stored
in a new Recordset object. If the command is not a row-returning query,
the provider returns a closed Recordset object.

The returned Recordset object is
always a read-only, forward-only cursor. If you need a Recordset object
with more functionality, first create a Recordset object with the
desired property settings, then use the Recordset object’s ADO
Recordset Object Open Method
to execute the query and return the desired
cursor type.

The contents of the CommandText
argument are specific to the provider and can be standard SQL syntax or any
special command format that the provider supports.

Execute Method Examples (ADO Connection Object)

See the Command ADO
Command Object Execute Method
.

ADO
Connection Object OpenSchema Method

Obtains database schema information from
the provider.

OpenSchema Method Syntax
Set recordset = connection.OpenSchema (QueryType,
 Criteria, SchemaID)
OpenSchema Method Parameters

QueryType

The type of schema query to run. Can be
any of the constants listed below.

Criteria

Optional array of query constraints for
each QueryType option, as listed below:

QueryType values

Criteria
values

adSchemaAsserts

CONSTRAINT_CATALOG

CONSTRAINT_SCHEMA

CONSTRAINT_NAME

Not currently supported on UNIX.

adSchemaCatalogs

CATALOG_NAME

Not currently supported on UNIX.

asSchemaCharacterSets

CHARACTER_SET_CATALOG

CHARACTER_SET_SCHEMA

CHARACTER_SET_NAME

Not currently supported on UNIX.

adSchemaCheckConstraints

CONSTRAINT_CATALOG

CONSTRAINT_SCHEMA

CONSTRAINT_NAME

Not currently supported on UNIX.

adSchemaCollations

COLLATION_CATALOG

COLLATION_SCHEMA

COLLATION_NAME

Not currently supported on UNIX.

adSchemaColumnDomainUsage

DOMAIN_CATALOG

DOMAIN_SCHEMA

DOMAIN_NAME

COLUMN_NAME

Not currently supported on UNIX.

adSchemaColumnPrivileges

TABLE_CATALOG

TABLE_SCHEMA

TABLE_NAME

COLUMN_NAME

GRANTOR

GRANTEE

Not currently supported on UNIX.

adSchemaColumns

TABLE_CATALOG

TABLE_SCHEMA

TABLE_NAME

Supported on UNIX.

adSchemaConstraintTableUsage

TABLE_CATALOG

TABLE_SCHEMA

TABLE_NAME

COLUMN_NAME

Not currently supported on UNIX.

adSchemaForeignKeys

PK_TABLE_CATALOG

PK_TABLE_SCHEMA

PK_TABLE_NAME

FK_TABLE_CATALOG

FK_TABLE_SCHEMA

FK_TABLE_NAME

Not currently supported on UNIX.

adSchemaIndexes

TABLE_CATALOG

TABLE_SCHEMA

INDEX_NAME

TYPE

TABLE_NAME

Not currently supported on UNIX.

adSchemaKeyColumnUsage

CONSTRAINT_CATALOG

CONSTRAINT_SCHEMA

CONSTRAINT_NAME

TABLE_CATALOG

TABLE_SCHEMA

TABLE_NAME

COLUMN_NAME

Not currently supported on UNIX.

adSchemaPrimaryKeys

PK_TABLE_CATALOG

PK_TABLE_SCHEMA

PK_TABLE_NAME

Not currently supported on UNIX.

adSchemaProcedureColumns

PROCEDURE_CATALOG

PROCEDURE_SCHEMA

PROCEDURE_NAME

COLUMN_NAME

Not currently supported on UNIX.

adSchemaProcedures

PROCEDURE_CATALOG

PROCEDURE_SCHEMA

PROCEDURE_NAME

PARAMETER_TYPE

Not currently supported on UNIX.

adSchemaProviderSpecific

see Remarks

Not currently supported on UNIX.

adSchemaProviderTypes

DATA_TYPE

BEST_MATCH

Supported on UNIX.

adSchemaReferentialConstraints

CONSTRAINT_CATALOG

CONSTRAINT_SCHEMA

CONSTRAINT_NAME

Not currently supported on UNIX.

adSchemaSchemata

CATALOG_NAME

SCHEMA_NAME

SCHEMA_OWNER

Not currently supported on UNIX.

adSchemaSQLLanguages

<none>

Not currently supported on UNIX.

adSchemaStatistics

TABLE_CATALOG

TABLE_SCHEMA

TABLE_NAME

Not currently supported on UNIX.

adSchemaTableConstraints

CONSTRAINT_CATALOG

CONSTRAINT_SCHEMA

CONSTRAINT_NAME

TABLE_CATALOG

TABLE_SCHEMA

TABLE_NAME

CONSTRAINT_TYPE

Not currently supported on UNIX.

adSchemaTablePrivileges

TABLE_CATALOG

TABLE_SCHEMA

TABLE_NAME

Not currently supported on UNIX.

adSchemaTables

TABLE_CATALOG

TABLE_SCHEMA

TABLE_NAME

TABLE_TYPE

Supported on UNIX.

adSchemaTranslations

TRANSLATION_CATALOG

TRANSLATION_SCHEMA

TRANSLATION_NAME

Not currently supported on UNIX.

adSchemaUsagePrivileges

OBJECT_CATALOG

OBJECT_SCHEMA

OBJECT_NAME

OBJECT_TYPE

GRANTOR

GRANTEE

Not currently supported on UNIX.

adSchemaViewColumnUsage

VIEW_CATALOG

VIEW_SCHEMA

VIEW_NAME

Not currently supported on UNIX.

adSchemaViewTableUsage

VIEW_CATALOG

VIEW_SCHEMA

VIEW_NAME

Not currently supported on UNIX.

adSchemaViews

TABLE_CATALOG

TABLE_SCHEMA

TABLE_NAME

Not currently supported on UNIX.

SchemaID

The GUID for a provider-schema schema
query is not defined by the OLE DB 1.1 specification. This parameter is
required if QueryType is set to adSchemaProviderSpecific;
otherwise, it is not used.

OpenSchema Method Return Values

Returns an ADO Recordset
Object
that contains schema information.

OpenSchema Method Remarks

The OpenSchema method returns
information about the data source, such as information about the tables on the
server and the columns in the tables.

The Criteria argument is an array
of values that can be used to limit the results of a schema query. Each schema
query has a different set of parameters that it supports. The actual schemas
are defined by the OLE DB specification under the “IDBSchemaRowset”
interface. The ones supported in ADO
1.5 are listed above.

The constant adSchemaProviderSpecific
is used for the QueryType argument if the provider defines its own
non-standard schema queries outside those listed above. When this constant is
used, the SchemaID argument is required to pass the GUID of the schema
query to execute. If QueryType is set to adSchemaProviderSpecific

but SchemaID is not provided, an error will result.

Providers are not required to support all
of the OLE DB standard schema queries. Specifically, only adSchemaTables,
adSchemaColumns and adSchemaProviderTypes are required by the OLE
DB specification. However, the provider is not required to support the Criteria

constraints listed above for those schema queries.

OpenSchema Method Example

This Visual Basic example uses the OpenSchema
method to display the name and type of each table in the Pubs database.

Public Sub OpenSchemaX()
Dim cnn1 As ADODB.Connection
Dim rstSchema As ADODB.Recordset
Dim strCnn As String
Set cnn1 = New ADODB.Connection
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
cnn1.Open strCnn
Set rstSchema = cnn1.OpenSchema(adSchemaTables)
Do Until rstSchema.EOF
Debug.Print "Table name: " & _
rstSchema!TABLE_NAME & vbCr & _
"Table type: " & rstSchema!TABLE_TYPE & vbCr
rstSchema.MoveNext
Loop
rstSchema.Close
cnn1.Close
End Sub
ADO
Connection Object Open Method

Opens a connection to a data source.

Open Method Syntax (ADO Connection Object)
connection.Open ConnectionString, UserID, Password
Open Method Parameters (ADO Connection Object)

ConnectionString

An optional String containing
connection information. See the ConnectionString property for details on
valid settings.

UserID

An optional String containing a
user name to use when establishing the connection.

Password

An optional String containing a
password to use when establishing the connection.

Open Method Remarks (ADO Connection Object)

Using the Open method on a Connection
object establishes the physical connection to a data source. After this method
successfully completes, the connection is live and you can issue commands
against it and process results.

Use the optional ConnectionString
argument to specify a connection string containing a series of argument
= value statements separated by semicolons. The ConnectionString property
automatically inherits the value used for the ConnectionString

argument. Therefore, you can either set the ConnectionString property of
the Connection object before opening it, or use the ConnectionString
argument to set or override the current connection parameters during the Open
method call.

If you pass user and password information
both in the ConnectionString argument and in the optional UserID

and Password arguments, the results may be unpredictable; you should
only pass such information in either the ConnectionString argument or
the UserID and Password arguments.

When you have concluded your operations
over an open Connection, use the ADO
Connection Object Close Method
to free any associated system resources.
Closing an object does not remove it from memory; you may change its property
settings and use the Open method to open it again later. To completely
eliminate an object from memory, set the object variable to Nothing.

Open Method Examples (ADO Connection Object)

See the ADO
Connection Object Close Method
.

ADO
Connection Object BeginTrans, CommitTrans, and RollbackTrans Methods

The transaction methods manage transaction
processing within a Connection object.

These transaction methods are summarized
as follows:

Method

Description

BeginTrans

Begins a new transaction

CommitTrans

Saves any changes and ends the
current transaction. It may also start a new transaction.

RollbackTrans

Cancels any changes made during the
current transaction and ends the transaction. It may also start a new
transaction.

BeginTrans, CommitTrans, and RollbackTrans Methods
Syntax
level = connection.BeginTrans()
connection.BeginTrans
connection.CommitTrans
connection.RollbackTrans
BeginTrans, CommitTrans, and RollbackTrans Methods
Remarks

Use these methods with a Connection
object when you want to save or cancel a series of changes made to the source
data as a single unit. For example, to transfer money between accounts, you
subtract an amount from one and add the same amount to the other. If either
update fails, the accounts no longer balance. Making these changes within an
open transaction ensures either all or none of the changes goes through.

Not all providers support transactions.
Check that the provider-defined property “Transaction DDL” appears in
the Connection object’s ADO Properties
Collection
, indicating that the provider supports transactions. If the
provider does not support transactions, calling one of these methods will
return an error.

Once you call the BeginTrans
method, the provider will no longer instantaneously commit any changes you make
until you call CommitTrans or RollbackTrans to end the
transaction.

For providers that support nested
transactions, calling the BeginTrans method within an open transaction
starts a new, nested transaction. The return value indicates the level of
nesting: a return value of “1” indicates you have opened a top-level
transaction (that is, the transaction is not nested within another
transaction), “2” indicates that you have opened a second-level
transaction (a transaction nested within a top-level transaction), and so
forth. Calling CommitTrans or RollbackTrans affects only the most
recently opened transaction; you must close or rollback the current transaction
before you can resolve any higher-level transactions.

Calling the CommitTrans method
saves changes made within an open transaction on the connection and ends the
transaction. Calling the RollbackTrans method reverses any changes made
within an open transaction and ends the transaction. Calling either method when
there is no open transaction generates an error.

Depending on the Connection
object’s ADO
Connection Object Attributes Property
, calling either the CommitTrans

or RollbackTrans methods may automatically start a new transaction. If
the Attributes property is set to adXactCommitRetaining, the
provider automatically starts a new transaction after a CommitTrans
call. If the Attributes property is set to adXactAbortRetaining,
the provider automatically starts a new transaction after a RollbackTrans

call.

BeginTrans, CommitTrans, and RollbackTrans Methods
Return Value

BeginTrans can be called as a
function that returns a Long variable indicating the nesting level of
the transaction.

BeginTrans, CommitTrans, and RollbackTrans Methods
Examples

This Visual Basic example changes the book
type of all psychology books in the Titles table of the database. After
the BeginTrans method starts a transaction that isolates all the changes
made to the Titles table, the CommitTrans method saves the
changes. Notice that you can use the RollbackTrans method to undo
changes that you saved using the ADO
Recordset Object Update Method
.

Public Sub BeginTransX()
Dim cnn1 As ADODB.Connection
Dim rstTitles As ADODB.Recordset
Dim strCnn As String
Dim strTitle As String
Dim strMessage As String
` Open connection.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set cnn1 = New ADODB.Connection
cnn1.Open strCnn
` Open titles table.
Set rstTitles = New ADODB.Recordset
rstTitles.CursorType = adOpenDynamic
rstTitles.LockType = adLockPessimistic
rstTitles.Open "titles", cnn1, , , adCmdTable
rstTitles.MoveFirst
cnn1.BeginTrans
` Loop through recordset and ask user if she wants
` to change the type for a specified title.
Do Until rstTitles.EOF
If Trim(rstTitles!Type) = "psychology" Then
strTitle = rstTitles!Title
strMessage = "Title: " & strTitle & vbCr & _
"Change type to self help?"
` Change the title for the specified employee.
If MsgBox(strMessage, vbYesNo) = vbYes Then
rstTitles!Type = "self_help"
rstTitles.Update
End If
End If
rstTitles.MoveNext
Loop
` Ask if the user wants to commit to all the
` changes made above.
If MsgBox("Save all changes?", vbYesNo) = vbYes Then
cnn1.CommitTrans
Else
cnn1.RollbackTrans
End If
` Print current data in recordset.
rstTitles.Requery
rstTitles.MoveFirst
Do While Not rstTitles.EOF
Debug.Print rstTitles!Title & " - " & rstTitles!Type
rstTitles.MoveNext
Loop
' Restore original data
rstTitles.MoveFirst
Do Until rstTitles.EOF
If Trim(rstTitles!Type) = "self_help" Then
rstTitles!Type = "psychology"
rstTitles.Update
End If
rstTitles.MoveNext
Loop
rstTitles.Close
cnn1.Close
End Sub

ADO
Connection Object Properties


Property

Description

Attributes

One or more characteristics of an
object.

CommandTimeout

How long to wait while executing a
command before terminating the command and issuing and error.

ConnectionString

Contains the information used to
establish a connection to a data source.

ConnectionTimeout

How long to wait while establishing
a connection before terminating the attempt and issuing and error.

CursorLocation

The location of the cursor engine in
a recordset.

DefaultDatabase

The default database for the Connection

object. This property is not currently supported on UNIX.

IsolationLevel

The level of isolation for the Connection
object.

Mode

The available permissions for
modifying data in a Connection object.

Provider

The name of a provider for a Connection
object. This property is not available on UNIX.

State

Describes the current state of the Connection

object.

Version

The ADO version number.

In this section:

ADO
Connection Object Attributes Property

ADO
Connection Object CommandTimeout Property

ADO
Connection Object ConnectionString Property

ADO
Connection Object ConnectionTimeout Property

ADO
Connection Object CursorLocation Property

ADO
Connection Object DefaultDatabase Property

ADO
Connection Object IsolationLevel Property

ADO
Connection Object Mode Property

ADO
Connection Object Provider Property

ADO
Connection Object State Property

ADO
Connection Object Version Property

ADOConnection Object Attributes Property

One or more characteristics of an object. This
property is read-only on UNIX.

Attributes Property Return Values (ADO Connection Object)

Sets or returns a Long value.

For a Connection object, the Attributes
property is read/write, and its value can be the sum of any one or more of
these XactAttributeEnum values (default is zero):

Value

Description

adXactCommitRetaining

Performs retaining commits, that is,
calling the CommitTrans method automatically starts a new transaction.
Not all providers support this, and it is always zero under UNIX.

adXactAbortRetaining

Performs retaining aborts, that is,
calling the BeginTrans, CommitTrans, and RollbackTrans methods
automatically starts a new transaction. Not all providers support this, and
it is always zero under UNIX.

Attributes Property Remarks (ADO Connection Object)

Use the Attributes property to set
or return characteristics of Connection objects.

When you set multiple attributes, you can
sum the appropriate constants. If you set the property value to a sum including
incompatible constants, an error occurs.

Attributes Property Examples (ADO Connection Object)

This Visual Basic example displays the
value of the Attributes property for Connection objects. It uses
the ADO
Field Object Name Property
to display the name of each Field and Property

object.

Public Sub AttributesX
Dim cnn1 As ADODB.Connection
Dim strCnn As String
' Open connection
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set cnn1 = New ADODB.Connection
cnn1.Open strCnn
' Display the attributes of the connection.
Debug.Print "Connection attributes = " & _
cnn1.Attributes
cnn1.Close
End Sub
ADO
Connection Object CommandTimeout Property

How long to wait while executing a command
before terminating the attempt and generating an error.

CommandTimeout Property Return Values (ADO Connection Object)

Sets or returns a Long value that
specifies, in seconds, how long to wait for a command to execute. Default is
30.

CommandTimeout Property Remarks (ADO Connection Object)

Use the CommandTimeout property on
a Connection object to allow the cancellation of an ADO
Connection Object Execute Method
call, due to delays from network traffic
or heavy server use. If the interval set in the CommandTimeout property
elapses before the command completes execution, an error occurs and ADO cancels the command.
If you set the property to zero, ADO

will wait indefinitely until the execution is complete. Make sure the provider
and data source to which you are writing code supports the CommandTimeout
functionality.

The CommandTimeout setting on a Connection
object has no effect on the CommandTimeout setting on a Command

object on the same Connection; that is, the Command object’s CommandTimeout
property does not inherit the value of the Connection object’s CommandTimeout
value.

On a Connection object, the CommandTimeout
property remains read/write after the Connection is opened.

CommandTimeout Property Examples (ADO Connection Object)

See the ActiveConnection property.

ADO
Connection Object ConnectionString Property

Contains the information used to establish
a connection to a data source.

ConnectionString Property Return Values (ADO Connection Object)

Sets or returns a String value.

ConnectionString Property Remarks (ADO Connection Object)

Use the ConnectionString property
to specify a data source by passing a detailed connection string containing a
series of argument = value statements separated by
semicolons.

ADO
supports seven arguments for the ConnectionString property; any other
arguments pass directly to the provider without any processing by ADO. The arguments ADO supports are as
follows:

Argument

Description

Provider

Specifies the name of the provider
to use for the connection.

DataSource

Specifies the name of a data source
for the connection, for example, an Oracle database registered as an ODBC
data source.

UserID

Specifies the user name to use when
opening the connection.

Password

Specifies the password to use when
opening the connection.

FileName

Specifies the name of a
provider-specific file (for example, a persisted data source object)
containing preset connection information.

RemoteProvider

Specifies the name of a provider to
use when opening a client-side connection (Remote Data Service only).

RemoteServer

Specifies the path name of the
server to use when opening a client-side connection (Remote Data Service
only).

After you set the ConnectionString
property and open the Connection object, the provider may alter the
contents of the property, for example, by mapping the ADO-defined argument
names to their provider equivalents.

The ConnectionString property
automatically inherits the value used for the ConnectionString
argument of the ADO
Connection Object Open Method
, so you can override the current ConnectionString
property during the Open method call.

Because the File Name argument
causes ADO to
load the associated provider, you cannot pass both the Provider and File
Name
arguments.

The ConnectionString property is
read/write when the connection is closed and read-only when it is open.

Remote Data Service Usage: When
used on a client-side Connection object, the ConnectionString
property can only include the Remote Provider and Remote Server
parameters.

ConnectionString Property Example (ADO Connection Object)

This Visual Basic example demonstrates different
ways of using the ConnectionString property to open a Connection
object. It also uses the ConnectionTimeout property to set a connection
timeout period, and the ADO
Connection Object State Property
to check the state of the connections. The

GetState function is required for this procedure to run.

Public Sub ConnectionStringX()
Dim cnn1 As ADODB.Connection
Dim cnn2 As ADODB.Connection
Dim cnn3 As ADODB.Connection
Dim cnn4 As ADODB.Connection
' Open a connection without using a Data Source Name (DSN).
Set cnn1 = New ADODB.Connection
cnn1.ConnectionString = "driver={SQL Server};" & _
"server=bigsmile;uid=sa;pwd=pwd;database=pubs"
cnn1.ConnectionTimeout = 30
cnn1.Open
' Open a connection using a DSN and ODBC tags.
Set cnn2 = New ADODB.Connection
cnn2.ConnectionString = "DSN=Pubs;UID=sa;PWD=pwd;"
cnn2.Open
' Open a connection using a DSN and OLE DB tags.
Set cnn3 = New ADODB.Connection
cnn3.ConnectionString = "Data Source=Pubs;User ID=sa;Password=pwd;"
cnn3.Open
' Open a connection using a DSN and individual
' arguments instead of a connection string.
Set cnn4 = New ADODB.Connection
cnn4.Open "Pubs", "sa", "pwd"
' Display the state of the connections.
MsgBox "cnn1 state: " & GetState(cnn1.State) & vbCr & _
"cnn2 state: " & GetState(cnn1.State) & vbCr & _
"cnn3 state: " & GetState(cnn1.State) & vbCr & _
"cnn4 state: " & GetState(cnn1.State)
cnn4.Close
cnn3.Close
cnn2.Close
cnn1.Close
End Sub
Public Function GetState(intState As Integer) As String
Select Case intState
Case adStateClosed
GetState = "adStateClosed"
Case adStateOpen
GetState = "adStateOpen"
End Select
End Function
ADO
Connection Object ConnectionTimeout Property

Sets how long to wait while establishing a
connection before terminating the attempt and generating an error.

ConnectionTimeout Property Return Values (ADO Connection Object)

Sets or returns a Long value that
specifies, in seconds, how long to wait for the connection to open. Default is
15.

ConnectionTimeout Property Remarks (ADO Connection Object)

Use the ConnectionTimeout property
on a Connection object if delays from network traffic or heavy server
use make it necessary to abandon a connection attempt. If the time from the ConnectionTimeout

property setting elapses prior to the opening of the connection, an error
occurs and ADO
cancels the attempt. If you set the property to zero, ADO will wait indefinitely until the
connection is opened. Make sure the provider to which you are writing code
supports the ConnectionTimeout functionality.

The ConnectionTimeout property is
read/write when the connection is closed and read-only when it is open.

ConnectionTimeout Property Example (ADO Connection Object)

See the ConnectionString property.

ADO
Connection Object CursorLocation Property

Sets or returns the location of the cursor
engine.

CursorLocation Property Return Values (ADO Connection Object)

Sets or returns a Long value that
can be set to one of the following constants:

Constant

Description

adUseClient

Uses client-side cursors supplied by
a local cursor library. Local cursor engines will often allow many features
that driver-supplied cursors may not, so using this setting may provide an
advantage with respect to features that will be enabled. For
backward-compatibility, the synonym adUseClientBatch is also supported.

Note: With the Sun ONE ASP
implementation of ADO,
adUseClient has a value of 1, and adUseClientBatch has a value of 3.

adUseServer

Default. Uses data provider or
driver-supplied cursors. These cursors are sometimes very flexible and allow
for some additional sensitivity to reflecting changes that others make to the
actual data source. However, some features of the Microsoft Client Cursor
Provider (such as disassociated recordsets) cannot be simulated.

Note: With the Sun ONE ASP
implementation of ADO,
adUseServer has a value of 2.

CursorLocation Property Remarks (ADO Connection Object)

This property allows you to choose between
various cursor libraries accessible to the provider. Usually, you can choose
between using a client-side cursor library or one that is located on the
server.

This property setting only affects
connections established after the property has been set. Changing the CursorLocation
property has no effect on existing connections.

This property is read/write on a Connection.

CursorLocation Property Example (ADO Connection Object)

See the AbsolutePosition property
example.

ADO
Connection Object DefaultDatabase Property

The default database for a Connection
object. This property is not currently supported on UNIX.

DefaultDatabase Property Return Values

Sets or returns a String that
evaluates to the name of a database available from the provider.

DefaultDatabase Property Remarks

Use the DefaultDatabase property to
set or return the name of the default database on a specific Connection

object.

If there is a default database, SQL
strings may use an unqualified syntax to access objects in that database. To
access objects in a database other than the one specified in the DefaultDatabase
property, you must qualify object names with the desired database name. Upon
connection, the provider will write default database information to the DefaultDatabase
property. Some providers allow only one database per connection, in which case
you cannot change the DefaultDatabase property.

Some data sources and providers may not
support this feature, and may return an error or an empty string.

Remote Data Service Usage: This
property is not available on a client-side Connection object.

DefaultDatabase Property Example

See ADO
Connection Object Provider Property

ADOConnection Object IsolationLevel Property

The level of transaction isolation for a Connection
object. Transactions are not currently supported on UNIX.

IsolationLevel Property Return Values

Sets or returns one of the following IsolationLevelEnum
values:

Constant

Description

adXactUnspecified

The provider is using a different IsolationLevel
than specified, but the level cannot be determined.

adXactChaos

You cannot overwrite pending changes
from more highly isolated transactions.

adXactBrowse

You can view uncommitted changes
from one transaction in other transactions.

adXactReadUncommitted

Same as adXactBrowse.

adXactCursorStability

Default. You can view changes in
other transactions only after they have been committed.

adXactReadCommitted

Same as adXactCursorStability.

adXactRepeatableRead

You cannot see changes in other
transactions, but requerying can bring new Recordset objects.

adXactIsolated

Transactions are conducted in
isolation of other transactions.

adXactSerializable

Same as adXactIsolated.

IsolationLevel Property Remarks

Use the IsolationLevel property to
set the isolation level of a Connection object. The IsolationLevel

property is read/write. The setting does not take effect until the next time
you call the BeginTrans method. If the level of isolation you request is
unavailable, the provider may return the next greater level of isolation.

IsolationLevel Property Example

This example uses the ADO
Connection Object Mode Property
to open an exclusive connection, and the IsolationLevel
property to open a transaction that is conducted in isolation of other
transactions.

Public Sub IsolationLevelX()
Dim cnn1 As ADODB.Connection
Dim rstTitles As ADODB.Recordset
Dim strCnn As String
` Assign connection string to variable.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
` Open connection and titles table.
Set cnn1 = New ADODB.Connection
cnn1.Mode = adModeShareExclusive
cnn1.IsolationLevel = adXactIsolated
cnn1.Open strCnn
Set rstTitles = New ADODB.Recordset
rstTitles.CursorType = adOpenDynamic
rstTitles.LockType = adLockPessimistic
rstTitles.Open "titles", cnn1, , , adCmdTable
cnn1.BeginTrans
` Display connection mode.
If cnn1.Mode = adModeShareExclusive Then
MsgBox "Connection mode is exclusive."
Else
MsgBox "Connection mode is not exclusive."
End If
` Display isolation level.
If cnn1.IsolationLevel = adXactIsolated Then
MsgBox "Transaction is isolated."
Else
MsgBox "Transaction is not isolated."
End If
` Change the type of psychology titles.
Do Until rstTitles.EOF
If Trim(rstTitles!Type) = "psychology" Then
rstTitles!Type = "self_help"
rstTitles.Update
End If
rstTitles.MoveNext
Loop
` Print current data in recordset.
rstTitles.Requery
Do While Not rstTitles.EOF
Debug.Print rstTitles!Title & " - " & rstTitles!Type
rstTitles.MoveNext
Loop
` Restore original data.
cnn1.RollbackTrans
rstTitles.Close
cnn1.Close
End Sub
ADO
Connection Object Mode Property

The available permissions for modifying
data in a Connection.

Mode Property Return Values (ADO Connection Object)

Sets or returns one of the following ConnectModeEnum
values:

Constant

Description

adModeUnknown

Default. The permissions have not
been set or cannot be determined.

adModeRead

Read-only permission.

adModeWrite

Write-only permission.

adModeReadWrite

Read/write permission.

adModeShareDenyRead

Prevents others from opening a
connection with read permission.

adModeShareDenyWrite

Prevents others from opening a
connection with write permission.

adModeShareExclusive

Prevents others from opening a
connection.

adModeShareDenyNone

Allows others to open a connection
with any permissions. Neither read nor write access can be denied to others.

Mode Property Remarks (ADO Connection Object)

Use the Mode property to set or
return the access permissions in use by the provider on the current connection.
You can set the Mode property only when the Connection object is
closed.

Mode Property Example (ADO Connection Object)

See the IsolationLevel property
example.

ADO
Connection Object Provider Property

The name of the provider for a Connection
object. This property is not available on UNIX.

Provider Property Return Values

Sets or returns a String value.

Provider Property Remarks

Use the Provider property to set or
return the name of the provider for a connection. This property can also be set
by the contents of the ConnectionString property or the ConnectionString
argument of the ADO
Connection Object Open Method
; however, specifying a provider in more than
one place while calling the Open method can have unpredictable results.
If no provider is specified, the property will default to MSDASQL (Microsoft
OLE DB Provider for ODBC).

The Provider property is read/write
when the connection is closed and read-only when it is open. The setting does
not take effect until you either open the Connection object or access
the ADO
Properties Collection
of the Connection object. If the setting is
invalid, an error occurs.

Provider Property Example

This Visual Basic example demonstrates the
Provider property by opening two Connection objects using
different providers. It also uses the DefaultDatabase property to set
the default database for the Microsoft ODBC Provider.

Public Sub ProviderX()
Dim cnn1 As ADODB.Connection
Dim cnn2 As ADODB.Connection
` Open a connection using the Microsoft ODBC provider.
Set cnn1 = New ADODB.Connection
cnn1.ConnectionString = "driver={SQL Server};" & _
"server=bigsmile;uid=sa;pwd=pwd"
cnn1.Open strCnn
cnn1.DefaultDatabase = "pubs"
` Display the provider.
MsgBox "Cnn1 provider: " & cnn1.Provider
` Open a connection using the Microsoft Jet provider.
Set cnn2 = New ADODB.Connection
cnn2.Provider = "Microsoft.Jet.OLEDB.3.51"
cnn2.Open "C:\Samples\northwind.mdb", "admin", ""
` Display the provider.
MsgBox "Cnn2 provider: " & cnn2.Provider
cnn1.Close
cnn2.Close
End Sub
ADO Connection Object
State Property

Describes the current state of an object.

State Property Return Values (ADO Connection Object)

Sets or returns a Long value that
can be one of the following constants:

Constant

Description

adStateClosed

Default. The object is closed.

adStateOpen

The object is open.

State Property Remarks (ADO Connection Object)

You can use the State property to
determine the current state of a given object at any time.

State Property Examples (ADO Connection Object)

This Visual Basic example demonstrates
different ways of using the ConnectionString property to open a Connection
object. It also uses the ConnectionTimeout property to set a connection
timeout period, and the State property to check the state of the connections.
The GetState function is required for this procedure to run.

Public Sub ConnectionStringX()
Dim cnn1 As ADODB.Connection
Dim cnn2 As ADODB.Connection
Dim cnn3 As ADODB.Connection
Dim cnn4 As ADODB.Connection
` Open a connection without using a DSN.
Set cnn1 = New ADODB.Connection
cnn1.ConnectionString = "driver={SQL Server};" & _
"server=bigsmile;uid=sa;pwd=pwd;database=pubs"
cnn1.ConnectionTimeout = 30
cnn1.Open
` Open a connection using a DSN and ODBC tags.
Set cnn2 = New ADODB.Connection
cnn2.ConnectionString = "DSN=Pubs;UID=sa;PWD=pwd;"
cnn2.Open
` Open a connection using a DSN and OLE DB tags.
Set cnn3 = New ADODB.Connection
cnn3.ConnectionString = "Data Source=Pubs;User ID=sa;Password=pwd;"
cnn3.Open
` Open a connection using a DSN and individual
` arguments instead of a connection string.
Set cnn4 = New ADODB.Connection
cnn4.Open "Pubs", "sa", "pwd"
` Display the state of the connections.
MsgBox "cnn1 state: " & GetState(cnn1.State) & vbCr &_
"cnn2 state: " & GetState(cnn1.State) & vbCr & _
"cnn3 state: " & GetState(cnn1.State) & vbCr & _
"cnn4 state: " & GetState(cnn1.State)
cnn4.Close
cnn3.Close
cnn2.Close
cnn1.Close
End Sub
Public Function GetState(intState As Integer) As String
Select Case intState
Case adStateClosed
GetState = "adStateClosed"
Case adStateOpen
GetState = "adStateOpen"
End Select
End Function
ADO
Connection Object Version Property

The ADO
version number.

Version Property Return Values

Returns a String value.

Version Property Remarks

Use the Version property to return
the version number of the ADO

implementation. The version of the provider will be available on Windows
servers as a dynamic property in the ADO Properties
Collection
. The Properties collection is not currently
supported on UNIX.

Version Property Example

This Visual Basic example uses the Version
property of a Connection object to display the current ADO version. It also uses several dynamic
properties to show the current DBMS name and version, OLE DB version, provider
name and version, driver name and version, and driver ODBC version.

Public Sub VersionX()
Dim cnn1 As ADODB.Connection
' Open connection.
Set cnn1 = New ADODB.Connection
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
cnn1.Open strCnn
strVersionInfo = "ADO Version: " & cnn1.Version & vbCr & _
"DBMS Name: " & cnn1.Properties("DBMS Name") & vbCr & _
"DBMS Version: " & cnn1.Properties("DBMS Version") & vbCr & _
"OLE DB Version: " & cnn1.Properties("OLE DB Version") & vbCr & _
"Provider Name: " & cnn1.Properties("Provider Name") & vbCr & _
"Provider Version: " & cnn1.Properties("Provider Version") & vbCr & _
"Driver Name: " & cnn1.Properties("Driver Name") & vbCr & _
"Driver Version: " & cnn1.Properties("Driver Version") & vbCr & _
"Driver ODBC Version: " & cnn1.Properties("Driver ODBC Version")
MsgBox strVersionInfo
cnn1.Close
End Sub

ADO
Connection Object Remarks


A Connection object represents a
session with a data source. In the case of a client/server database system, it
may represent an actual network connection to the server. Depending on the
functionality of the provider, some collections, properties, and methods of the
Connection object may not be available.

Use the collections, methods, and
properties of a Connection object for:

·
configuring the connection before opening it
with the ConnectionString, CommandTimeout, and ADO
Connection Object Mode Property
properties.

·
setting the CursorLocation property to
invoke the Client Cursor Provider, which supports batch updates. Batch
updates are not currently supported on UNIX.

·
setting the default database for the connection
with the DefaultDatabase property.

·

setting the level of isolation for the
transactions opened on the connection with the IsolationLevel property. Transactions
are not currently supported on UNIX.

·
specifying an OLE DB provider with the ADO
Connection Object Provider Property
.

·
establishing and breaking the physical
connection to the data source with the ADO
Connection Object Open Method
and ADO
Connection Object Close Method
methods.

·
executing a command on the connection with the ADO
Connection Object Execute Method
and configuring the execution with the CommandTimeout
property.

·
managing transactions on the open connection,
including nested transactions if the provider supports them, with the BeginTrans,
CommitTrans,
and RollbackTrans methods and the ADO
Connection Object Attributes Property
. The transaction methods are not
currently supported on UNIX.

·
examining errors returned from the data source
with the ADO
Errors Collection
.

·
reading the version from the ADO implementation in use with the ADO
Connection Object Version Property
.

·
obtaining schema information about your database
with the ADO
Connection Object OpenSchema Method
.

Note iconNote

To execute a query without using a Command object,
pass a query string to the Execute method of a Connection object.
However, a Command object is required when you want to retain the
command text and re-execute it, or use query parameters.

ADO Error
Object


The ADO Error
object provides specific details about each ADO error.

In this section:

ADO
Error Object Properties

ADO
Error Object Remarks

ADO Error
Object Properties


Note iconNote

None of the ADO
Error object properties listed in this section are currently supported
on UNIX.

Property

Description

Description

A descriptive string associated with
an error.

HelpContext

The help file topic associated with
an error.

HelpContext, HelpFile

The help file associated with an
error.

NativeError

The provider-specific error code for
an error.

Number

The number that uniquely identifies
an error.

Source

The name of the object or
application that originally generated the error.

SQL State

The SQL state for a given error.

In this section:

ADO
Error Object Description Property

ADO
Error Object HelpContext, HelpFile Property

ADO
Error Object NativeError Property

ADO
Error Object Number Property

ADO
Error Object Source Property

ADO
Error Object SQLState Property

ADO Error
Object Description Property

A descriptive string associated with an Error

object. This property is not currently supported on UNIX.

Description Property Return Values (ADO Error Object)

Returns a String value.

Description Property Remarks (ADO Error Object)

Use the Description property to
obtain a short description of the error. Display this property to alert the
user to an error that you cannot or do not want to handle. The string will come
from either ADO
or a provider.

Providers are responsible for passing
specific error text to ADO.

ADO adds an Error
object to the ADO
Errors Collection
for each provider error or warning it receives. Enumerate
the Errors collection to trace the errors that the provider passes.

Description Property Example (ADO Error Object)

This Visual Basic example triggers an
error, traps it, and displays the ADO
Error Object Description Property
, ADO
Error Object HelpContext, HelpFile Property
, ADO
Error Object NativeError Property
, ADO
Error Object Number Property
, ADO
Error Object Source Property
, and ADO
Error Object SQLState Property
properties of the resulting Error

object:

Public Sub DescriptionX()
Dim cnn1 As ADODB.Connection
Dim errLoop As ADODB.Error
Dim strError As String
On Error GoTo ErrorHandler
` Intentionally trigger an error.
Set cnn1 = New ADODB.Connection
cnn1.Open "nothing"
Exit Sub
ErrorHandler:
` Enumerate Errors collection and display
` properties of each Error object.
For Each errLoop In cnn1.Errors
strError = "Error #" & errLoop.Number & vbCr & _
" " & errLoop.Description & vbCr & _
" (Source: " & errLoop.Source & ")" & vbCr & _
" (SQL State: " & errLoop.SQLState & ")" & vbCr & _
" (NativeError: " & errLoop.NativeError & ")" & vbCr
If errLoop.HelpFile = "" Then
strError = strError & _
" No Help file available" & _
vbCr & vbCr
Else
strError = strError & _
" (HelpFile: " & errLoop.HelpFile & ")" & vbCr & _
" (HelpContext: " & errLoop.HelpContext & ")" & _
vbCr & vbCr
End If
Debug.Print strError
Next
Resume Next
End Sub
ADO Error
Object HelpContext, HelpFile Property

The help file and topic associated with an
Error object. This property is not currently supported on UNIX.

HelpContext, HelpFile Property Return Values

HelpContextID

Returns a context ID, as a Long
value, for a topic in a Microsoft Windows Help file.

HelpFile

Returns a String that evaluates to
a fully resolved path to a Help file.

HelpContext, HelpFile Property Remarks

If a Windows Help (.hlp) file is specified
in the HelpFile property, the HelpContext property is used to
automatically display the Help topic it identifies. If there is no relevant
help topic available, the HelpContext property returns zero and the HelpFile

property returns a zero-length string (“”).

HelpContext, HelpFile Property Examples

See the ADO
Error Object Description Property
example.

ADO Error
Object NativeError Property

The provider-specific error code for a
given Error object. This property is not currently supported on UNIX.

NativeError Property Return Values

Returns a Long value.

NativeError Property Remarks

Use the NativeError property to
retrieve the database-specific error information for a particular Error
object. For example, when using the Microsoft ODBC Provider for OLE DB with a
SQL Server database, native error codes that originate from SQL Server pass
through ODBC and the ODBC Provider to the ADO

NativeError property.

NativeError Property Example

See the ADO
Error Object Description Property
.

ADO Error
Object Number Property

The number that uniquely identifies an Error
object. This property is not currently supported on UNIX.

Number Property Return Values

Returns a Long value.

Number Property Remarks

Use the Number property to
determine which error occurred. The value of the property is a unique number
that corresponds to the error condition.

Number Property Example

See the ADO
Error Object Description Property
.

ADO Error
Object Source Property

The name of the object or application that
originally generated an error. This property is not currently supported on
UNIX
.

Source Property Return Values

Returns a String value.

Source Property Remarks

Use the Source property on an Error
object to determine the name of the object or application that originally
generated an error. This could be the object’s class name or programmatic ID.
For errors in ADODB, the property value will be ADODB.ObjectName.

Source Property Parameters (ADO Error Object)

ObjectName

The name of the object that triggered the
error. The Source property is read-only for Error objects.

Based on the error documentation from the Source,
ADO
Error Object Number Property
, and ADO
Error Object Description Property
properties of Error objects, you
can write code that will handle the error appropriately.

Source Property Example

See the ADO
Error Object Description Property
example.

ADO Error
Object SQLState Property

The SQL state for a given Error
object. This property is not currently supported on UNIX.

SQLState Property Return Values

Returns a five-character String

that follows the ANSI SQL standard.

SQLState Property Remarks

Use the SQLState property to read
the five-character error code that the provider returns when an error occurs
during the processing of a SQL statement. For example, when using the Microsoft
OLE DB Provider for ODBC with a SQL Server database, SQL state error codes
originate from ODBC based either on errors specific to ODBC or on errors that
originate from Microsoft SQL Server, and are then mapped to ODBC errors. These
error codes are documented in the ANSI SQL standard, but may be implemented
differently by different data sources.

SQLState Property Example

See the ADO
Error Object Description Property
example.

ADO Error
Object Remarks


Any operation involving ADO objects can generate one or more provider
errors. As each error occurs, one or more Error objects are placed in
the ADO
Errors Collection
of the ADO Connection
Object
. When another ADO

operation generates an error, the Errors collection is cleared, and the
new set of Error objects are placed in the Errors collection.

Note iconNote

Each Error object represents a specific provider
error, not an ADO
error. ADO

errors are exposed to the run-time exception handling mechanism. For example,
in Microsoft Visual Basic, the occurrence of an ADO-specific error will
trigger an On Error event and appear in the Err object. For a
complete list of ADO
errors, see Appendix B.

Read the Error object’s properties
to obtain specific details about each error:

·
The ADO
Error Object Description Property
contains the text of the error.

·

The ADO
Error Object Number Property
contains the Long integer value of the
error constant.

·
The ADO
Error Object Source Property
identifies the object that raised the error.
This is particularly useful when you have several Error objects in the Errors

collection following a request to a data source.

·
The ADO
Error Object HelpContext, HelpFile Property
indicate the appropriate
Microsoft Windows Help file and Help topic, respectively (if any exist), for
the error.

·
The ADO
Error Object SQLState Property
and ADO
Error Object NativeError Property
properties provide information
from SQL data sources.

ADO
supports the return of multiple errors by a single ADO operation to allow for error information
specific to the provider. To obtain this error information in an error handler,
use the appropriate error-trapping features of the language or environment you
are working with, then use nested loops to enumerate the properties of each Error
object in the Errors collection.

ADO

clears the OLE Error Info object before making a call that could
potentially generate a new provider error. However, the Errors collection
on the Connection object is cleared and populated only when the provider
generates a new error, or when the ADO Collections
Clear Method
is called.

Some properties and methods return
warnings that appear as Error objects in the Errors collection
but do not halt a program’s execution. Before you call the ADO
Recordset Object Resync Method
, ADO
Recordset Object UpdateBatch Method
, or ADO
Recordset Object CancelBatch Method
methods on an ADO Recordset
Object
, or before you set the ADO
Recordset Object Filter Property
on a Recordset object, call the ADO Collections
Clear Method
on the Errors collection so that you can read the Count

property of the Errors collection to test for returned warnings.

If there is no valid Connection
object when using Microsoft Visual Basic and VBScript, retrieve error
information from the Err object.

To refer to an Error object in a
collection by its ordinal number, use either of the following syntax forms:

connection.Errors.Item(0)
connection.Errors(0)

ADO Field Object


The ADO Field Object represents a column
of data with a common data type.

In this section:

ADO
Field Object Collections

ADO
Field Object Methods

ADO
Field Object Properties

ADO
Field Object Remarks

ADO Field Object Collections


Properties

All Property objects for a
specific instance of a Field object. This collection is not
currently supported on UNIX.

ADO Field Object Methods


Method

Description

AppendChunk

Appends data to a large text or
binary data field.

GetChunk

Returns all or a portion of the
contents of a large text or binary data field.

In this section:

ADO
Field Object AppendChunk Method

ADO
Field Object GetChunk Method

ADO Field Object AppendChunk Method

Appends data to a large text or binary
data Field object.

AppendChunk Method Syntax (ADO Field Object)
object.AppendChunk Data
AppendChunk Method Parameters (ADO Field Object)

object

A Field object.

Data

A Variant containing the data you want to
append to the object.

AppendChunk Method Remarks (ADO Field Object)

Use the AppendChunk method on a Field
object to fill it with long binary or character data. In situations where
system memory is limited, you can use the AppendChunk method to
manipulate long values in portions rather than in their entirety.

If the adFldLong bit in the ADO
Field Object Attributes Property
of a Field object is set to True,
you can use the AppendChunk method for that field.

The first AppendChunk call on a Field
object writes data to the field, overwriting any existing data. Subsequent AppendChunk
calls add to existing data. If you are appending data to one field and then you
set or read the value of another field in the current record, ADO assumes that you are done appending data
to the first field. If you call the AppendChunk method on the first
field again, ADO

interprets the call as a new AppendChunk operation and overwrites the
existing data. Accessing fields in other ADO Recordset
Object
objects (that are not clones of the first Recordset
object) will not disrupt AppendChunk operations.

If there is no current record when you
call AppendChunk on a Field object, an error occurs.

AppendChunk Method Examples (ADO Field Object)

See the ADO
Field Object GetChunk Method
example.

ADO Field Object GetChunk Method

Returns all or a portion of the contents
of a large text or binary data Field object.

GetChunk Method Syntax (ADO Field Object)
variable = field.GetChunk( Size )
GetChunk Method Parameters (ADO Field Object)

variable

Variant to hold data returned.

Size

A Long expression equal to the
number of bytes or characters you want to retrieve.

GetChunk Method Remarks (ADO Field Object)

Use the GetChunk method on a Field
object to retrieve part or all of its long binary or character data. In
situations where system memory is limited, you can use the GetChunk
method to manipulate long values in portions rather than in their entirety.

The data a GetChunk call returns is
assigned to variable. If Size is greater than the remaining
data, the GetChunk method returns only the remaining data without
padding variable with empty spaces. If the field is empty, the GetChunk

method returns Null.

Each subsequent GetChunk call
retrieves data starting from where the previous GetChunk call left off.
However, if you are retrieving data from one field and then you set or read the
value of another field in the current record, ADO assumes you are done retrieving data from
the first field. If you call the GetChunk method on the first field
again, ADO

interprets the call as a new GetChunk operation and starts reading from
the beginning of the data. Accessing fields in other ADO Recordset
Object
objects (that are not clones of the first Recordset
object) will not disrupt GetChunk operations.

If the adFldLong bit in the ADO
Field Object Attributes Property
of a Field object is set to True,
you can use the GetChunk method for that field.

If there is no current record when you use
the GetChunk method on a Field object, error 3021 (no current
record) occurs.

GetChunk Method Return Values (ADO Field Object)

Returns a Variant.

GetChunk Method Example (ADO Field Object)

See the ADO
Field Object AppendChunk Method
.

ADO Field Object Properties


Property

Description

ActualSize

The actual length of a field value.

Attributes

One or more characteristics of a
field. This property is read-only on UNIX.

DefinedSize

The defined size of a field.

Name

The name of a field.

NumericScale

The scale of numeric values in a
field.

OriginalValue

The value of a field that existed in
the record before any changes were made. This property is not currently
supported on UNIX
.

Precision

The degree of precision for numeric
values in a field.

Type

The data type of the field.

UnderlyingValue

The current value of the field in
the database. This property is not currently supported on UNIX.

Value

The value assigned to the field.

In this section:

ADO
Field Object ActualSize Property

ADO
Field Object Attributes Property

ADO
Field Object DefinedSize Property

ADO
Field Object Name Property

ADO
Field Object NumericScale Property

ADO
Field Object OriginalValue Property

ADO
Field Object Precision Property

ADO
Field Object Type Property

ADO
Field Object UnderlyingValue Property

ADO
Field Object Value Property

ADO Field Object ActualSize Property

The actual length of a field’s value.

ActualSize Property Return Values (ADO Field Object)

Returns a Long value. Some
providers may allow this property to be set to reserve space for BLOB data, in
which case the default value is 0.

ActualSize Property Remarks (ADO Field Object)

Use the ActualSize property to
return the actual length of a Field object’s value. For all fields, the ActualSize

property is read-only. If ADO
cannot determine the length of the Field object’s value, the ActualSize
property returns adUnknown.

The ActualSize and ADO
Field Object DefinedSize Property
properties are different as shown
in the following example: a Field object with a declared type of adVarChar

and a maximum length of 50 characters returns a DefinedSize property
value of 50, but the ActualSize property value it returns is the length
of the data stored in the field for the current record.

ActualSize Property Example (ADO Field Object)

This Visual Basic example uses the ActualSize
and DefinedSize properties to display the defined size and actual size
of a field.

Public Sub ActualSizeX()
Dim rstStores As ADODB.Recordset
Dim strCnn As String
' Open a recordset for the Stores table.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set rstStores = New ADODB.Recordset
rstStores.Open "stores", strCnn, , , adCmdTable
' Loop through the recordset displaying the contents
' of the stor_name field, the field's defined size,
' and its actual size.
rstStores.MoveFirst
Do Until rstStores.EOF
MsgBox "Store name: " & rstStores!stor_name & _
vbCr & "Defined size: " & _
rstStores!stor_name.DefinedSize & _
vbCr & "Actual size: " & _
rstStores!stor_name.ActualSize & vbCr
rstStores.MoveNext
Loop
rstStores.Close
End Sub
ADO Field Object Attributes Property

One or more characteristics of an object. This
property is read-only on UNIX.

Attributes Property Return Values (ADO Field Object)

Sets or returns a Long value.

Attributes Property Field (ADO Field Object)

For a Field object, the Attributes
property is read-only, and its value can be the sum of any one or more of these
FieldAttributeEnum values:

Value

Description

adFldMayDefer

The field is deferred; that is, the
field values are not retrieved from the data source with the whole record,
but only when you explicitly access them.

adFldUpdatable

The field can be written.

adFldUnknownUpdatable

The provider cannot determine if the
field can be written.

adFldFixed

The field contains fixed-length
data.

adFldIsNullable

The field accepts Null

values.

adFldMayBeNull

You can read Null values from
the field.

adFldLong

The field is a long binary field.
Also indicates that you can use the ADO
Field Object AppendChunk Method
and ADO
Field Object GetChunk Method
methods.

adFldRowID

The field contains some kind of
record ID (record number, unique identifier, and so forth).

adFldRowVersion

The field contains some kind of time
or date stamp used to track updates.

adFldCacheDeferred

The provider caches field values and
subsequent reads are done from the cache.

Attributes Property Remarks (ADO Field Object)

Use the Attributes property to set
or return characteristics of Field objects.

When you set multiple attributes, you can
sum the appropriate constants. If you set the property value to a sum including
incompatible constants, an error occurs.

ADO Field Object DefinedSize Property

The defined size of a Field object.

DefinedSize Property Return Values (ADO Field Object)

Returns a Long value that reflects
the defined size of a field as a number of bytes.

DefinedSize Property Remarks (ADO Field Object)

Use the DefinedSize property to
determine the data capacity of a Field object.

The DefinedSize and ADO
Field Object ActualSize Property
properties are different. For
example, consider a Field object with a declared type of adVarChar
and a DefinedSize property value of 50, containing a single character.
The ActualSize property value it returns is the length in bytes of the
single character.

DefinedSize Property Examples (ADO Field Object)

See the ADO
Field Object ActualSize Property
example.

ADO Field Object Name Property

The name of an object.

Name Property Return Values (ADO Field Object)

Sets or returns a String value. The
value is read-only on a Field object.

Name Property Remarks (ADO Field Object)

Use the Name property to retrieve
the name of a Field object.

The Name property is read-only.
Names do not have to be unique within a collection.

Name Property Examples (ADO Field Object)

See the ADO
Field Object Attributes Property
example.

ADO Field Object NumericScale Property

The scale of Numeric values in a Field
object.

NumericScale Property Return Values (ADO Field
Object)

Sets or returns a Byte value,
indicating the number of decimal places to which numeric values will be
resolved

NumericScale Property Remarks (ADO Field Object)

Use the NumericScale property to
determine how many digits to the right of the decimal point will be used to
represent values for a numeric Field object.

The NumericScale property is
read-only.

ADO Field Object OriginalValue Property

The value of a Field object that
existed in the record before any changes were made. This property is not
currently supported on UNIX
.

OriginalValue Property Return Values (ADO Field
Object)

Returns a Variant value.

OriginalValue Property Remarks (ADO Field Object)

Use the OriginalValue property to
return the original field value for a field from the current record.

In immediate update mode (the provider
writes changes to the underlying data source once you call the ADO
Recordset Object Update Method
), the OriginalValue property returns
the field value that existed prior to any changes (that is, since the last Update

method call). This is the same value that the ADO
Recordset Object CancelUpdate Method
uses to replace the ADO
Field Object Value Property
.

In batch update mode (the provider caches
multiple changes and writes them to the underlying data source only when you
call the ADO
Recordset Object UpdateBatch Method
), the OriginalValue property
returns the field value that existed prior to any changes (that is, since the
last UpdateBatch method call). This is the same value that the ADO
Recordset Object CancelBatch Method
uses to replace the Value

property. When you use this property with the UnderlyingValue property,
you can resolve conflicts that arise from batch updates. Batch updates are
currently not supported on UNIX
.

OriginalValue Property Example (ADO Field Object)

This Visual Basic example demonstrates the
OriginalValue and UnderlyingValue properties by displaying a
message if a record’s underlying data has changed during a ADO Recordset
Object
batch update.

Public Sub OriginalValueX()
Dim cnn1 As ADODB.Connection
Dim rstTitles As ADODB.Recordset
Dim fldType As ADODB.Field
Dim strCnn As String
' Open connection.
Set cnn1 = New ADODB.Connection
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
cnn1.Open strCnn
' Open recordset for batch update.
Set rstTitles = New ADODB.Recordset
Set rstTitles.ActiveConnection = cnn1
rstTitles.CursorType = adOpenKeyset
rstTitles.LockType = adLockBatchOptimistic
rstTitles.Open "titles"
' Set field object variable for Type field.
Set fldType = rstTitles!Type
' Change the type of psychology titles.
Do Until rstTitles.EOF
If Trim(fldType) = "psychology" Then
fldType = "self_help"
End If
rstTitles.MoveNext
Loop
' Similate a change by another user by updating
' data using a command string.
cnn1.Execute "UPDATE titles SET type = 'sociology' " & _
"WHERE type = 'psychology'"
'Check for changes.
rstTitles.MoveFirst
Do Until rstTitles.EOF
If fldType.OriginalValue <> _
fldType.UnderlyingValue Then
MsgBox "Data has changed!" & vbCr & vbCr & _
" Title ID: " & rstTitles!title_id & vbCr & _
" Current value: " & fldType & vbCr & _
" Original value: " & _
fldType.OriginalValue & vbCr & _
" Underlying value: " & _
fldType.UnderlyingValue & vbCr
End If
rstTitles.MoveNext
Loop
' Cancel the update because this is a demonstration.
rstTitles.CancelBatch
rstTitles.Close
' Restore original values.
cnn1.Execute "UPDATE titles SET type = 'psychology' " & _
"WHERE type = 'sociology'"
cnn1.Close
End Sub
ADO Field Object Precision Property

The degree of precision for numeric Field
objects.

Precision Property Return Values (ADO Field Object)

Sets or returns a Byte value,
indicating the maximum total number of digits used to represent values. The
value is read-only on a Field object.

Precision Property Remarks (ADO Field Object)

Use the Precision property to
determine the maximum number of digits used to represent values for a numeric Field

object.

Precision Property Example (ADO Field Object)

See the ADO
Field Object NumericScale Property
.

ADO Field Object Type Property

The operational type or data type of a Field
object.

Type Property Return Values (ADO Field Object)

Sets or returns one of the following DataTypeEnum
values. The corresponding OLE DB type indicators are as follows:

Constant

Description

adArray

Or’d together with another type to
indicate that the data is a safe-array of that type (DBTYPE_ARRAY).

adBigInt

An 8-byte signed integer
(DBTYPE_I8).

adBinary

A binary value (DBTYPE_BYTES).

adBoolean

A Boolean value (DBTYPE_BOOL).

adByRef

Or’d together with another type to
indicate that the data is a pointer to data of the other type (DBTYPE_BYREF).

adBSTR

A null-terminated character string
(Unicode) (DBTYPE_BSTR).

adChar

A String value (DBTYPE_STR).

adCurrency

A currency value (DBTYPE_CY).
Currency is a fixed-point number with 4 digits to the right of the decimal
point. It is stored in an 8-byte signed integer scaled by 10,000.

adDate

A date value (DBTYPE_DATE). A date
is stored as a Double, the whole part of which is the number of days since
December 30, 1899, and the fractional part of which is the fraction of a day.

adDBDate

A date value (yyyymmdd)
(DBTYPE_DBDATE).

adDBTime

A time value (hhmmss) (DBTYPE_DBTIME).

adDBTimeStamp

A date-time stamp (yyyymmddhhmmss
plus a fraction in billionths) (DBTYPE_DBTIMESTAMP).

adDecimal

An exact numeric value with a fixed
precision and scale (DBTYPE_DECIMAL).

adDouble

A double-precision floating point
value (DBTYPE_R8).

adEmpty

No value was specified
(DBTYPE_EMPTY).

adError

A 32-bit error code (DBTYPE_ERROR).

adGUID

A globally unique identifier (GUID)
(DBTYPE_GUID).

adIDispatch

A pointer to an IDispatch

interface on an OLE object (DBTYPE_IDISPATCH).

adInteger

A 4-byte signed integer (DBTYPE_I4).

adIUnknown

A pointer to an IUnknown
interface on an OLE object (DBTYPE_IUNKNOWN).

adNumeric

An exact numeric value with a fixed
precision and scale (DBTYPE_NUMERIC).

adSingle

A single-precision floating point
value (DBTYPE_R4).

adSmallInt

A 2-byte signed integer (DBTYPE_I2).

adTinyInt

A 1-byte signed integer (DBTYPE_I1).

adUnsignedBigInt

An 8-byte unsigned integer
(DBTYPE_UI8).

adUnsignedInt

A 4-byte unsigned integer
(DBTYPE_UI4).

adUnsignedSmallInt

A 2-byte unsigned integer
(DBTYPE_UI2).

adUnsignedTinyInt

A 1-byte unsigned integer
(DBTYPE_UI1).

adUserDefined

A user-defined variable
(DBTYPE_UDT).

adVariant

An Automation Variant
(DBTYPE_VARIANT).

adVector

OR’d together with another type to
indicate that the data is a DBVECTOR structure, as defined by OLE DB, that
contains a count of elements and a pointer to data of the other type
(DBTYPE_VECTOR).

adWChar

A null-terminated Unicode character
string (DBTYPE_WSTR).

Type Property Remarks (ADO Field Object)

For Field objects, the Type

property is read-only.

Type Property Example (ADO Field Object)

This example demonstrates the Type
property by displaying the name of the constant corresponding to the value of
the Type property of all the Field objects in the Employees

table. The FieldType function is required for this procedure to run.

Public Sub TypeX()
Dim rstEmployees As ADODB.Recordset
Dim fldLoop As ADODB.Field
Dim strCnn As String
` Open recordset with data from Employee table.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set rstEmployees = New ADODB.Recordset
rstEmployees.Open "employee", strCnn, , , adCmdTable
Debug.Print "Fields in Employee Table:" & vbCr
` Enumerate Fields collection of Employees table.
For Each fldLoop In rstEmployees.Fields
Debug.Print " Name: " & fldLoop.Name & vbCr & _
" Type: " & FieldType(fldLoop.Type) & vbCr
Next fldLoop
End Sub
Public Function FieldType(intType As Integer) As String
Select Case intType
Case adChar
FieldType = "adChar"
Case adVarChar
FieldType = "adVarChar"
Case adSmallInt
FieldType = "adSmallInt"
Case adUnsignedTinyInt
FieldType = "adUnsignedTinyInt"
Case adDBTimeStamp
FieldType = "adDBTimeStamp"
End Select
End Function
ADO Field Object UnderlyingValue Property

A Field object’s current value in
the database. This property is not currently supported on UNIX.

UnderlyingValue Property Return Values (ADO Field
Object)

Returns a Variant value.

UnderlyingValue Property Remarks (ADO Field Object)

Use the UnderlyingValue property to
return the current field value from the database. The field value in the UnderlyingValue
property is the value that is visible to your transaction and may be the result
of a recent update by another transaction. This may differ from the ADO
Field Object OriginalValue Property
, which reflects the value that was
originally returned to the ADO Recordset
Object
.

This is similar to using the ADO
Recordset Object Resync Method
, but the UnderlyingValue property
returns only the value for a specific field from the current record. This is
the same value that the Resync method uses to replace the ADO
Field Object Value Property
.

When you use this property with the OriginalValue

property, you can resolve conflicts that arise from batch updates.

UnderlyingValue Property Example (ADO Field Object)

See the ADO
Field Object OriginalValue Property
example.

ADO Field Object Value Property

Indicates the value assigned to a Field
object.

Value Property Return Values (ADO Field Object)

Sets or returns a Variant value. Default
value depends on the ADO
Field Object Type Property
.

Value Property Remarks (ADO Field Object)

Use the Value property to set or
return data from Field objects. ADO
allows setting and returning long binary data with the Value property.

Value Property Example (ADO Field Object)

This Visual Basic example demonstrates the
Value property with Field and Property objects by
displaying field and property values for the Employees table.

Public Sub ValueX()
Dim rstEmployees As ADODB.Recordset
Dim fldLoop As ADODB.Field
Dim prpLoop As ADODB.Property
Dim strCnn As String
' Open recordset with data from Employee table.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set rstEmployees = New ADODB.Recordset
rstEmployees.Open "employee", strCnn, , , adCmdTable
Debug.Print "Field values in rstEmployees"
' Enumerate the Fields collection of the Employees
' table.
For Each fldLoop In rstEmployees.Fields
` Because Value is the default property of a
` Field object, the use of the actual keyword
` here is optional.
Debug.Print " " & fldLoop.Name & " = " &
fldLoop.Value
Next fldLoop
Debug.Print "Property values in rstEmployees"
' Enumerate the Properties collection of the
' Recordset object.
For Each prpLoop In rstEmployees.Properties
' Because Value is the default property of a
' Property object, the use of the actual keyword
' here is optional.
Debug.Print " " & prpLoop.Name & " = " &
prpLoop.Value
Next prpLoop
rstEmployees.Close
End Sub

ADO Field Object Remarks


A ADO Recordset
Object
has an ADO Fields
Collection
made up of Field objects. Each Field object
corresponds to a column in the recordset. You use the ADO
Field Object Value Property
of Field objects to set or return data
for the current record. Depending on the functionality the provider exposes,
some collections, methods, or properties of a Field object may not be
available.

The collections, methods, and properties
of a Field object are used to:

·
return the name of a field with the ADO
Field Object Name Property
.

·
view or change the data in the field with the ADO
Field Object Value Property
.

·
return the basic characteristics of a field with
the ADO
Field Object Type Property
, ADO
Field Object Precision Property
, and ADO
Field Object NumericScale Property
properties.

·
return the declared size of a field with the ADO
Field Object DefinedSize Property
.

·
return the actual size of the data in a given
field with the ADO
Field Object ActualSize Property
.

·
determine what types of functionality are
supported for a given field with the ADO
Field Object Attributes Property
and ADO Properties
Collection
.

·
manipulate the values of fields containing long
binary or long character data with the ADO
Field Object AppendChunk Method
and ADO
Field Object GetChunk Method
methods.

·
resolve discrepancies in field values during
batch updating with the ADO
Field Object OriginalValue Property
and UnderlyingValue properties
(if the provider supports batch updates).

Note iconNote

All metadata properties (Name, Type, DefinedSize,
Precision, and NumericScale) are available before opening the Field

object’s recordset. Setting them at that time is useful for dynamically
constructing forms.

ADO
Parameter Object


The ADO Parameter Object represents a
parameter or argument associated with a Command object based on a
parameterized query or stored procedure.

In this section:

ADO
Parameter Object Collections

ADO
Parameter Object Methods

ADO
Parameter Object Properties

ADO
Parameter Object Remarks

ADO
Parameter Object Collections


Properties

All the Property objects for
a specific instance of a Parameter object. This collection is not
currently supported on UNIX.

ADO
Parameter Object Methods


AppendChunk

Appends data to a large text or
binary data parameter.

In this section:

ADO
Parameter Object AppendChunk Method

ADO
Parameter Object AppendChunk Method

Appends data to a large text or binary
data Parameter object.

AppendChunk Method Syntax (ADO Parameter Object)
object.AppendChunk Data
AppendChunk Method Parameters (ADO Parameter Object)

object

A Parameter object.

Data

A Variant containing the data you want to
append to the object.

AppendChunk Method Remarks (ADO Parameter Object)

Use the AppendChunk method on a Parameter

object to fill it with long binary or character data. In situations where
system memory is limited, you can use the AppendChunk method to
manipulate long values in portions rather than in their entirety.

If the adFldLong bit in the ADO
Parameter Object Attributes Property
of a Parameter object is set to
True, you can use the AppendChunk method for that parameter.

The first AppendChunk call on a Parameter
object writes data to the parameter, overwriting any existing data. Subsequent AppendChunk
calls on a Parameter object adds to existing parameter data. An AppendChunk
call that passes a Null value generates an error; you must manually set
the ADO
Parameter Object Value Property
of the Parameter object to a
zero-length string (“”) in order to clear its value.

ADO
Parameter Object Properties


Property

Description

Attributes

One or more characteristics of a
parameter. This property is currently read-only on UNIX.

Direction

Indicates if the parameter is an
input parameter, an output parameter, or both; or if the parameter is the
output of a stored procedure.

Name

The name of the parameter.

NumericScale

The scale of numeric values in the
parameter.

Precision

The degree of precision for numeric
values in the parameter.

Size

The maximum size, in bytes or
characters, of a parameter.

Type

The data type of the parameter.

Value

The value assigned to the parameter.

In this section:

ADO
Parameter Object Attributes Property

ADO
Parameter Object Direction Property

ADO
Parameter Object Name Property

ADO
Parameter Object NumericScale Property

ADO
Parameter Object Precision Property

ADO
Parameter Object Size Property

ADO
Parameter Object Type Property

ADO
Parameter Object Value Property

ADO
Parameter Object Attributes Property

One or more characteristics of an object. This
property is read-only on UNIX.

Attributes Property Return Values (ADO Parameter Object)

Sets or returns a Long value.

Attributes Property Parameters (ADO Parameter Object)

For an ADO Parameter
Object
, the Attributes property is read/write, and its value can be
the sum of any one or more of these ParameterAttributesEnum values:

Value

Description

adParamSigned

Default. The parameter accepts
signed values.

adParamNullable

The parameter accepts Null

values.

adParamLong

The parameter accepts long binary
data.

Attributes Property Remarks (ADO Parameter Object)

Use the Attributes property to set
or return characteristics of Parameter objects.

When you set multiple attributes, you can
sum the appropriate constants. If you set the property value to a sum including
incompatible constants, an error occurs.

Attributes Property Examples (ADO Parameter Object)

This Visual Basic example displays the
value of the Attributes property for Connection, Field,
and Property objects. It uses the ADO
Parameter Object Name Property
to display the name of each Field and

Property object.

Public Sub AttributesX
Dim cnn1 As ADODB.Connection
Dim rstEmployees As ADODB.Recordset
Dim fldLoop As ADODB.Field
Dim proLoop As ADODB.Property
Dim strCnn As String
' Open connection and recordset.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set cnn1 = New ADODB.Connection
cnn1.Open strCnn
Set rstEmployees = New ADODB.Recordset
rstEmployees.Open "employee", cnn1, , ,
adCmdTable
' Display the attributes of the connection.
Debug.Print "Connection attributes = " & _
cnn1.Attributes
' Display attributes of the Employee table fields
Debug.Print "Field attributes:"
For Each fldLoop In rstEmployees.Fields
Debug.Print " " & fldLoop.Name & " = " & _
fldLoop.Attributes
Next fldLoop
' Display attributes of the Employee table properties.
Debug.Print "Property attributes:"
For Each proLoop In rstEmployees.Properties
Debug.Print " " & proLoop.Name & " = " & _
proLoop.Attributes
Next proLoop
rstEmployees.Close
cnn1.Close
End Sub
ADO
Parameter Object Direction Property

Indicates whether the Parameter
object represents an input parameter, an output parameter, or both, or if the
parameter is the return value from a stored procedure.

Direction Property Return Values

Sets or returns one of the following ParameterDirectionEnum
values

Constant

Description

AdParamInput

Default. Indicates an input
parameter.

AdParamOutput

Indicates an output parameter.

AdParamInputOutput

Indicates a two-way parameter.

AdParamReturnValue

Indicates a return value.

Direction Property Remarks

Use the Direction property to
specify how a parameter is passed to or from a procedure. The Direction

property is read/write; this allows you to work with providers that do not
return this information, or to set this information when you do not want ADO to make an extra call
to the provider to retrieve parameter information.

Not all providers can determine the
direction of parameters in their stored procedures. In these cases, you must
set the Direction property prior to executing the query.

ADO
Parameter Object Name Property

The name of an object.

Name Property Return Values (ADO Parameter Object)

Sets or returns a String value. The
value is read/write on a Parameter object.

Name Property Remarks (ADO Parameter Object)

Use the Name property to assign a
name to or retrieve the name of a Parameter object.

For Parameter objects not yet
appended to the ADO Parameters
Collection
, the Name property is read/write. For appended Parameter

objects and all other objects, the Name property is read-only. Names do
not have to be unique within a collection.

ADO Parameter
Object NumericScale Property

The scale of Numeric values in a Parameter
object.

NumericScale Property Return Values

Sets or returns a Byte value,
indicating the number of decimal places to which numeric values will be
resolved.

NumericScale Property Remarks

Use the NumericScale property to
determine how many digits to the right of the decimal point will be used to
represent values for a numeric Parameter object.

For Parameter objects, the NumericScale

property is read/write.

ADO
Parameter Object Precision Property

The degree of precision for Numeric

values in a Parameter object.

Precision Property Return Values (ADO Parameter Object)

Sets or returns a Byte value,
indicating the maximum total number of digits used to represent values. The
value is read/write on a Parameter object.

Precision Property Remarks (ADO Parameter Object)

Use the Precision property to
determine the maximum number of digits used to represent values for a numeric Parameter
object.

ADOParameter Object Size Property

The maximum size, in bytes or characters,
of a Parameter object.

Size Property Return Values (ADO Parameter Object)

Sets or returns a Long value that
indicates the maximum size in bytes or characters of a value in a Parameter
object.

Size Property Remarks (ADO Parameter Object)

Use the Size property to determine
the maximum size for values written to or read from the ADO
Parameter Object Value Property
of a Parameter object. The Size

property is read/write. If you specify a variable-length data type for a Parameter
object, you must set the object’s Size property before appending it to
the ADO
Parameters Collection
; otherwise an error occurs. If you have already
appended the Parameter object to the Parameters collection of an ADO Command
Object
and you change its type to a variable-length data type, you must set
the Parameter object’s Size property before executing the Command

object; otherwise an error occurs.

If you use the ADO Collections
Refresh Method
to obtain parameter information from the provider and it
returns one or more variable-length data type Parameter objects, ADO may
allocate memory for the parameters based on their maximum potential size, which
could cause an error during execution. To prevent an error, you should
explicitly set the Size property for these parameters before executing
the command.

Size Property Example (ADO Parameter Object)

See ActiveConnection property
example.

ADO
Parameter Object Type Property

The operational type or data type of a Parameter
object.

Type Property Return Values (ADO Parameter Object)

Sets or returns one of the following DataTypeEnum
values. The corresponding OLE DB type indicators are as follows:

Constant

Description

adArray

OR’d together with another type to
indicate that the data is a safe-array of that type (DBTYPE_ARRAY).

adBigInt

An 8-byte signed integer
(DBTYPE_I8).

adBinary

A binary value (DBTYPE_BYTES).

adBoolean

A Boolean value (DBTYPE_BOOL).

adByRef

Or’d together with another type to
indicate that the data is a pointer to data of the other type (DBTYPE_BYREF).

adBSTR

A null-terminated character string
(Unicode) (DBTYPE_BSTR).

adChar

A String value (DBTYPE_STR).

adCurrency

A currency value (DBTYPE_CY).
Currency is a fixed-point number with 4 digits to the right of the decimal
point. It is stored in an 8-byte signed integer scaled by 10,000.

adDate

A date value (DBTYPE_DATE). A date
is stored as a Double, the whole part of which is the number of days since
December 30, 1899, and the fractional part of which is the fraction of a day.

adDBDate

A date value (yyyymmdd)
(DBTYPE_DBDATE).

adDBTime

A time value (hhmmss)
(DBTYPE_DBTIME).

adDBTimeStamp

A date-time stamp (yyyymmddhhmmss
plus a fraction in billionths) (DBTYPE_DBTIMESTAMP).

adDecimal

An exact numeric value with a fixed
precision and scale (DBTYPE_DECIMAL).

adDouble

A double-precision floating point
value (DBTYPE_R8).

adEmpty

No value was specified
(DBTYPE_EMPTY).

adError

A 32-bit error code (DBTYPE_ERROR).

adGUID

A globally unique identifier (GUID)
(DBTYPE_GUID).

adIDispatch

A pointer to an IDispatch

interface on an OLE object (DBTYPE_IDISPATCH).

adInteger

A 4-byte signed integer (DBTYPE_I4).

adIUnknown

A pointer to an IUnknown
interface on an OLE object (DBTYPE_IUNKNOWN).

adLongVarBinary

A long binary value.

adLongVarChar

A long String value.

adLongVarWChar

A long null-terminated string value.

adNumeric

An exact numeric value with a fixed
precision and scale (DBTYPE_NUMERIC).

adSingle

A single-precision floating point
value (DBTYPE_R4).

adSmallInt

A 2-byte signed integer (DBTYPE_I2).

adTinyInt

A 1-byte signed integer (DBTYPE_I1).

adUnsignedBigInt

An 8-byte unsigned integer
(DBTYPE_UI8).

adUnsignedInt

A 4-byte unsigned integer (DBTYPE_UI4).

adUnsignedSmallInt

A 2-byte unsigned integer
(DBTYPE_UI2).

adUnsignedTinyInt

A 1-byte unsigned integer
(DBTYPE_UI1).

adUserDefined

A user-defined variable
(DBTYPE_UDT).

adVarBinary

A binary value.

adVarChar

A String value.

adVariant

An Automation Variant
(DBTYPE_VARIANT).

adVector

OR’d together with another type to
indicate that the data is a DBVECTOR structure, as defined by OLE DB, that
contains a count of elements and a pointer to data of the other type
(DBTYPE_VECTOR).

adVarWChar

A null-terminated Unicode character
string.

adWChar

A null-terminated Unicode character
string (DBTYPE_WSTR).

Type Property Remarks (ADO Parameter Object)

For Parameter objects, the Type

property is read/write.

ADO
Parameter Object Value Property

Indicates the value assigned to a Parameter

object.

Value Property Return Values (ADO Parameter Object)

Sets or returns a Variant value. Default
value depends on the ADO
Parameter Object Type Property
.

Value Property Remarks (ADO Parameter Object)

Use the Value property to set or
return parameter values with Parameter objects.

ADO
allows setting and returning long binary data with the Value property.

ADO Parameter
Object Remarks


The Properties collection
is not currently supported on UNIX.

Many providers support parameterized
commands. These are commands where the desired action is defined once, but
variables (or parameters) are used to alter some details of the command. For
example, an SQL SELECT statement could use a parameter to define the matching
criteria of a WHERE clause, and another to define the column name for a SORT BY
clause.

The Parameter objects represent
parameters associated with parameterized queries, or the in/out arguments and
the return values of stored procedures. Depending on the functionality of the
provider, some collections, methods, or properties of a Parameter object
may not be available.

The collections, methods, and properties
of a Parameter object are used to:

·
set or return the name of a parameter with the ADO
Parameter Object Name Property
.

·
set or return the value of a parameter with the ADO
Parameter Object Value Property
.

·

set or return parameter characteristics with the
ADO
Parameter Object Attributes Property
, ADO
Parameter Object Direction Property
, ADO
Parameter Object Precision Property
, ADO
Parameter Object NumericScale Property
, ADO
Parameter Object Size Property
, and ADO
Parameter Object Type Property
properties.

·
pass long binary or character data to a
parameter with the ADO
Parameter Object AppendChunk Method
.

If you know the names and properties of
the parameters associated with the stored procedure or parameterized query you
wish to call, you can use the CreateParameter method to create Parameter
objects with the appropriate property settings and use the ADO Collections
Append Method
to add them to the ADO Parameters
Collection
. This lets you set and return parameter values without having to
call the ADO
Collections Refresh Method
on the Parameters collection to retrieve
the parameter information from the provider, a potentially resource-intensive

ADO
Property Object


The ADO Property

object represents a dynamic characteristic of an ADO object that is defined by the provider. This
object is not currently supported on UNIX.

In this section:

ADO
Property Object Properties

ADO
Property Object Remarks

ADOProperty Object Properties


Property

Description

Attributes

One or more characteristics of a
property.

Name

The name of the property.

Type

The operational or data type of the
property.

Value

The value assigned to the property.

In this section:

ADO
Property Object Attributes Property

ADO
Property Object Name Property

ADO
Property Object Type Property

ADO
Property Object Value Property

ADO
Property Object Attributes Property

One or more characteristics of an object.

Attributes Property Return Values (ADO Property Object)

Sets or returns a Long value.

Attributes Property Property (ADO Property Object)

For a Property object, the Attributes
property is read-only, and its value can be the sum of any one or more of these
PropertyAttributesEnum values:

Value

Description

adPropNotSupported

The property is not supported by the
provider.

adPropRequired

The user must specify a value for
this property before the data source is initialized.

adPropOptional

The user does not need to specify a
value for this property before the data source is initialized.

adPropRead

The user can read the property.

adPropWrite

The user can set the property.

Attributes Property Remarks (ADO Property Object)

Use the Attributes property to set
or return characteristics of Property objects.

When you set multiple attributes, you can
sum the appropriate constants. If you set the property value to a sum including
incompatible constants, an error occurs.

Attributes Property Examples (ADO Property Object)

This Visual Basic example displays the
value of the Attributes property for Property objects. It uses
the ADO
Property Object Name Property
to display the name of each Property

object.

Public Sub AttributesX
Dim cnn1 As ADODB.Connection
Dim rstEmployees As ADODB.Recordset
Dim fldLoop As ADODB.Field
Dim proLoop As ADODB.Property
Dim strCnn As String
' Open connection and recordset.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set cnn1 = New ADODB.Connection
cnn1.Open strCnn
Set rstEmployees = New ADODB.Recordset
rstEmployees.Open "employee", cnn1, , ,
adCmdTable
' Display attributes of the Employee table properties.
Debug.Print "Property attributes:"
For Each proLoop In rstEmployees.Properties
Debug.Print " " & proLoop.Name & " = " & _
proLoop.Attributes
Next proLoop
rstEmployees.Close
cnn1.Close
End Sub
ADO
Property Object Name Property

The name of an object.

Name Property Return Values (ADO Property Object)

Sets or returns a String value. The
value is read-only on a Property object.

Name Property Remarks (ADO Property Object)

Use the Name property to assign a
name to or retrieve the name of a Property object.

You can retrieve the Name property
of an object by an ordinal reference, after which the object can be referred to
directly by name. For example, if rstMain.Properties(20).Name yields
Updatability, you can subsequently refer to this property as
rstMain.Properties(“Updatability”).

Name Property Examples (ADO Property Object)

See the ADO
Property Object Attributes Property
example.

ADO
Property Object Type Property

The operational type or data type of a Property
object.

Type Property Return Values (ADO Property Object)

Sets or returns one of the following DataTypeEnum
values. The corresponding OLE DB type indicators are as follows:

Constant

Description

adArray

Or’d together with another type to
indicate that the data is a safe-array of that type (DBTYPE_ARRAY).

adBigInt

An 8-byte signed integer
(DBTYPE_I8).

adBinary

A binary value (DBTYPE_BYTES).

adBoolean

A Boolean value (DBTYPE_BOOL).

adByRef

Or’d together with another type to
indicate that the data is a pointer to data of the other type (DBTYPE_BYREF).

adBSTR

A null-terminated character string
(Unicode) (DBTYPE_BSTR).

adChar

A String value (DBTYPE_STR).

adCurrency

A currency value (DBTYPE_CY).
Currency is a fixed-point number with 4 digits to the right of the decimal
point. It is stored in an 8-byte signed integer scaled by 10,000.

adDate

A date value (DBTYPE_DATE). A date
is stored as a Double, the whole part of which is the number of days since
December 30, 1899, and the fractional part of which is the fraction of a day.

adDBDate

A date value (yyyymmdd)
(DBTYPE_DBDATE).

adDBTime

A time value (hhmmss)
(DBTYPE_DBTIME).

adDBTimeStamp

A date-time stamp (yyyymmddhhmmss
plus a fraction in billionths) (DBTYPE_DBTIMESTAMP).

adDecimal

An exact numeric value with a fixed
precision and scale (DBTYPE_DECIMAL).

adDouble

A double-precision floating point
value (DBTYPE_R8).

adEmpty

No value was specified
(DBTYPE_EMPTY).

adError

A 32-bit error code (DBTYPE_ERROR).

adGUID

A globally unique identifier (GUID)
(DBTYPE_GUID).

adIDispatch

A pointer to an IDispatch

interface on an OLE object (DBTYPE_IDISPATCH).

adInteger

A 4-byte signed integer (DBTYPE_I4).

adIUnknown

A pointer to an IUnknown
interface on an OLE object (DBTYPE_IUNKNOWN).

adLongVarBinary

A long binary value.

adLongVarChar

A long String value.

adLongVarWChar

A long null-terminated string value.

adNumeric

An exact numeric value with a fixed
precision and scale (DBTYPE_NUMERIC).

adSingle

A single-precision floating point
value (DBTYPE_R4).

adSmallInt

A 2-byte signed integer (DBTYPE_I2).

adTinyInt

A 1-byte signed integer (DBTYPE_I1).

adUnsignedBigInt

An 8-byte unsigned integer
(DBTYPE_UI8).

adUnsignedInt

A 4-byte unsigned integer
(DBTYPE_UI4).

adUnsignedSmallInt

A 2-byte unsigned integer
(DBTYPE_UI2).

adUnsignedTinyInt

A 1-byte unsigned integer
(DBTYPE_UI1).

adUserDefined

A user-defined variable
(DBTYPE_UDT).

adVarBinary

A binary value.

adVarChar

A String value.

adVariant

An Automation Variant
(DBTYPE_VARIANT).

adVector

OR’d together with another type to
indicate that the data is a DBVECTOR structure, as defined by OLE DB, that
contains a count of elements and a pointer to data of the other type
(DBTYPE_VECTOR).

adVarWChar

A null-terminated Unicode character
string.

adWChar

A null-terminated Unicode character
string (DBTYPE_WSTR).

Type Property Remarks (ADO Property Object)

The Type property is read-only.

ADO Property
Object Value Property

Indicates the value assigned to a Property
object.

Value Property Return Values (ADO Property Object)

Sets or returns a Variant value. Default
value depends on the ADO
Property Object Type Property
.

Value Property Remarks (ADO Property Object)

Use the Value property to set or
return property settings with Property objects.

Value Property Example (ADO Property Object)

This Visual Basic example demonstrates the

Value property with Field and Property objects by
displaying field and property values for the Employees table.

Public Sub ValueX()
Dim rstEmployees As ADODB.Recordset
Dim fldLoop As ADODB.Field
Dim prpLoop As ADODB.Property
Dim strCnn As String
' Open recordset with data from Employee table.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set rstEmployees = New ADODB.Recordset
rstEmployees.Open "employee", strCnn, , , adCmdTable
Debug.Print "Field values in rstEmployees"
' Enumerate the Fields collection of the Employees
' table.
For Each fldLoop In rstEmployees.Fields
` Because Value is the default property of a
` Field object, the use of the actual keyword
` here is optional.
Debug.Print " " & fldLoop.Name & " = " &
fldLoop.Value
Next fldLoop
Debug.Print "Property values in rstEmployees"
' Enumerate the Properties collection of the
' Recordset object.
For Each prpLoop In rstEmployees.Properties
' Because Value is the default property of a
' Property object, the use of the actual keyword
' here is optional.
Debug.Print " " & prpLoop.Name & " = " &
prpLoop.Value
Next prpLoop
rstEmployees.Close
End Sub

ADO
Property Object Remarks


ADO
objects have two types of properties: built-in and dynamic. Built-in properties
are those properties implemented in ADO

and immediately available to any new object, using the familiar
MyObject.Property syntax.

Built-in properties do not appear as Property
objects in an object’s ADO Properties
Collection
, so while you can change their values, you cannot modify their
characteristics or delete them.

Dynamic properties are defined by the
underlying data provider, and appear in the Properties collection for
the appropriate ADO

object. For example, a property specific to the provider may indicate if an ADO Recordset
Object
supports transactions or updating. These additional properties will
appear as Property objects in that Recordset object’s Properties
collection. Dynamic properties can be referenced only through the collection,
using the MyObject.Properties(0) or MyObject.Properties(“Name”)
syntax.

A dynamic Property object has four
built-in properties:

·
The ADO
Property Object Name Property
is a string that identifies the property.

·
The ADO
Property Object Type Property
is an integer that specifies the property
data type.

·
The ADO
Property Object Value Property
is a Variant that contains the property
setting.

·
The ADO
Property Object Attributes Property
is a long value that indicates
characteristics of the property specific to the provider.

ADORecordset Object


The Recordset object represents the
entire set of records from a database table or the results of an executed
command.

In this section:

ADO
Recordset Object Collections

ADO
Recordset Object Methods

UpdateBatch
Method Remarks

ADO Recordset
Object Properties

ADO Recordset
Object Remarks

ADORecordset Object Collections


Collection

Description

Fields

All the stored Field objects
of a Recordset object.

Properties

All the Property objects for
a specific instance of a Recordset object. This collection is not
currently supported on UNIX.

ADO
Recordset Object Methods


Method

Description

AddNew

Creates a new record for an
updatable Recordset object.

CancelBatch

Cancels a pending batch update. This
method is not currently supported on UNIX.

CancelUpdate

Cancels any changes made to the
current record prior to calling the Update method.

Clone

Creates a new Recordset

object from an existing Recordset object. This method is not
currently supported on UNIX.

Close

Closes an open Recordset
object and any dependent objects.

Delete

Deletes the current record or group
of records from a Recordset object.

GetRows

Retrieves multiple rows from a Recordset

object into an array.

Move

Moves the position of the current
record in a Recordset object.

MoveFirst, MoveLast, MoveNext,
MovePrevious

Moves to the first, first, last,
next record in a Recordset object and makes that record the current
record.

NextRecordset

Clears the current Recordset
object and returns the next recordset by advancing through a series of
commands. This method is not currently supported on UNIX.

Open

Opens a cursor.

Requery

Updates the data in a recordset by
re-executing the query on which the object is based.

Resync

Refreshes the data in the Recordset

object from the underlying database.

Supports

Determines whether a specified Recordset
object supports a particular type of functionality.

Update

Saves any changes you make to the
current record of a Recordset object.

UpdateBatch

Writes all pending batch updates. This
method is not currently supported on UNIX.

In this section:

ADO
Recordset Object AddNew Method

ADO
Recordset Object CancelBatch Method

ADO
Recordset Object CancelUpdate Method

ADO
Recordset Object Clone Method

ADO
Recordset Object Delete Method

ADO
Recordset Object GetRows Method

ADO
Recordset Object Move Method

ADO
Recordset Object MoveFirst, MoveLast, MoveNext, MovePrevious Methods

ADO
Recordset Object NextRecordset Method

ADO
Recordset Object Open Method

ADO
Recordset Object Requery Method

ADO Recordset
Object Resync Method

ADO Recordset
Object Supports Method

ADO Recordset
Object Update Method

ADO Recordset
Object UpdateBatch Method

ADO
Recordset Object AddNew Method

Creates a new record for an updateable Recordset

object.

AddNew Method Syntax
recordset.AddNew Fields, Values
AddNew Method Parameters

Fields

An optional single name or an array of
names or ordinal positions of the fields in the new record.

Values

An optional single value or an array of
values for the fields in the new record. If Fields is an array, Values
must also be an array with the same number of members; otherwise, an error
occurs. The order of field names must match the order of field values in each
array.

AddNew Method Remarks

Use the AddNew method to create and
initialize a new record. Use the ADO
Recordset Object Supports Method
with adAddNew to verify whether you
can add records to the current Recordset object.

After you call the AddNew method,
the new record becomes the current record and remains current after you call
the ADO
Recordset Object Update Method
. If the Recordset object does not
support bookmarks, you may not be able to access the new record once you move
to another record. Depending on your cursor type, you may need to call the ADO
Recordset Object Requery Method
to make the new record accessible.

If you call AddNew while editing
the current record or while adding a new record, ADO calls the Update method to save
any changes and then creates the new record.

The behavior of the AddNew method
depends on the updating mode of the Recordset object and whether or not
you pass the Fields and Values arguments.

In immediate update mode (the
provider writes changes to the underlying data source once you call the Update

method), calling the AddNew method without arguments sets the ADO
Recordset Object EditMode Property
to adEditAdd. The provider caches
any field value changes locally. Calling the Update method posts the new
record to the database and resets the EditMode property to adEditNone.
If you pass the Fields and Values arguments, ADO immediately posts the new record to the
database (no Update call is necessary); the EditMode property
value does not change (adEditNone).

In batch update mode (the
provider caches multiple changes and writes them to the underlying data source
only when you call the UpdateBatch method), calling the AddNew
method without arguments sets the EditMode property to adEditAdd.
The provider caches any field value changes locally. Calling the Update

method adds the new record to the current recordset and resets the EditMode
property to adEditNone, but the provider does not post the changes to
the underlying database until you call the ADO
Recordset Object UpdateBatch Method
. If you pass the Fields and Values
arguments, ADO
sends the new record to the provider for storage in a cache; you need to call
the UpdateBatch method to post the new record to the underlying database.

Batch updating is not currently supported on UNIX.

ADO
Recordset Object CancelBatch Method

Cancels a pending batch update. This
method is not currently supported on UNIX.

CancelBatch Method Syntax
recordset.CancelBatch AffectRecords
CancelBatch Method Parameters

AffectRecords

An optional AffectEnum value that
determines how many records the CancelBatch method will affect. It can
be one of the following constants:

Constant

Description

adAffectCurrent

Cancels pending updates only for the
current record.

adAffectGroup

Cancels pending updates for records
that satisfy the current ADO
Recordset Object Filter Property
setting. You must set the Filter

property to one of the valid predefined constants in order to use this
option.

adAffectAll

Default. Cancels pending updates for
all the records in the Recordset object, including any hidden by the
current Filter property setting.

CancelBatch Method Remarks

Use the CancelBatch method to
cancel any pending updates in a recordset in batch update mode. If the
recordset is in immediate update mode, calling CancelBatch without adAffectCurrent
generates an error.

If you are editing the current record or
are adding a new record when you call CancelBatch, ADO first calls the ADO
Recordset Object CancelUpdate Method
to cancel any cached changes; after
that, all pending changes in the recordset are canceled.

It’s possible that the current record will
be indeterminable after a CancelBatch call, especially if you were in
the process of adding a new record. For this reason, it is prudent to set the
current record position to a known location in the recordset after the CancelBatch

call. For example, call the ADO
Recordset Object MoveFirst, MoveLast, MoveNext, MovePrevious Methods
.

If the attempt to cancel the pending
updates fails because of a conflict with the underlying data (for example, a
record has been deleted by another user), the provider returns warnings to the ADO Errors
Collection
but does not halt program execution. A run-time error occurs
only if there are conflicts on all the requested records. Use the Filter
property (adFilterAffectedRecords) and the ADO
Recordset Object Status Property
to locate records with conflicts.

CancelBatch Method Examples

This Visual Basic example demonstrates the
ADO
Recordset Object UpdateBatch Method
in conjunction with the CancelBatch method.

Public Sub UpdateBatchX()
Dim rstTitles As ADODB.Recordset
Dim strCnn As String
Dim strTitle As String
Dim strMessage As String
` Assign connection string to variable.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set rstTitles = New ADODB.Recordset
rstTitles.CursorType = adOpenKeyset
rstTitles.LockType = adLockBatchOptimistic
rstTitles.Open "titles", strCnn, , , adCmdTable
rstTitles.MoveFirst
` Loop through recordset and ask user if she wants
` to change the type for a specified title.
Do Until rstTitles.EOF
If Trim(rstTitles!Type) = "psychology" Then
strTitle = rstTitles!Title
strMessage = "Title: " & strTitle & vbCr & _
"Change type to self help?"
If MsgBox(strMessage, vbYesNo) = vbYes Then
rstTitles!Type = "self_help"
End If
End If
rstTitles.MoveNext
Loop
` Ask if the user wants to commit to all the
` changes made above.
If MsgBox("Save all changes?", vbYesNo) = vbYes Then
rstTitles.UpdateBatch
Else
rstTitles.CancelBatch
End If
` Print current data in recordset.
rstTitles.Requery
rstTitles.MoveFirst
Do While Not rstTitles.EOF
Debug.Print rstTitles!Title & " - " & rstTitles!Type
rstTitles.MoveNext
Loop
` Restore original values because this is a demonstration.
rstTitles.MoveFirst
Do Until rstTitles.EOF
If Trim(rstTitles!Type) = "self_help" Then
rstTitles!Type = "psychology"
End If
rstTitles.MoveNext
Loop
rstTitles.UpdateBatch
rstTitles.Close
End Sub
ADO
Recordset Object CancelUpdate Method

Cancels any changes made to the current
record or to a new record prior to calling the Update method.

CancelUpdate Method Syntax
recordset.CancelUpdate
CancelUpdate Method Remarks

Use the CancelUpdate method to
cancel any changes made to the current record or to discard a newly added
record.

Note iconNote

You cannot undo changes to the current record or to a new
record after you call the ADO
Recordset Object Update Method
unless the changes are either part of a
transaction that you can roll back with the RollbackTrans method or
part of a batch update that you can cancel with the ADO
Recordset Object CancelBatch Method
.

If you are adding a new record
when you call the CancelUpdate method, the record that was current prior
to the ADO
Recordset Object AddNew Method
call becomes the current record again. If
you have not changed the current record or added a new record, calling the CancelUpdate
method generates an error.

CancelUpdate Method Examples

These Visual Basic examples demonstrate
the ADO
Recordset Object Update Method
in conjunction with the CancelUpdate
method.

Public Sub UpdateX()
Dim rstEmployees As ADODB.Recordset
Dim strOldFirst As String
Dim strOldLast As String
Dim strMessage As String
` Open recordset with names from Employee table.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set rstEmployees = New ADODB.Recordset
rstEmployees.CursorType = adOpenKeyset
rstEmployees.LockType = adLockOptimistic
rstEmployees.Open "SELECT fname, lname " & _
"FROM Employee ORDER BY lname", strCnn, , , adCmdText
` Store original data.
strOldFirst = rstEmployees!fname
strOldLast = rstEmployees!lname
` Change data in edit buffer.
rstEmployees!fname = "Linda"
rstEmployees!lname = "Kobara"
` Show contents of buffer and get user input.
strMessage = "Edit in progress:" & vbCr & _
" Original data = " & strOldFirst & " " & _
strOldLast & vbCr & " Data in buffer = " & _
rstEmployees!fname & " " & rstEmployees!lname & vbCr & vbCr & _
"Use Update to replace the original data with " & _
"the buffered data in the Recordset?"
If MsgBox(strMessage, vbYesNo) = vbYes Then
rstEmployees.Update
Else
rstEmployees.CancelUpdate
End If
` Show the resulting data.
MsgBox "Data in recordset = " & rstEmployees!fname & " " & _
rstEmployees!lname
` Restore original data because this is a demonstration.
If Not (strOldFirst = rstEmployees!fname And _
strOldLast = rstEmployees!lname) Then
rstEmployees!fname = strOldFirst
rstEmployees!lname = strOldLast
rstEmployees.Update
End If
rstEmployees.Close
End Sub

This example demonstrates the Update
method in conjunction with the AddNew method.

Public Sub UpdateX2()
Dim cnn1 As ADODB.Connection
Dim rstEmployees As ADODB.Recordset
Dim strEmpID As String
Dim strOldFirst As String
Dim strOldLast As String
Dim strMessage As String
` Open a connection.
Set cnn1 = New ADODB.Connection
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
cnn1.Open strCnn
` Open recordset with data from Employee table.
Set rstEmployees = New ADODB.Recordset
rstEmployees.CursorType = adOpenKeyset
rstEmployees.LockType = adLockOptimistic
rstEmployees.Open "employee", cnn1, , , adCmdTable
rstEmployees.AddNew
strEmpID = "B-S55555M"
rstEmployees!emp_id = strEmpID
rstEmployees!fname = "Bill"
rstEmployees!lname = "Sornsin"
` Show contents of buffer and get user input.
strMessage = "AddNew in progress:" & vbCr & _
"Data in buffer = " & rstEmployees!emp_id & ", " & _
rstEmployees!fname & " " & rstEmployees!lname & vbCr & vbCr & _
"Use Update to save buffer to recordset?"
If MsgBox(strMessage, vbYesNoCancel) = vbYes Then
rstEmployees.Update
` Go to the new record and show the resulting data.
MsgBox "Data in recordset = " & rstEmployees!emp_id & ", " & _
rstEmployees!fname & " " & rstEmployees!lname
Else
rstEmployees.CancelUpdate
MsgBox "No new record added."
End If
` Delete new data because this is a demonstration.
cnn1.Execute "DELETE FROM employee WHERE emp_id = '" & strEmpID & "'"
rstEmployees.Close
End Sub
ADO
Recordset Object Clone Method

Creates a duplicate Recordset object
from an existing Recordset object. This method is not currently
supported on UNIX.

Clone Method Syntax
Set rstDuplicate = rstOriginal.Clone ()
Clone Method Parameters

rstDuplicate

An object variable identifying the
duplicate Recordset object you’re creating.

rstOriginal

An object variable identifying the Recordset
object you want to duplicate.

Clone Method Remarks

Use the Clone method to create
multiple, duplicate Recordset objects, particularly if you want to be
able to maintain more than one current record in a given set of records. Using
the Clone method is more efficient than creating and opening a new Recordset
object with the same definition as the original.

The current record of a newly created
clone is set to the first record.

Changes you make to one Recordset
object are visible in all of its clones regardless of cursor type. However,
once you execute the ADO
Recordset Object Requery Method
on the original Recordset,
the clones will no longer be synchronized to the original.

Closing the original recordset does not
close its copies; closing a copy does not close the original or any of the
other copies.

You can only clone a Recordset
object that supports bookmarks. Bookmark values are interchangeable; that is, a
bookmark reference from one Recordset object refers to the same record
in any of its clones.

Clone Method Return Values

Returns a Recordset object
reference.

ADO
Recordset Object Close Method

Closes an open object and any dependent
objects.

Close Method Syntax
object.Close
Close Method Remarks

Use the Close method to close a Recordset
object to free any associated system resources. Closing an object does not
remove it from memory; you may change its property settings and open it again
later. To completely eliminate an object from memory, set the object variable
to Nothing.

Using the Close method to close a Recordset

object releases the associated data and any exclusive access you may have had
to the data through this particular Recordset object. You can later call
the ADO
Recordset Object Open Method
to reopen the Recordset with the same
or modified attributes. While the Recordset object is closed, calling
any methods that require a live cursor generates an error.

If an edit is in progress while in
immediate update mode, calling the Close method generates an error; call
the ADO
Recordset Object Update Method
or ADO
Recordset Object CancelUpdate Method
first. If you close the Recordset

object during batch updating, all changes since the last ADO
Recordset Object UpdateBatch Method
call are lost.

If you use the Clone method to
create copies of an open Recordset object, closing the original or a
clone does not affect any of the other copies.

Close Method Examples

This VBScript example uses the Open
and Close methods on both Recordset and Connection objects
that have been opened.

<!-- #Include file="ADOVBS.INC" -->
<HTML><HEAD>
<TITLE>ADO 1.5 Open Method</TITLE>
</HEAD><BODY>
<FONT FACE="MS SANS SERIF" SIZE=2>
<Center><H3>ADO Open Method</H3>
<TABLE WIDTH=600 BORDER=0>
<TD VALIGN=TOP ALIGN=LEFT COLSPAN=3><FONT SIZE=2>
<!--- ADO Connection used to create 2 recordsets-->
<%
Set OBJdbConnection = Server.CreateObject("ADODB.Connection")
OBJdbConnection.Open "AdvWorks"
SQLQuery = "SELECT * FROM Customers"
'First Recordset RSCustomerList
Set RSCustomerList = OBJdbConnection.Execute(SQLQuery)
'Second Recordset RsProductist
Set RsProductList = Server.CreateObject("ADODB.Recordset")
RsProductList.CursorType = adOpenDynamic
RsProductList.LockType = adLockOptimistic
RsProductList.Open "Products", OBJdbConnection
%>
<TABLE COLSPAN=8 CELLPADDING=5 BORDER=0>
<!-- BEGIN column header row for Customer Table-->
<TR><TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Company Name</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Contact Name</FONT></TD>
<TD ALIGN=CENTER WIDTH=150 BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>E-mail address</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>City</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>State/Province</FONT></TD></TR>
<!--Display ADO Data from Customer Table-->
<% Do While Not RScustomerList.EOF %>
<TR><TD BGCOLOR="f7efde" ALIGN=CENTER>
<FONT STYLE="ARIAL NARROW" SIZE=1>
<%= RSCustomerList("CompanyName")%>
</FONT></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER>
<FONT STYLE="ARIAL NARROW" SIZE=1>
<%= RScustomerList("ContactLastName") & ", " %>
<%= RScustomerList("ContactFirstName") %>
</FONT></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER>
<FONT STYLE="ARIAL NARROW" SIZE=1>
<%= RScustomerList("ContactLastName")%>
</FONT></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER>
<FONT STYLE="ARIAL NARROW" SIZE=1>
<%= RScustomerList("City")%>
</FONT></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER>
<FONT STYLE="ARIAL NARROW" SIZE=1>
<%= RScustomerList("StateOrProvince")%>
</FONT></TD></TR>
<!-Next Row = Record Loop and add to html table-->
<%
RScustomerList.MoveNext
Loop
RScustomerList.Close
OBJdbConnection.Close
%>
</TABLE>
<HR>
<TABLE COLSPAN=8 CELLPADDING=5 BORDER=0>
<!-- BEGIN column header row for Product List Table-->
<TR><TD ALIGN=CENTER BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Product Type</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Product Name</FONT></TD>
<TD ALIGN=CENTER WIDTH=350 BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Product Description</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Unit Price</FONT></TD></TR>
<!-- Display ADO Data Product List-->
<% Do While Not RsProductList.EOF %>
<TR> <TD BGCOLOR="f7efde" ALIGN=CENTER>
<FONT STYLE="ARIAL NARROW" SIZE=1>
<%= RsProductList("ProductType")%>
</FONT></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER>
<FONT STYLE="ARIAL NARROW" SIZE=1>
<%= RsProductList("ProductName")%>
</FONT></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER>
<FONT STYLE="ARIAL NARROW" SIZE=1>
<%= RsProductList("ProductDescription")%>
</FONT></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER>
<FONT STYLE="ARIAL NARROW" SIZE=1>
<%= RsProductList("UnitPrice")%>
</FONT></TD>
<!-- Next Row = Record -->
<%
RsProductList.MoveNext
Loop
'Remove Objects from Memory Freeing
Set RsProductList = Nothing
Set OBJdbConnection = Nothing
%>
</TABLE></FONT></Center></BODY></HTML>
ADO
Recordset Object Delete Method

Deletes the current record or a group of
records.

Delete Method Syntax
recordset.Delete AffectRecords
Delete Method Parameters

AffectRecords

An optional AffectEnum value that
determines how many records the Delete method will affect. Can be one of
the following constants:

Constant

Description

adAffectCurrent

Default. Delete only the current
record.

adAffectGroup

Delete the records that satisfy the
current ADO
Recordset Object Filter Property
setting. You must set the Filter

property to one of the valid predefined constants in order to use this option.
The Filter property is not currently supported on UNIX.

Delete Method Remarks

Using the Delete method marks the
current record or a group of records in a Recordset object for deletion.
If the Recordset object doesn’t allow record deletion, an error occurs.
If you are in immediate update mode, deletions occur in the database
immediately. Otherwise, the records are marked for deletion from the cache and
the actual deletion happens when you call the ADO
Recordset Object UpdateBatch Method
. (Use the Filter property to
view the deleted records.)

Retrieving field values from the deleted
record generates an error. After deleting the current record, the deleted
record remains current until you move to a different record. Once you move away
from the deleted record, it is no longer accessible.

If you nest deletions in a transaction,
you can recover deleted records with the RollbackTrans method. If you
are in batch update mode, you can cancel a pending deletion or group of pending
deletions with the ADO
Recordset Object CancelBatch Method
.

If the attempt to delete records fails
because of a conflict with the underlying data (for example, a record has
already been deleted by another user), the provider returns warnings to the ADO Errors
Collection
, but does not halt program execution. A run-time error occurs
only if there are conflicts on all the requested records.

Delete Method Examples

This VBScript example uses the Delete
method to remove a specified record from a recordset.

<!-- #Include file="ADOVBS.INC" -->
<% Language = VBScript %>
<HTML>
<HEAD><TITLE>ADO 1.5 Delete Method</TITLE>
</HEAD><BODY>
<FONT FACE="MS SANS SERIF" SIZE=2>
<Center><H3>ADO Delete Method</H3>
<!--- ADO Connection Object used to create recordset-->
<%
'Create and Open Connection Object
Set OBJdbConnection = Server.CreateObject("ADODB.Connection")
OBJdbConnection.Open "AdvWorks"
'Create and Open Recordset Object
Set RsCustomerList = Server.CreateObject("ADODB.Recordset")
RsCustomerList.ActiveConnection = OBJdbConnection
RsCustomerList.CursorType = adOpenKeyset
RsCustomerList.LockType = adLockOptimistic
RsCustomerList.Source = "Customers"
RsCustomerList.Open
%>
<!-- Move to designated Record and Delete It -->
<%
If Not IsEmpty(Request.Form("WhichRecord")) Then
`Get value to move from Form Post method
Moves = Request.Form("WhichRecord")
RsCustomerList.Move CInt(Moves)
If Not RsCustomerList.EOF or RsCustomerList.BOF Then
RsCustomerList.Delete 1
RsCustomerList.MoveFirst
Else
Response.Write "Not a Valid Record Number"
RsCustomerList.MoveFirst
End If
End If
%>
<!-- BEGIN column header row for Customer Table-->
<TABLE COLSPAN=8 CELLPADDING=5 BORDER=0><TR>
<TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Company Name</FONT>
</TD>
<TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Contact Name</FONT>
</TD>
<TD ALIGN=CENTER WIDTH=150 BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Phone Number</FONT>
</TD>
<TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>City</FONT>
</TD>
<TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>State/Province</FONT>
</TD></TR>
<!--Display ADO Data from Customer Table Loop through Recordset
adding one Row to HTML Table each pass-->
<% Do While Not RsCustomerList.EOF %>
<TR><TD BGCOLOR="f7efde" ALIGN=CENTER>
<FONT STYLE="ARIAL NARROW" SIZE=1>
<%= RSCustomerList("CompanyName")%>
</FONT></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER>
<FONT STYLE="ARIAL NARROW" SIZE=1>
<%= RScustomerList("ContactLastName") & ", " %>
<%= RScustomerList("ContactFirstName") %>
</FONT></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER>
<FONT STYLE="ARIAL NARROW" SIZE=1>
<%= RScustomerList("PhoneNumber")%>
</FONT></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER>
<FONT STYLE="ARIAL NARROW" SIZE=1>
<%= RScustomerList("City")%>
</FONT></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER>
<FONT STYLE="ARIAL NARROW" SIZE=1>
<%= RScustomerList("StateOrProvince")%>
</FONT></TD>
</TR>
<!-Next Row = Record Loop and add to html table-->
<%
RScustomerList.MoveNext
Loop
%>
</Table></Center></FONT>
<!-- Do Client side Input Data Validation Move to named
 record and Delete it -->
<Center>
<H4>Clicking Button Will Remove Designated Record</H4>
<H5>There are <%=RsCustomerList.RecordCount - 1%> Records in this Set</H5>
<Form Method = Post Action = "Delete.asp" Name = Form>
<Input Type = Text Name = "WhichRecord" Size = 3></Form>
<Input Type = Button Name = cmdDelete Value = "Delete Record"></Center>
</BODY>
<Script Language = "VBScript">
Sub cmdDelete_OnClick
If IsNumeric(Document.Form.WhichRecord.Value) Then
Document.Form.WhichRecord.Value = CInt(Document.Form.WhichRecord.Value)
Dim Response
Response = MsgBox("Are You Sure About Deleting This Record?", vbYesNo, "ADO-ASP Example")
If Response = vbYes Then
Document.Form.Submit
End If
Else
MsgBox "You Must Enter a Valid Record Number",,"ADO-ASP Example"
End If
End Sub
</Script>
</HTML>
ADO
Recordset Object GetRows Method

Retrieves multiple records of a recordset
into an array.

GetRows Method Syntax
array = recordset.GetRows( Rows, Start, Fields )
GetRows Method Parameters

array

Two-dimensional Array containing
records.

Rows

An optional Long expression
indicating the number of records to retrieve. Default is adGetRowsRest

(-1).

Start

An optional String or Variant that
evaluates to the bookmark for the record from which the GetRows
operation should begin. You can also use one of the following BookmarkEnum
values:

Constant

Description

AdBookmarkCurrent

Start at the current record.

AdBookmarkFirst

Start at the first record.

AdBookmarkLast

Start at the last record.

Fields

An optional Variant representing
a single field name or ordinal position or an array of field names or ordinal
position numbers. ADO
returns only the data in these fields.

GetRows Method Return Values

Returns a two-dimensional array.

GetRows Method Remarks

Use the GetRows method to copy
records from a recordset into a two-dimensional array. The first subscript
identifies the field and the second identifies the record number. The array
variable is automatically dimensioned to the correct size when the GetRows

method returns the data.

If you do not specify a value for the Rows
argument, the GetRows method automatically retrieves all the records in
the Recordset object. If you request more records than are available, GetRows
returns only the number of available records.

If the Recordset object supports
bookmarks, you can specify at which record the GetRows method should
begin retrieving data by passing the value of that record’s ADO
Recordset Object Bookmark Property
.

If you want to restrict the fields the GetRows
call returns, you can pass either a single field name/number or an array of
field names/numbers in the Fields argument.

After you call GetRows, the next
unread record becomes the current record, or the ADO
Recordset Object BOF, EOF Properties
property is set to True
if there are no more records.

GetRows Method Examples

This Visual Basic example uses the GetRows

method to retrieve a specified number of rows from a recordset and to fill an
array with the resulting data. The GetRows method will return fewer than
the desired number of rows in two cases: either if EOF has been reached,
or if GetRows tried to retrieve a record that was deleted by another
user. The function returns False only if the second case occurs. The GetRowsOK
function is required for this procedure to run.

Public Sub GetRowsX()
Dim rstEmployees As ADODB.Recordset
Dim strCnn As String
Dim strMessage As String
Dim intRows As Integer
Dim avarRecords As Variant
Dim intRecord As Integer
' Open recordset with names and hire dates from employee table.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set rstEmployees = New ADODB.Recordset
rstEmployees.Open "SELECT fName, lName, hire_date " & _
"FROM Employee ORDER BY lName", strCnn, , , adCmdText
Do While True
` Get user input for number of rows.
strMessage = "Enter number of rows to retrieve."
intRows = Val(InputBox(strMessage))
If intRows <= 0 Then Exit Do
` If GetRowsOK is successful, print the results,
` noting if the end of the file was reached.
If GetRowsOK(rstEmployees, intRows, _
avarRecords) Then
If intRows > UBound(avarRecords, 2) + 1 Then
Debug.Print "(Not enough records in " & _
"Recordset to retrieve " & intRows & _
" rows.)"
End If
Debug.Print UBound(avarRecords, 2) + 1 & _
" records found."
` Print the retrieved data.
For intRecord = 0 To UBound(avarRecords, 2)
Debug.Print " " & _
avarRecords(0, intRecord) & " " & _
avarRecords(1, intRecord) & ", " & _
avarRecords(2, intRecord)
Next intRecord
Else
` Assuming the GetRows error was due to data
` changes by another user, use Requery to
` refresh the Recordset and start over.
If MsgBox("GetRows failed--retry?", _
vbYesNo) = vbYes Then
rstEmployees.Requery
Else
Debug.Print "GetRows failed!"
Exit Do
End If
End If
` Because using GetRows leaves the current
` record pointer at the last record accessed,
` move the pointer back to the beginning of the
` Recordset before looping back for another search.
rstEmployees.MoveFirst
Loop
rstEmployees.Close
End Sub
Public Function GetRowsOK(rstTemp As ADODB.Recordset, _
intNumber As Integer, avarData As Variant) As Boolean
` Store results of GetRows method in array.
avarData = rstTemp.GetRows(intNumber)
` Return False only if fewer than the desired
` number of rows were returned, but not because the
` end of the Recordset was reached.
If intNumber > UBound(avarData, 2) + 1 And _
Not rstTemp.EOF Then
GetRowsOK = False
Else
GetRowsOK = True
End If
End Function
ADO
Recordset Object Move Method

Moves the position of the current record
in a Recordset object.

Move Method Syntax
recordset.Move NumRecords, Start
Move Method Parameters

NumRecords

A signed Long expression specifying
the number of records the current record position moves.

Start

An optional String or Variant that
evaluates to a bookmark. You can also use one of the following BookmarkEnum
values:

Constant

Description

AdBookmarkCurrent

Default. Start at the current
record.

AdBookmarkFirst

Start at the first record.

AdBookmarkLast

Start at the last record.

Move Method Remarks

The Move method is supported on all
Recordset objects.

If the NumRecords argument is
greater than zero, the current record position moves forward (toward the end of
the recordset). If NumRecords is less than zero, the current record
position moves backward (toward the beginning of the recordset).

If the Move call would move the
current record position to a point before the first record, ADO sets the current record to the position
before the first record in the recordset (BOF is True). An
attempt to move backward when the ADO
Recordset Object BOF, EOF Properties
property is already True

generates an error.

If the Move call would move the
current record position to a point after the last record, ADO sets the current record to the position
after the last record in the recordset (EOF is True). An attempt
to move forward when the ADO
Recordset Object BOF, EOF Properties
property is already True

generates an error.

Calling the Move method from an
empty Recordset object generates an error.

If you pass the Start argument,
the move is relative to the record with this bookmark, assuming the Recordset

object supports bookmarks. If not specified, the move is relative to the
current record.

If you are using the ADO
Recordset Object CacheSize Property
to locally cache records from the
provider, passing a NumRecords that moves the current record position
outside of the current group of cached records forces ADO to retrieve a new group of records
starting from the destination record. The CacheSize property determines
the size of the newly retrieved group, and the destination record is the first
record retrieved.

If the Recordset object is
forward-only, a user can still pass a NumRecords less than zero as
long as the destination is within the current set of cached records. If the Move
call would move the current record position to a record before the first cached
record, an error will occur. Thus, you can use a record cache that supports
full scrolling over a provider that only supports forward scrolling. Because
cached records are loaded into memory, you should avoid caching more records
than is necessary. Even if a forward-only Recordset object supports
backward moves in this way, calling the ADO
Recordset Object MoveFirst, MoveLast, MoveNext, MovePrevious Methods
method
on any forward-only Recordset object still generates an error.

Move Method Example

This VBScript example uses the Move method
to position the record pointer based on user input. Try entering a letter or
non-integer to see the error-handling work.

<!-- #Include file="ADOVBS.INC" -->
<% Language = VBScript %>
<HTML><HEAD>
<TITLE>ADO 1.5 Move Methods</TITLE></HEAD>
<BODY>
<FONT FACE="MS SANS SERIF" SIZE=2>
<Center>
<H3>ADO Move Methods</H3>
<%
'Create and Open Connection Object
Set OBJdbConnection = Server.CreateObject("ADODB.Connection")
OBJdbConnection.Open "AdvWorks"
'Create and Open Recordset Object
Set RsCustomerList = Server.CreateObject("ADODB.Recordset")
RsCustomerList.ActiveConnection = OBJdbConnection
RsCustomerList.CursorType = adOpenKeyset
RsCustomerList.LockType = adLockOptimistic
RsCustomerList.Source = "Customers"
RsCustomerList.Open
'Check number of user moves this session
'Increment by amount in Form
Session("Clicks") = Session("Clicks") + Request.Form("MoveAmount")
Clicks = Session("Clicks")
'Move to last known recordset position plus amount passed by Form Post method
RsCustomerList.Move CInt(Clicks)
'Error Handling
If RsCustomerList.EOF Then
Session("Clicks") = RsCustomerList.RecordCount
Response.Write "This is the Last Record"
RsCustomerList.MoveLast
Else If RsCustomerList.BOF Then
Session("Clicks") = 1
RsCustomerList.MoveFirst
Response.Write "This is the First Record"
End If
End If
%>
<H3>Current Record Number is <BR>
<% If Session("Clicks") = 0 Then
Session("Clicks") = 1
End If
Response.Write(Session("Clicks") )%> of <%=RsCustomerList.RecordCount%></H3>
<HR>
<Center><TABLE COLSPAN=8 CELLPADDING=5 BORDER=0>
<!-- BEGIN column header row for Customer Table-->
<TR>
<TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Company Name</FONT>
</TD>
<TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Contact Name</FONT>
</TD>
<TD ALIGN=CENTER WIDTH=150 BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Phone Number</FONT>
</TD>
<TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>City</FONT>
</TD>
<TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>State/Province</FONT>
</TD>
</TR>
<!--Display ADO Data from Customer Table-->
<TR>
<TD BGCOLOR="f7efde" ALIGN=CENTER>
<FONT STYLE="ARIAL NARROW" SIZE=1>
<%= RSCustomerList("CompanyName")%>
</FONT></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER>
<FONT STYLE="ARIAL NARROW" SIZE=1>
<%= RScustomerList("ContactLastName") & ", " %>
<%= RScustomerList("ContactFirstName") %>
</FONT></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER>
<FONT STYLE="ARIAL NARROW" SIZE=1>
<%= RScustomerList("PhoneNumber")%>
</FONT></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER>
<FONT STYLE="ARIAL NARROW" SIZE=1>
<%= RScustomerList("City")%>
</FONT></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER>
<FONT STYLE="ARIAL NARROW" SIZE=1>
<%= RScustomerList("StateOrProvince")%>
</FONT></TD>
</TR> </Table></FONT>
<HR>
<Input Type = Button Name = cmdDown Value = "< ">
<Input Type = Button Name = cmdUp Value = " >">
<H5>Click Direction Arrows for Previous or Next Record
<BR> Click Move Amount to use Move Method
Enter Number of Records to Move + or - </H5>
<Table>
<Form Method = Post Action="Move.asp" Name=Form>
<TR><TD><Input Type="Button" Name = Move Value="Move Amount "></TD><TD></TD><TD>
<Input Type="Text" Size="4" Name="MoveAmount" Value = 0></TD><TR>
</Form></Table></Center>
</BODY>
<Script Language = "VBScript">
Sub Move_OnClick
' Make sure move value entered is an integer
If IsNumeric(Document.Form.MoveAmount.Value)Then
Document.Form.MoveAmount.Value = CInt(Document.Form.MoveAmount.Value)
Document.Form.Submit
Else
MsgBox "You Must Enter a Number", ,"ADO-ASP Example"
Document.Form.MoveAmount.Value = 0
End If
End Sub
Sub cmdDown_OnClick
Document.Form.MoveAmount.Value = -1
Document.Form.Submit
End Sub
Sub cmdUp_OnClick
Document.Form.MoveAmount.Value = 1
Document.Form.Submit
End Sub
</Script>
</HTML>
ADO
Recordset Object MoveFirst, MoveLast, MoveNext, MovePrevious Methods

These methods move to the first, last,
next, or previous record in a specified Recordset object and make that
record the current record.

MoveFirst, MoveLast, MoveNext, MovePrevious Methods
Syntax
recordset.{MoveFirst | MoveLast | MoveNext | MovePrevious}
MoveFirst, MoveLast, MoveNext, MovePrevious Methods
Remarks

Use the MoveFirst method to move
the current record position to the first record in the recordset.

Use the MoveLast method to move the
current record position to the last record in the recordset. The Recordset
object must support bookmarks or backward cursor movement; otherwise, the
method call will generate an error.

Use the MoveNext method to move the
current record position one record forward (toward the bottom of the recordset).
If the last record is the current record and you call the MoveNext
method, ADO

sets the current record to the position after the last record in the recordset
(EOF is True). An attempt to move forward when the ADO
Recordset Object BOF, EOF Properties
property is already True
generates an error.

Use the MovePrevious method to move
the current record position one record backward (toward the top of the
recordset). The Recordset object must support bookmarks or backward
cursor movement; otherwise, the method call will generate an error. If the
first record is the current record and you call the MovePrevious method,

ADO sets the
current record to the position before the first record in the recordset (BOF
is True). An attempt to move backward when the ADO
Recordset Object BOF, EOF Properties
property is already True
generates an error. If the Recordset object does not support either
bookmarks or backward cursor movement, the MovePrevious method will
generate an error.

If the recordset is forward-only and you
want to support both forward and backward scrolling, you can use the ADO
Recordset Object CacheSize Property
to create a record cache that will
support backward cursor movement through the ADO
Recordset Object Move Method
. Because cached records are loaded into
memory, you should avoid caching more records than is necessary. You can call
the MoveFirst method in a forward-only Recordset object; doing so
may cause the provider to re-execute the command that generated the Recordset
object.

MoveFirst, MoveLast, MoveNext, MovePrevious Methods
Example

This VBScript example uses the MoveFirst,
MoveLast, MoveNext, and MovePrevious methods to move the
record pointer of a recordset based on the supplied command. The MoveAny

function is required for this procedure to run. Try moving beyond the upper or
lower limits of the recordset to see error-handling work.

<!-- #Include file="ADOVBS.INC" -->
<% Language = VBScript %>
<HTML><HEAD>
<TITLE>ADO 1.5 MoveNext MovePrevious MoveLast MoveFirst Methods</TITLE></HEAD>
<BODY>
<FONT FACE="MS SANS SERIF" SIZE=2>
<Center>
<H3>ADO Methods<BR>MoveNext MovePrevious MoveLast MoveFirst</H3>
<!-- Create Connection and Recordset Objects on Server -->
<%
'Create and Open Connection Object
Set OBJdbConnection = Server.CreateObject("ADODB.Connection")
OBJdbConnection.Open "AdvWorks"
'Create and Open Recordset Object
Set RsCustomerList = Server.CreateObject("ADODB.Recordset")
RsCustomerList.ActiveConnection = OBJdbConnection
RsCustomerList.CursorType = adOpenKeyset
RsCustomerList.LockType = adLockOptimistic
RsCustomerList.Source = "Customers"
RsCustomerList.Open
' Check Request.Form collection to see if any moves are recorded
If Not IsEmpty(Request.Form("MoveAmount")) Then
'Keep track of the number and direction of moves this session
Session("Moves") = Session("Moves") + Request.Form("MoveAmount")
Clicks = Session("Moves")
'Move to last known position
RsCustomerList.Move CInt(Clicks)
'Check if move is + or - and do error checking
If CInt(Request.Form("MoveAmount")) = 1 Then
If RsCustomerList.EOF Then
Session("Moves") = RsCustomerList.RecordCount
RsCustomerList.MoveLast
End If
RsCustomerList.MoveNext
End If
If Request.Form("MoveAmount") < 1 Then
RsCustomerList.MovePrevious
End If
'Check if First Record or Last Record Command Buttons Clicked
If Request.Form("MoveLast") = 3 Then
RsCustomerList.MoveLast
Session("Moves") = RsCustomerList.RecordCount
End If
If Request.Form("MoveFirst") = 2 Then
RsCustomerList.MoveFirst
Session("Moves") = 1
End If
End If
' Do Error checking for combination of Move Button clicks
If RsCustomerList.EOF Then
Session("Moves") = RsCustomerList.RecordCount
RsCustomerList.MoveLast
Response.Write "This is the Last Record"
End If
If RsCustomerList.BOF Then
Session("Moves") = 1
RsCustomerList.MoveFirst
Response.Write "This is the First Record"
End If
%>
<H3>Current Record Number is <BR>
<!-- Display Current Record Number and Recordset Size -->
<% If IsEmpty(Session("Moves")) Then
Session("Moves") = 1
End If
%>
<%Response.Write(Session("Moves") )%> of <%=RsCustomerList.RecordCount%></H3>
<HR>
<Center><TABLE COLSPAN=8 CELLPADDING=5 BORDER=0>
<!-- BEGIN column header row for Customer Table-->
<TR><TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Company Name</FONT>
</TD>
<TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Contact Name</FONT>
</TD>
<TD ALIGN=CENTER WIDTH=150 BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Phone Number</FONT>
</TD>
<TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>City</FONT>
</TD>
<TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>State/Province</FONT>
</TD></TR>
<!--Display ADO Data from Customer Table-->
<TR>
<TD BGCOLOR="f7efde" ALIGN=CENTER>
<FONT STYLE="ARIAL NARROW" SIZE=1>
<%= RSCustomerList("CompanyName")%>
</FONT></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER>
<FONT STYLE="ARIAL NARROW" SIZE=1>
<%= RScustomerList("ContactLastName") & ", " %>
<%= RScustomerList("ContactFirstName") %>
</FONT></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER>
<FONT STYLE="ARIAL NARROW" SIZE=1>
<%= RScustomerList("PhoneNumber")%>
</FONT></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER>
<FONT STYLE="ARIAL NARROW" SIZE=1>
<%= RScustomerList("City")%>
</FONT></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER>
<FONT STYLE="ARIAL NARROW" SIZE=1>
<%= RScustomerList("StateOrProvince")%>
</FONT></TD>
</TR> </Table></FONT>
<HR>
<Input Type = Button Name = cmdDown Value = "< ">
<Input Type = Button Name = cmdUp Value = " >">
<BR>
<Input Type = Button Name = cmdFirst Value = "First Record">
<Input Type = Button Name = cmdLast Value = "Last Record">
<H5>Click Direction Arrows to Use MovePrevious or MoveNext
<BR> </H5>
<!-- Use Hidden Form Fields to send values to Server -->
<Form Method = Post Action="MoveOne.asp" Name=Form>
<Input Type="Hidden" Size="4" Name="MoveAmount" Value = 0>
<Input Type="Hidden" Size="4" Name="MoveLast" Value = 0>
<Input Type="Hidden" Size="4" Name="MoveFirst" Value = 0>
</Form></BODY>
<Script Language = "VBScript">
Sub cmdDown_OnClick
'Set Values in Form Input Boxes and Submit Form
Document.Form.MoveAmount.Value = -1
Document.Form.Submit
End Sub
Sub cmdUp_OnClick
Document.Form.MoveAmount.Value = 1
Document.Form.Submit
End Sub
Sub cmdFirst_OnClick
Document.Form.MoveFirst.Value = 2
Document.Form.Submit
End Sub
Sub cmdLast_OnClick
Document.Form.MoveLast.Value = 3
Document.Form.Submit
End Sub
</Script></HTML>
ADO
Recordset Object NextRecordset Method

Clears the current Recordset object
and returns the next recordset by advancing through a series of commands. This
method is not currently supported on UNIX
.

NextRecordset Method Syntax
Set recordset2 = recordset1.NextRecordset( RecordsAffected )
NextRecordset Method Parameters

recordset2

Recordset containing results of command.

RecordsAffected

An optional Long variable to which the
provider returns the number of records that the current operation affected.

NextRecordset Method Return Values

Returns a Recordset object. In the
syntax model, recordset1 and recordset2 can be the same Recordset
object, or you can use separate objects.

NextRecordset Method Remarks

Use the NextRecordset method to
return the results of the next command in a compound command statement or of a
stored procedure that returns multiple results. If you open a Recordset
object based on a compound command statement (for example, “SELECT * FROM
table1;SELECT * FROM table2”) using the ADO
Command Object Execute Method
on an ADO Command
Object
or the ADO
Recordset Object Open Method
on a recordset, ADO executes only the first
command and returns the results to recordset. To access the results of
subsequent commands in the statement, call the NextRecordset method.

As long as there are additional results,
the NextRecordset method will continue to return Recordset
objects. If a row-returning command returns no records, the returned Recordset
object will be empty; test for this case by verifying that the ADO
Recordset Object BOF, EOF Properties
are both True. If a non
row-returning command executes successfully, the returned Recordset

object will be closed, which you can verify by testing the ADO
Recordset Object State Property
on the recordset. When there are no more
results, recordset will be set to Nothing.

If an edit is in progress while in
immediate update mode, calling the NextRecordset method generates an
error; call the ADO
Recordset Object Update Method
or the ADO
Recordset Object CancelUpdate Method
first.

If you need to pass parameters for more
than one command in the compound statement by filling the ADO Parameters
Collection
or by passing an array with the original Open or Execute
call, the parameters must be in the same order in the collection or array as
their respective commands in the command series. You must finish reading all
the results before reading output parameter values.

When you call the NextRecordset
method, ADO

executes only the next command in the statement. If you explicitly close the Recordset
object before stepping through the entire command statement, ADO never executes the remaining commands.

The NextRecordset method is not
available on a client-side (ADOR) Recordset object.

NextRecordset Method Example

This Visual Basic example uses the NextRecordset
method to view the data in a recordset that uses a compound command statement
made up of three separate SELECT statements.

Public Sub NextRecordsetX()
Dim rstCompound As ADODB.Recordset
Dim strCnn As String
Dim intCount As Integer
` Open compound recordset.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set rstCompound = New ADODB.Recordset
rstCompound.Open "SELECT * FROM authors; " & _
"SELECT * FROM stores; " & _
"SELECT * FROM jobs", strCnn, , , adCmdText
` Display results from each SELECT statement.
intCount = 1
Do Until rstCompound Is Nothing
Debug.Print "Contents of recordset #" & intCount
Do While Not rstCompound.EOF
Debug.Print , rstCompound.Fields(0), _
rstCompound.Fields(1)
rstCompound.MoveNext
Loop
Set rstCompound = rstCompound.NextRecordset
intCount = intCount + 1
Loop
End Sub
ADO
Recordset Object Open Method

Opens a cursor.

Open Method Syntax
recordset.Open Source, ActiveConnection, CursorType, LockType, Options
Open Method Parameters

Source

An optional Variant that evaluates to a
valid Command object variable name, an SQL statement, a table name, or a
stored procedure call.

ActiveConnection

An optional Variant that evaluates to a
valid Connection object variable name, or a String containing ConnectionString

parameters.

CursorType

An optional CursorTypeEnum value
that determines the type of cursor that the provider should use when opening
the recordset. Can be one of the following constants (See the ADO
Recordset Object CursorType Property
for definitions of these settings.):

Constant

Description

adOpenForwardOnly

Default. Opens a forward-only
cursor.

adOpenKeyset

Opens a keyset cursor.

adOpenDynamic

Opens a dynamic cursor.

adOpenStatic

Opens a static cursor.

LockType

An optional LockTypeEnum value that
determines what type of locking (concurrency) the provider should use when
opening the recordset. Can be one of the following constants (See the LockType

property for more information.):

Constant

Description

adLockReadOnly

Default. Read-only; you cannot alter
the data.

adLocPessimistic

Pessimistic locking, record by
record. The provider does what is necessary to ensure successful editing of
the records, usually by locking records at the data source immediately upon
editing.

adLockOptimistic

Optimistic locking, record by
record. The provider uses optimistic locking, locking records only when you
call the Update method.

adLockBatchOptimistic

Optimistic batch updates. Required
for batch update mode as opposed to immediate update mode.

Options

An optional Long value that
indicates how the provider should evaluate the Source argument if it
represents something other than a Command object. Can be one of the
following constants (See the CommandType property for a more detailed
explanation of these constants.):

Constant

Description

adCmdText

The provider should evaluate Source

as a textual definition of a command.

adCmdTable

The provider should evaluate Source
as a table name.

adCmdStoredProc

The provider should evaluate Source
as a stored procedure.

adCmdUnknown

The type of command in the Source

argument is not known.

See the ADO
Command Object CommandType Property
for a more detailed explanation of the
four constants in this list.

Open Method Remarks

Using the Open method on a Recordset
object opens a cursor that represents records from a base table or the results
of a query.

Use the optional Source argument
to specify a data source using one of the following: an ADO Command
Object
variable, an SQL statement, a stored procedure, or a table name.

The ActiveConnection argument
corresponds to the ActiveConnection property and specifies in which
connection to open the Recordset object. If you pass a connection
definition for this argument, ADO
opens a new connection using the specified parameters. You can change the value
of this property after opening the recordset to send updates to another
provider. Or, you can set this property to Nothing (in Microsoft Visual
Basic) to disconnect the recordset from any provider.

For the other arguments that correspond
directly to properties of a Recordset object (Source, CursorType,
and LockType), the relationship of the arguments to the properties is
as follows:

·
The property is read/write before the Recordset

object is opened.

·
The property settings are used unless you pass
the corresponding arguments when executing the Open method. If you pass
an argument, it overrides the corresponding property setting, and the property
setting is updated with the argument value.

·
After you open the Recordset object,
these properties become read-only.

Note iconNote

For Recordset objects whose ADO
Recordset Object Source Property
is set to a valid Command object,
the ActiveConnection property is read-only, even if the Recordset

object isn’t open.

If you pass a Command object in the
Source argument and also pass an ActiveConnection argument,
an error occurs. The ActiveConnection property of the Command

object must already be set to a valid ADO Connection
Object
or connection string.

If you pass something other than a Command
object in the Source argument, you can use the Options
argument to optimize evaluation of the Source argument. If the Options

argument is not defined, you may experience diminished performance because ADO must make calls to
the provider to determine if the argument is an SQL statement, a stored
procedure, or a table name. If you know what Source type you’re using,
setting the Options argument instructs ADO to jump directly to the relevant code. If
the Options argument does not match the Source type, an error
occurs.

If the data source returns no records, the
provider sets both the ADO
Recordset Object BOF, EOF Properties
to True, and the current
record position is undefined. You can still add new data to this empty Recordset
object if the cursor type allows it.

When you have concluded your operations
over an open Recordset object, use the ADO
Recordset Object Close Method
to free any associated system resources.
Closing an object does not remove it from memory; you may change its property
settings and use the Open method to open it again later. To completely eliminate
an object from memory, set the object variable to Nothing.

Open Method Examples

See the ADO
Recordset Object Close Method
.

ADO
Recordset Object Requery Method

Updates the data in a Recordset
object by re-executing the query on which the object is based.

Requery Method Syntax
recordset.Requery
Requery Method Remarks

Use the Requery method to refresh
the entire contents of a Recordset object from the data source by
reissuing the original command and retrieving the data a second time. Calling
this method is equivalent to calling the ADO
Recordset Object Close Method
and ADO
Recordset Object Open Method
methods in succession. If you are
editing the current record or adding a new record, an error occurs.

While the Recordset object is open,
the properties that define the nature of the cursor (ADO
Recordset Object CursorType Property
, ADO
Recordset Object LockType Property
, ADO
Recordset Object MaxRecords Property
, and so forth) are read-only. Thus,
the Requery method can only refresh the current cursor. To change any of
the cursor properties and view the results, you must use the Close

method so that the properties become read/write again. You can then change the
property settings and call the Open method to reopen the cursor.

Requery Method Example

See the command ADO
Command Object Execute Method
.

ADO
Recordset Object Resync Method

Refreshes the data in the current Recordset
object from the underlying database.

Resync Method Syntax
recordset.Resync AffectRecords
Resync Method Parameters

AffectRecords

An optional AffectEnum constant
that determines how many records the Resync method will affect. Can be
one of the following constants:

Constant

Description

adAffectCurrent

Refresh only the current record.

adAffectGroup

Refresh the records that satisfy the
current Filter property setting. You must set the Filter

property to one of the valid predefined constants in order to use this
option. The Filter property is not currently supported on
UNIX.

adAffectAll

Default. Refresh all the records in
the Recordset object, including any hidden by the current Filter
property setting.

Resync Method Remarks

Use the Resync method to
re-synchronize records in the current recordset with the underlying database.
This is useful if you are using either a static or forward-only cursor but you
want to see any changes in the underlying database. Calling the Resync
method cancels any pending batch updates.

Unlike the ADO
Recordset Object Requery Method
, the Resync method does not
re-execute the Recordset object’s underlying command; new records in the
underlying database will not be visible.

If the attempt to resynchronize fails
because of a conflict with the underlying data (for example, a record has been
deleted by another user), the provider returns warnings to the ADO Errors
Collection
, but does not halt program execution. A run-time error occurs
only if there are conflicts on all the requested records. Use the ADO
Recordset Object Filter Property
(adFilterAffectedRecords) and the ADO
Recordset Object Status Property
to locate records with conflicts.

Resync Method Examples

This Visual Basic example demonstrates
using the Resync method to refresh data in a static recordset.

Public Sub ResyncX()
Dim strCnn As String
Dim rstTitles As ADODB.Recordset
' Open connections.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
' Open recordset for titles table.
Set rstTitles = New ADODB.Recordset
rstTitles.CursorType = adOpenStatic
rstTitles.LockType = adLockBatchOptimistic
rstTitles.Open "titles", strCnn, , , adCmdTable
' Change the type of the first title in the recordset.
rstTitles!Type = "database"
' Display the results of the change.
MsgBox "Before resync: " & vbCr & vbCr & _
"Title - " & rstTitles!Title & vbCr & _
"Type - " & rstTitles!Type
' Resync with database and redisplay results.
rstTitles.Resync
MsgBox "After resync: " & vbCr & vbCr & _
"Title - " & rstTitles!Title & vbCr & _
"Type - " & rstTitles!Type
rstTitles.CancelBatch
rstTitles.Close
End Sub
ADO
Recordset Object Supports Method

Determines whether a specified Recordset
object supports a particular type of functionality.

Supports Method Syntax
boolean = recordset.Supports( CursorOptions )
Supports Method Parameters

CursorOptions

A Long expression that consists of
one or more of the following CursorOptionEnum values:

Value

Description

adAddNew

The AddNew method adds new
records.

adApproxPosition

You can read and set the AbsolutePosition

and AbsolutePage properties.

adBookmark

The Bookmark property
accesses specific records.

adDelete

The Delete method deletes
records.

adHoldRecords

You can retrieve more records or
change the next retrieve position without committing all pending changes.

adMovePrevious

The MoveFirst, MovePrevious,
Move, and GetRows methods move the current position backward
without requiring bookmarks.

adResync

The Resync method modifies
existing data.

adUpdate

The Update method modifies
existing data.

adUpdateBatch

The UpdateBatch and CancelBatch

methods transmit changes to the provider in groups.

Supports Method Remarks

Use the Supports method to
determine what types of functionality a Recordset object supports. If
the Recordset object supports the features whose corresponding constants
are in CursorOptions, the Supports method returns True.
Otherwise, it returns False.

Note iconNote

Although the Supports method may return True

for a given functionality, it does not guarantee that the provider can make
the feature available under all circumstances. The Supports method
simply returns whether or not the provider can support the specified
functionality assuming certain conditions are met. For example, the Supports
method may indicate that a Recordset object supports updates even
though the cursor is based on a multi-table join, some columns of which are
not updatable.

Supports Method Examples

This Visual Basic example uses the Supports
method to display the options supported by a recordset opened with different
cursor types. The DisplaySupport function is required for this procedure
to run.

Public Sub SupportsX()
Dim aintCursorType(4) As Integer
Dim rstTitles As ADODB.Recordset
Dim strCnn As String
Dim intIndex As Integer
` Open connections.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
` Fill array with CursorType constants.
aintCursorType(0) = adOpenForwardOnly
aintCursorType(1) = adOpenKeyset
aintCursorType(2) = adOpenDynamic
aintCursorType(3) = adOpenStatic
` Open recordset using each CursorType and
` optimitic locking. Then call the DisplaySupport
` procedure to display the supported options.
For intIndex = 0 To 3
Set rstTitles = New ADODB.Recordset
rstTitles.CursorType = aintCursorType(intIndex)
rstTitles.LockType = adLockOptimistic
rstTitles.Open "titles", strCnn, , , adCmdTable
Select Case aintCursorType(intIndex)
Case adOpenForwardOnly
Debug.Print "ForwardOnly cursor supports:"
Case adOpenKeyset
Debug.Print "Keyset cursor supports:"
Case adOpenDynamic
Debug.Print "Dynamic cursor supports:"
Case adOpenStatic
Debug.Print "Static cursor supports:"
End Select
DisplaySupport rstTitles
rstTitles.Close
Next intIndex
End Sub
Public Sub DisplaySupport(rstTemp As ADODB.Recordset)
Dim alngConstants(9) As Long
Dim booSupports As Boolean
Dim intIndex As Integer
' Fill array with cursor option constants.
alngConstants(0) = adAddNew
alngConstants(1) = adApproxPosition
alngConstants(2) = adBookmark
alngConstants(3) = adDelete
alngConstants(4) = adHoldRecords
alngConstants(5) = adMovePrevious
alngConstants(6) = adResync
alngConstants(7) = adUpdate
alngConstants(8) = adUpdateBatch
For intIndex = 0 To 8
booSupports = _
rstTemp.Supports(alngConstants(intIndex))
If booSupports Then
Select Case alngConstants(intIndex)
Case adAddNew
Debug.Print " AddNew"
Case adApproxPosition
Debug.Print " AbsolutePosition and AbsolutePage"
Case adBookmark
Debug.Print " Bookmark"
Case adDelete
Debug.Print " Delete"
Case adHoldRecords
Debug.Print " holding records"
Case adMovePrevious
Debug.Print " MovePrevious and Move"
Case adResync
Debug.Print " resyncing data"
Case adUpdate
Debug.Print " Update"
Case adUpdateBatch
Debug.Print " batch updating"
End Select
End If
Next intIndex
End Sub
ADO
Recordset Object Update Method

Saves any changes you make to the current
record of a Recordset object.

Note iconNote

This method is not available for some databases and ODBC
drivers.

Update Method Syntax
recordset.Update Fields, Values
Update Method Parameters

Fields

An optional Variant representing a single
name or a Variant array representing names or ordinal positions of the field or
fields you wish to modify.

Values

An optional Variant representing a single
value or a Variant array representing values for the field or fields in the new
record.

Update Method Remarks

Use the Update method to save any
changes you make to the current record of a Recordset object since
calling the ADO
Recordset Object AddNew Method
or since changing any field values in an
existing record. The Recordset object must support updates.

To set field values, do one of the
following:

·
Assign values to a ADO Field Object
object’s ADO
Field Object Value Property
and call the ADO
Recordset Object Update Method
.

·
Pass a field name and a value as arguments with
the Update call.

·
Pass an array of field names and an array of
values with the Update call.

When you use arrays of fields and values,
there must be an equal number of elements in both arrays. Also, the order of
field names must match the order of field values. If the number and order of
fields and values do not match, an error occurs.

If the Recordset object supports
batch updating, then you can cache multiple changes to one or more records
locally until you call the ADO
Recordset Object UpdateBatch Method
. If you are editing the current record
or adding a new record when you call the UpdateBatch method, ADO will automatically
call the Update method to save any pending changes to the current record
before transmitting the batched changes to the provider. Batch updating is
not currently supported on UNIX.

If you move from the record you are adding
or editing before calling the Update method, ADO will automatically call Update to
save the changes. You must call the ADO
Recordset Object CancelUpdate Method
if you want to cancel any changes made
to the current record or to discard a newly added record.

The current record remains current after
you call the Update method.

Update Method Examples

The following Visual Basic examples show
how to use the Update method.

The first example demonstrates using the Update
method in conjunction with CancelUpdate method.

Public Sub UpdateX()
Dim rstEmployees As ADODB.Recordset
Dim strOldFirst As String
Dim strOldLast As String
Dim strMessage As String
` Open recordset with names from Employee table.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set rstEmployees = New ADODB.Recordset
rstEmployees.CursorType = adOpenKeyset
rstEmployees.LockType = adLockOptimistic
rstEmployees.Open "SELECT fname, lname " & _
"FROM Employee ORDER BY lname", strCnn, , , adCmdText
` Store original data.
strOldFirst = rstEmployees!fname
strOldLast = rstEmployees!lname
` Change data in edit buffer.
rstEmployees!fname = "Linda"
rstEmployees!lname = "Kobara"
` Show contents of buffer and get user input.
strMessage = "Edit in progress:" & vbCr & _
" Original data = " & strOldFirst & " " & _
strOldLast & vbCr & " Data in buffer = " & _
rstEmployees!fname & " " & rstEmployees!lname & vbCr & vbCr & _
"Use Update to replace the original data with " & _
"the buffered data in the Recordset?"
If MsgBox(strMessage, vbYesNo) = vbYes Then
rstEmployees.Update
Else
rstEmployees.CancelUpdate
End If
` Show the resulting data.
MsgBox "Data in recordset = " & rstEmployees!fname & " " & _
rstEmployees!lname
` Restore original data because this is a demonstration.
If Not (strOldFirst = rstEmployees!fname And _
strOldLast = rstEmployees!lname) Then
rstEmployees!fname = strOldFirst
rstEmployees!lname = strOldLast
rstEmployees.Update
End If
rstEmployees.Close
End Sub

The following example demonstrates using
the Update method in conjunction with the AddNew method:

Public Sub UpdateX2()
Dim cnn1 As ADODB.Connection
Dim rstEmployees As ADODB.Recordset
Dim strEmpID As String
Dim strOldFirst As String
Dim strOldLast As String
Dim strMessage As String
' Open a connection.
Set cnn1 = New ADODB.Connection
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
cnn1.Open strCnn
' Open recordset with data from Employee table.
Set rstEmployees = New ADODB.Recordset
rstEmployees.CursorType = adOpenKeyset
rstEmployees.LockType = adLockOptimistic
rstEmployees.Open "employee", cnn1, , , adCmdTable
rstEmployees.AddNew
strEmpID = "B-S55555M"
rstEmployees!emp_id = strEmpID
rstEmployees!fname = "Bill"
rstEmployees!lname = "Sornsin"
' Show contents of buffer and get user input.
strMessage = "AddNew in progress:" & vbCr & _
"Data in buffer = " & rstEmployees!emp_id & ", " & _
rstEmployees!fname & " " & rstEmployees!lname & vbCr & vbCr & _
"Use Update to save buffer to recordset?"
If MsgBox(strMessage, vbYesNoCancel) = vbYes Then
rstEmployees.Update
` Go to the new record and show the resulting data.
MsgBox "Data in recordset = " & rstEmployees!emp_id & ", " & _
rstEmployees!fname & " " & rstEmployees!lname
Else
rstEmployees.CancelUpdate
MsgBox "No new record added."
End If
' Delete new data because this is a demonstration.
cnn1.Execute "DELETE FROM employee WHERE emp_id = '" & strEmpID & "'"
rstEmployees.Close
End Sub
ADO
Recordset Object UpdateBatch Method

Writes all pending batch updates to disk. This
method is not currently supported on UNIX.

UpdateBatch Method Syntax
recordset.UpdateBatch AffectRecords
UpdateBatch Method Parameters

AffectRecords

An optional AffectEnum value that
determines how many records the UpdateBatch method will affect. Can be
one of the following constants:

Constant

Description

AdAffectCurrent

Write pending changes only for the
current record.

AdAffectGroup

Write pending changes for the
records that satisfy the current Filter property setting. You must set
the Filter property to one of the valid predefined constants in order
to use this option.

adAffectAll

Default. Write pending changes for
all the records in the Recordset object, including any hidden by the
current Filter property setting.

UpdateBatch Method Remarks


Use the UpdateBatch method when
modifying a Recordset object in batch update mode to transmit all
changes made in a Recordset object to the underlying database.

If the Recordset object supports
batch updating, then you can cache multiple changes to one or more records
locally until you call the UpdateBatch method. If you are editing the
current record or adding a new record when you call the UpdateBatch

method, ADO
will automatically call the ADO
Recordset Object Update Method
to save any pending changes to the current
record before transmitting the batched changes to the provider.

Note iconNote

You should use batch updating only with either a keyset or
static cursor.

If the attempt to transmit changes fails
because of a conflict with the underlying data (for example, a record has
already been deleted by another user), the provider returns warnings to the ADO Errors
Collection
but does not halt program execution. A run-time error occurs
only if there are conflicts on all the requested records. Use the ADO
Recordset Object Filter Property
(adFilterAffectedRecords) and the ADO
Recordset Object Status Property
to locate records with conflicts.

To cancel all pending batch updates, use
the ADO
Recordset Object CancelBatch Method
.

UpdateBatch Method Example

See the ADO
Recordset Object CancelBatch Method
example.

ADORecordset Object Properties


Property

Description

AbsolutePage

The page in which the current record
resides.

AbsolutePosition

The ordinal position of a Recordset
object’s current position.

ActiveConnection

The Connection object to
which the Recordset object currently belongs.

BOF, EOF

If BOF is True, the current
record position is before the first record in a Recordset object. If
EOF is True, the record position is after the last record in a Recordset

object.

Bookmark

A value that uniquely identifies the
current record in a Recordset object. Setting the Bookmark
property to a valid bookmark changes the current record.

CacheSize

The number of records from a Recordset
object that are cached locally in memory. This property is not currently
supported on UNIX.

CursorLocation

The location of the cursor engine.

CursorType

The type of cursor used in a Recordset

object.

EditMode

The editing status of the current record.

Filter

A filter for data in a Recordset
object.

LockType

The type of locks placed on records
during editing.

MarshalOptions

Which records are to be marshaled
back to the server.

MaxRecords

The maximum number of records to
return to a Recordset object from a query.

PageCount

The number of pages of data the Recordset

object contains.

PageSize

The number of records that make up
one page in the Recordset object.

RecordCount

The current number of records in a Recordset
object.

Source

The source for the data in a Recordset
object.

State

Describes the current state of the Recordset

object.

Status

The status of the current record
with respect to batch updates or other bulk operations.

In this section:

ADO Recordset
Object AbsolutePage Property

ADO Recordset
Object AbsolutePosition Property

ADO Recordset
Object ActiveConnection Property

ADO Recordset
Object BOF, EOF Properties

ADO Recordset
Object Bookmark Property

ADO Recordset
Object CacheSize Property

ADO Recordset
Object CursorLocation Property

ADO Recordset
Object CursorType Property

ADO Recordset
Object EditMode Property

ADO Recordset
Object Filter Property

ADO Recordset
Object LockType Property

ADO Recordset
Object MarshalOptions Property

ADO Recordset
Object MaxRecords Property

ADO Recordset
Object PageCount Property

ADO Recordset
Object PageSize Property

ADO Recordset
Object State Property

ADO Recordset
Object Status Property

ADO Recordset
Object Source Property

ADO Recordset
Object RecordCount Property

ADO
Recordset Object AbsolutePage Property

Specifies in which page the current record
resides.

AbsolutePage Property Return Values

Sets or returns a Long value from 1
to the number of pages in the Recordset object (PageCount), or
returns one of the following constants:

Constant

Description

adPosUnknown

The recordset is empty, the current
position is unknown, or the provider does not support the AbsolutePage
property

adPosBOF

The current record pointer is at BOF
(that is, the BOF property is True).

adPosEOF

The current record pointer is at EOF
(that is, the EOF property is True).

AbsolutePage Property Remarks

Use the AbsolutePage property to
identify the page number on which the current record is located. Use the ADO
Recordset Object PageSize Property
to logically divide the Recordset

object into a series of pages, each of which has the number of records equal to
PageSize (except for the last page, which may have fewer records). The
provider must support the appropriate functionality for this property to be
available.

Like the AbsolutePosition property,
AbsolutePage is 1-based and equals 1 when the current record is the
first record in the recordset. Set this property to move to the first record of
a particular page. Obtain the total number of pages from the ADO
Recordset Object PageCount Property
.

AbsolutePage Property Example

This Visual Basic example uses the AbsolutePage,
PageCount, and PageSize properties to display names and hire
dates from the Employees table five records at a time.

Public Sub AbsolutePageX()
Dim rstEmployees As ADODB.Recordset
Dim strCnn As String
Dim strMessage As String
Dim intPage As Integer
Dim intPageCount As Integer
Dim intRecord As Integer
` Open a recordset using a client cursor
` for the employee table.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set rstEmployees = New ADODB.Recordset
` Use client cursor to enable AbsolutePosition property.
rstEmployees.CursorLocation = adUseClient
rstEmployees.Open "employee", strCnn, , , adCmdTable
` Display names and hire dates, five records
` at a time.
rstEmployees.PageSize = 5
intPageCount = rstEmployees.PageCount
For intPage = 1 To intPageCount
rstEmployees.AbsolutePage = intPage
strMessage = ""
For intRecord = 1 To rstEmployees.PageSize
strMessage = strMessage & _
rstEmployees!fname & " " & _
rstEmployees!lname & " " & _
rstEmployees!hire_date & vbCr
rstEmployees.MoveNext
If rstEmployees.EOF Then Exit For
Next intRecord
MsgBox strMessage
Next intPage
rstEmployees.Close
End Sub
ADO
Recordset Object AbsolutePosition Property

Specifies the ordinal position of a Recordset
object’s current record.

AbsolutePosition Property Return Values

Sets or returns a Long value from 1
to the number of records in the Recordset object (RecordCount),
or returns one of the following constants:

Constant

Description

adPosUnknown

The recordset is empty, the current
position is unknown, or the provider does not support the AbsolutePosition
property.

adPosBOF

The current record pointer is at BOF
(that is, the BOF property is True).

adPosEOF

The current record pointer is at EOF
(that is, the EOF property is True).

AbsolutePosition Property Remarks

Use the AbsolutePosition property
to move to a record based on its ordinal position in the Recordset

object, or to determine the ordinal position of the current record. The
provider must support the appropriate functionality for this property to be
available.

Like the AbsolutePage property, AbsolutePosition
is 1-based and equals 1 when the current record is the first record in the
recordset. You can obtain the total number of records in the Recordset
object from the RecordCount property.

When you set the AbsolutePosition

property, even if it is to a record in the current cache, ADO reloads the cache with a new group of
records starting with the record you specified. The ADO
Recordset Object CacheSize Property
determines the size of this group.

Note iconNote

You should not use the AbsolutePosition property as
a surrogate record number. The position of a given record changes when you
delete a preceding record. There is also no assurance that a given record
will have the same AbsolutePosition if the Recordset object is
requeried or reopened. Bookmarks are still the recommended way of retaining
and returning to a given position, and are the only way of positioning across
all types of Recordset objects.

AbsolutePosition Property Example

This Visual Basic example demonstrates how
the AbsolutePosition property can track the progress of a loop that
enumerates all the records of a recordset. It uses the CursorLocation property
to enable the AbsolutePosition property by setting the cursor to a
client cursor.

Public Sub AbsolutePositionX()
Dim rstEmployees As ADODB.Recordset
Dim strCnn As String
Dim strMessage As String
' Open a recordset for the Employee table
' using a client cursor.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set rstEmployees = New ADODB.Recordset
' Use client cursor to enable AbsolutePosition property.
rstEmployees.CursorLocation = adUseClient
rstEmployees.Open "employee", strCnn, , , adCmdTable
' Enumerate Recordset.
Do While Not rstEmployees.EOF
' Display current record information.
strMessage = "Employee: " & rstEmployees!lName & vbCr & _
"(record " & rstEmployees.AbsolutePosition & _
" of " & rstEmployees.RecordCount & ")"
If MsgBox(strMessage, vbOKCancel) = vbCancel _
Then Exit Do
rstEmployees.MoveNext
Loop
rstEmployees.Close
End Sub
ADO
Recordset Object ActiveConnection Property

Specifies to which Connection
object the Recordset object currently belongs.

ActiveConnection Property Return Values (ADO Recordset Object)

Sets or returns a String containing
the definition for a connection or an ADO Connection
Object
. Default is a Null object reference.

ActiveConnection Property Remarks (ADO Recordset Object)

Use the ActiveConnection property
to determine the Connection object over which the specified Command
object will execute or the specified recordset will be opened.

For open Recordset objects or for Recordset
objects whose ADO
Recordset Object Source Property
is set to a valid ADO Command
Object
, the ActiveConnection property is read-only. Otherwise, it is
read/write.

You can set this property to a valid Connection
object or to a valid connection string. In this case, the provider creates a
new Connection object using this definition and opens the connection.
Additionally, the provider may set this property to the new Connection
object to give you a way to access the Connection object for extended
error information or to execute other commands.

If you use the ActiveConnection

argument of the ADO
Recordset Object Open Method
to open a Recordset object, the ActiveConnection
property will inherit the value of the argument.

If you set the Source property of
the Recordset object to a valid Command object variable, the ActiveConnection

property of the recordset inherits the setting of the Command object’s ActiveConnection
property.

ActiveConnection Property Example (ADO Recordset Object)

This Visual Basic example uses the ActiveConnection,

ADO
Command Object CommandText Property
, CommandTimeout, ADO
Command Object CommandType Property
, ADO
Parameter Object Size Property
, and ADO
Parameter Object Direction Property
properties to execute a stored
procedure:

Public Sub ActiveConnectionX()
Dim cnn1 As ADODB.Connection
Dim cmdByRoyalty As ADODB.Command
Dim prmByRoyalty As ADODB.Parameter
Dim rstByRoyalty As ADODB.Recordset
Dim rstAuthors As ADODB.Recordset
Dim intRoyalty As Integer
Dim strAuthorID As String
Dim strCnn As String
` Define a command object for a stored procedure.
Set cnn1 = New ADODB.Connection
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
cnn1.Open strCnn
Set cmdByRoyalty = New ADODB.Command
Set cmdByRoyalty.ActiveConnection = cnn1
cmdByRoyalty.CommandText = "byroyalty"
cmdByRoyalty.CommandType = adCmdStoredProc
cmdByRoyalty.CommandTimeout = 15
` Define the stored procedure's input parameter.
intRoyalty = Trim(InputBox( _
"Enter royalty:"))
Set prmByRoyalty = New ADODB.Parameter
prmByRoyalty.Type = adInteger
prmByRoyalty.Size = 3
prmByRoyalty.Direction = adParamInput
prmByRoyalty.Value = intRoyalty
cmdByRoyalty.Parameters.Append prmByRoyalty
` Create a recordset by executing the command.
Set rstByRoyalty = cmdByRoyalty.Execute()
` Open the Authors table to get author names for display.
Set rstAuthors = New ADODB.Recordset
rstAuthors.Open "authors", strCnn, , , adCmdTable
` Print current data in the recordset, adding
` author names from Authors table.
Debug.Print "Authors with " & intRoyalty & _
" percent royalty"
Do While Not rstByRoyalty.EOF
strAuthorID = rstByRoyalty!au_id
Debug.Print , rstByRoyalty!au_id & ", ";
rstAuthors.Filter = "au_id = '" & strAuthorID & "'"
Debug.Print rstAuthors!au_fname & " " & _
rstAuthors!au_lname
rstByRoyalty.MoveNext
Loop
rstByRoyalty.Close
rstAuthors.Close
cnn1.Close
End Sub
ADO
Recordset Object BOF, EOF Properties

BOF indicates that the current
record position is before the first record in a Recordset object.

EOF indicates that the current
record position is after the last record in a Recordset object.

BOF, EOF Properties Return Values

The BOF and EOF properties
return Boolean values.

BOF, EOF Properties Remarks

Use the BOF and EOF
properties to determine whether a Recordset object contains records or
whether you’ve gone beyond the limits of a Recordset object when you
move from record to record.

The BOF property returns True
(-1) if the current record position is before the first record and False
(0) if the current record position is on or after the first record.

The EOF property returns True

if the current record position is after the last record and False if the
current record position is on or before the last record.

If either the BOF or EOF
property is True, there is no current record.

If you open a Recordset object
containing no records, the BOF and EOF properties are set to True,
and the Recordset object’s RecordCount property setting is zero.
When you open a Recordset object that contains at least one record, the
first record is the current record and the BOF and EOF properties
are False.

If you delete the last remaining record in
the Recordset object, the BOF and EOF properties may
remain False until you attempt to reposition the current record.

This table shows which ADO
Recordset Object Move Method
methods are allowed with different
combinations of the BOF and EOF properties:

MoveFirst

MoveLast

Move
Previous Move < 0

Move
0

Move
Next Move > 0

BOF = True,

EOF = False

Allowed

Error

Error

Allowed

BOF=False

EOF=True

Allowed

Allowed

Error

Error

Both True

Error

Error

Error

Error

Both False

Allowed

Allowed

Allowed

Allowed

Allowing a Move method doesn’t
guarantee that the method will successfully locate a record; it only means that
calling the specified Move method won’t generate an error.

The following table shows what happens to
the BOF and EOF property settings when you call various Move

methods but are unable to successfully locate a record.

BOF

EOF

MoveFirst, MoveLast

Set to True

Set to True

Move 0

No change

No change

MovePrevious, Move < 0

Set to True

No change

MoveNext, Move > 0

No change

Set to True

BOF, EOF Properties Example

This Visual Basic example uses the BOF
and EOF properties to display a message if a user tries to move past the
first or last record of a recordset. It uses the ADO
Recordset Object Bookmark Property
to let the user flag a record in a
recordset and return to it later.

Public Sub BOFX()
Dim rstPublishers As ADODB.Recordset
Dim strCnn As String
Dim strMessage As String
Dim intCommand As Integer
Dim varBookmark As Variant
` Open recordset with data from Publishers table.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set rstPublishers = New ADODB.Recordset
rstPublishers.CursorType = adOpenStatic
` Use client cursor to enable AbsolutePosition property.
rstPublishers.CursorLocation = adUseClient
rstPublishers.Open "SELECT pub_id, pub_name FROM publishers " & _
"ORDER BY pub_name", strCnn, , , adCmdText
rstPublishers.MoveFirst
Do While True
` Display information about current record
` and get user input.
strMessage = "Publisher: " & rstPublishers!pub_name & _
vbCr & "(record " & rstPublishers.AbsolutePosition & _
" of " & rstPublishers.RecordCount & ")" & vbCr & vbCr & _
"Enter command:" & vbCr & _
"[1 - next / 2 - previous /" & vbCr & _
"3 - set bookmark / 4 - go to bookmark]"
intCommand = Val(InputBox(strMessage))
Select Case intCommand
` Move forward or backward, trapping for BOF
` or EOF.
Case 1
rstPublishers.MoveNext
If rstPublishers.EOF Then
MsgBox "Moving past the last record." & _
vbCr & "Try again."
rstPublishers.MoveLast
End If
Case 2
rstPublishers.MovePrevious
If rstPublishers.BOF Then
MsgBox "Moving past the first record." &
_vbCr & "Try again."
rstPublishers.MoveFirst
End If
` Store the bookmark of the current record.
Case 3
varBookmark = rstPublishers.Bookmark
` Go to the record indicated by the stored
` bookmark.
Case 4
If IsEmpty(varBookmark) Then
MsgBox "No Bookmark set!"
Else
rstPublishers.Bookmark = varBookmark
End If
Case Else
Exit Do
End Select
Loop
rstPublishers.Close
End Sub
ADO
Recordset Object Bookmark Property

Returns a bookmark that uniquely identifies
the current record in a Recordset object or sets the current record in a
Recordset object to the record identified by a valid bookmark.

Bookmark Property Return Values

Sets or returns a Variant expression that
evaluates to a valid bookmark.

Bookmark Property Remarks

Use the Bookmark property to save
the position of the current record and return to that record at any time.
Bookmarks are available only in Recordset objects that support bookmark
functionality.

When you open a Recordset object,
each of its records has a unique bookmark. To save the bookmark for the current
record, assign the value of the Bookmark property to a variable. To
quickly return to that record at any time after moving to a different record,
set the Recordset object’s Bookmark property to the value of that
variable.

The user may not be able to view the value
of the bookmark. Also, users should not expect bookmarks to be directly
comparable—two bookmarks that refer to the same record may have different
values.

If you use the ADO
Recordset Object Clone Method
to create a copy of a Recordset
object, the Bookmark property settings for the original and the
duplicate Recordset objects are identical and you can use them
interchangeably. However, you can’t use bookmarks from different Recordset
objects interchangeably, even if they were created from the same source or
command.

Bookmark Property Examples

See the ADO
Recordset Object BOF, EOF Properties
.

ADO
Recordset Object CacheSize Property

The number of records from a Recordset
object that are cached locally in memory. This property is not currently
supported on UNIX.

CacheSize Property Return Values

Sets or returns a Long value that
must be greater than 0. Default is 1.

CacheSize Property Remarks

Use the CacheSize property to
control how many records the provider keeps in its buffer and how many to
retrieve at one time into local memory. For example, if the CacheSize is
10, after first opening the Recordset object, the provider retrieves the
first 10 records into local memory. As you move through the Recordset
object, the provider returns the data from the local memory buffer. As soon as
you move past the last record in the cache, the provider retrieves the next 10
records from the data source into the cache.

The value of this property can be adjusted
during the life of the Recordset object, but changing this value only
affects the number of records in the cache after subsequent retrievals from the
data source. Changing the property value alone will not change the current
contents of the cache.

If there are fewer records to retrieve
than CacheSize specifies, the provider returns the remaining records; no
error occurs.

A CacheSize setting of zero is not
allowed and returns an error.

Records retrieved from the cache don’t
reflect concurrent changes that other users made to the source data. To force
an update of all the cached data, use the ADO
Recordset Object Resync Method
.

CacheSize Property Example

This Visual Basic example uses the CacheSize
property to show the difference in performance for an operation performed with
and without a 30-record cache.

Public Sub CacheSizeX()
Dim rstRoySched As ADODB.Recordset
Dim strCnn As String
Dim sngStart As Single
Dim sngEnd As Single
Dim sngNoCache As Single
Dim sngCache As Single
Dim intLoop As Integer
Dim strTemp As String
` Open the RoySched table.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set rstRoySched = New ADODB.Recordset
rstRoySched.Open "roysched", strCnn, , , adCmdTable
` Enumerate the Recordset object twice and record
` the elapsed time.
sngStart = Timer
For intLoop = 1 To 2
rstRoySched.MoveFirst
Do While Not rstRoySched.EOF
' Execute a simple operation for the performance test.
strTemp = rstRoySched!title_id
rstRoySched.MoveNext
Loop
Next intLoop
sngEnd = Timer
sngNoCache = sngEnd - sngStart
' Cache records in groups of 30 records.
rstRoySched.MoveFirst
rstRoySched.CacheSize = 30
sngStart = Timer
` Enumerate the Recordset object twice and record
' the elapsed time.
For intLoop = 1 To 2
rstRoySched.MoveFirst
Do While Not rstRoySched.EOF
` Execute a simple operation for the
` performance test.
strTemp = rstRoySched!title_id
rstRoySched.MoveNext
Loop
Next intLoop
sngEnd = Timer
sngCache = sngEnd - sngStart
' Display performance results.
MsgBox "Caching Performance Results:" & vbCr & _
" No cache: " & Format(sngNoCache, _
"##0.000") & " seconds" & vbCr & _
" 30-record cache: " & Format(sngCache, _
"##0.000") & " seconds"
rstRoySched.Close
End Sub
ADO
Recordset Object CursorLocation Property

Sets or returns the location of the cursor
engine. This property is read-only on UNIX.

CursorLocation Property Return Values

Sets or returns a Long value that
can be set to one of the following constants:

Constant

Description

adUseClient

Uses client-side cursors supplied by
a local cursor library. Local cursor engines will often allow many features
that driver-supplied cursors may not, so using this setting may provide an
advantage with respect to features that will be enabled. For
backward-compatibility, the synonym adUseClientBatch is also
supported.

adUseServer

Default. Uses data-provider or
driver-supplied cursors. These cursors are sometimes very flexible and allow
for some additional sensitivity to reflecting changes that others make to the
actual data source. However, some features of the Microsoft Client Cursor
Provider (such as disassociated recordsets) cannot be simulated.

CursorLocation Property Remarks

This property allows you to choose between
various cursor libraries accessible to the provider. Usually, you can choose
between using a client-side cursor library or one that is located on the
server.

This property setting only affects
connections established after the property has been set. Changing the CursorLocation
property has no effect on existing connections.

This property is read/write on a closed
recordset, and read-only on an open recordset.

CursorLocation Property Example

See the AbsolutePosition property
example.

ADO
Recordset Object CursorType Property

The type of cursor used in a Recordset
object.

CursorType Property Return Values

Sets or returns one of the following CursorTypeEnum
values:

Constant

Description

adOpenForwardOnly

Forward-only cursor. Default.
Identical to a static cursor except that you can only scroll forward through
records. This improves performance in situations when you only need to make a
single pass through a recordset.

adOpenKeyset

Keyset cursor. Like a dynamic
cursor, except that you can’t see records that other users add, although
records that other users delete are inaccessible from your recordset. Data
changes by other users are still visible.

adOpenDynamic

Dynamic cursor. Additions, changes,
and deletions by other users are visible, and all types of movement through
the recordset are allowed, except for bookmarks if the provider doesn’t
support them.

adOpenStatic

Static cursor. A static copy of a
set of records that you can use to find data or generate reports. Additions,
changes, or deletions by other users are not visible.

CursorType Property Remarks

Use the CursorType property to
specify the type of cursor that should be used when opening the Recordset

object. The CursorType property is read/write when the recordset is
closed and read-only when it is open.

If a provider does not support the
requested cursor type, the provider may return another cursor type. The CursorType
property will change to match the actual cursor type in use when the recordset
object is open. To verify specific functionality of the returned cursor, use
the ADO
Recordset Object Supports Method
. After you close the recordset, the CursorType
property reverts to its original setting.

The following chart shows the provider
functionality (identified by Supports method constants) required for
each cursor type.

Cursor Type

The
Supports method must return True for these constants

adOpenForwardOnly

none

adOpenKeyset

adBookmark, adHoldRecords,
adMovePrevious, adResync

adOpenDynamic

adMovePrevious

adOpenStatic

adBookmark, adHoldRecords,
adMovePrevious, adResync

Note iconNote

Although Supports(adUpdateBatch) may be true
for dynamic and forward-only cursors, for batch updates you should use either
a keyset or static cursor. Set the ADO
Recordset Object LockType Property
to adLockBatchOptimistic, and
set the CursorLocation property to adUseClient (or its synonym,
adUseClientBatch) to enable the Microsoft Client Cursor Engine, which
is required for batch updates.

CursorType Property Example

This Visual Basic example demonstrates
setting the CursorType and LockType properties before opening a
recordset. It also shows the value of the ADO
Recordset Object EditMode Property
under various conditions. The EditModeOutput

function is required for this procedure to run.

Public Sub EditModeX()
Dim cnn1 As ADODB.Connection
Dim rstEmployees As ADODB.Recordset
Dim strCnn As String
` Open recordset with data from Employee table.
Set cnn1 = New ADODB.Connection
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
cnn1.Open strCnn
Set rstEmployees = New ADODB.Recordset
Set rstEmployees.ActiveConnection = cnn1
rstEmployees.CursorType = adOpenKeyset
rstEmployees.LockType = adLockBatchOptimistic
rstEmployees.Open "employee", , , , adCmdTable
` Show the EditMode property under different editing
` states.
rstEmployees.AddNew
rstEmployees!emp_id = "T-T55555M"
rstEmployees!fname = "temp_fname"
rstEmployees!lname = "temp_lname"
EditModeOutput "After AddNew:", rstEmployees.EditMode
rstEmployees.UpdateBatch
EditModeOutput "After UpdateBatch:", rstEmployees.EditMode
rstEmployees!fname = "test"
EditModeOutput "After Edit:", rstEmployees.EditMode
rstEmployees.Close
` Delete new record because this is a demonstration.
cnn1.Execute "DELETE FROM employee WHERE emp_id = 'T-T55555M'"
End Sub
Public Function EditModeOutput(strTemp As String, _
intEditMode As Integer)
` Print report based on the value of the EditMode
` property.
Debug.Print strTemp
Debug.Print " EditMode = ";
Select Case intEditMode
Case adEditNone
Debug.Print "adEditNone"
Case adEditInProgress
Debug.Print "adEditInProgress"
Case adEditAdd
Debug.Print "adEditAdd"
End Select
End Function
ADO
Recordset Object EditMode Property

The editing status of the current record.

EditMode Property Return Values

Returns one of the following EditModeEnum
values:

Constant

Description

adEditNone

No editing operation is in progress.

adEditInProgress

The data in the current record has
been modified but not yet saved.

adEditAdd

The AddNew method has been
invoked and the current record in the copy buffer is a new record that hasn’t
been saved in the database.

EditMode Property Remarks

ADO
maintains an editing buffer associated with the current record. This property
indicates whether changes have been made to this buffer, or whether a new
record has been created. Use the EditMode property to determine the
editing status of the current record. You can test for pending changes if an
editing process has been interrupted and determine whether you need to use the ADO
Recordset Object Update Method
or ADO
Recordset Object CancelUpdate Method
.

See the ADO
Recordset Object AddNew Method
for a more detailed description of the EditMode
property under different editing conditions.

EditMode Property Example

See the ADO
Recordset Object CursorType Property
example.

ADO
Recordset Object Filter Property

A filter for data in a recordset.

Filter Property Return Values

Sets or returns a Variant value, which can
contain one of the following:

Criteria string

A string made up of one or more individual
clauses concatenated with AND or OR operators.

Array of bookmarks

An array of unique bookmark values that
point to records in the Recordset object. This return value is not
currently supported on UNIX.

One of the following FilterGroupEnum
values:

Constant

Description

adFilterNone

Removes the current filter and
restores all records to view.

adFilterPendingRecords

Enables you to view only records
that have changed but have not yet been sent to the server. Only applicable
for batch update mode. Not currently supported on UNIX.

adFilterAffectedRecords

Enables you to view only records
affected by the last Delete, Resync, UpdateBatch, or CancelBatch

call. Not currently supported on UNIX.

adFilterFetchedRecords

Enables you to view records in the
current cache, that is, the results of the last call to retrieve records from
the database. Not currently supported on UNIX.

Filter Property Remarks

Use the Filter property to
selectively screen out records in a Recordset object. The filtered
recordset becomes the current cursor. This affects other properties such as AbsolutePosition,
AbsolutePage, RecordCount,
and ADO
Recordset Object PageCount Property
that return values based on the
current cursor, since setting the Filter property to a specific value
will move the current record to the first record that satisfies the new value.

On UNIX systems the Filter property
is implemented for Recordset objects whose source is a SELECT query.
Setting the Filter property will resubmit the query with the criteria
string AND’d with the WHERE clause.

The criteria string is made up of clauses
in the form FieldName-Operator-Value (for example, “LastName =
‘Smith'”). You can create compound clauses by concatenating individual
clauses with AND (for example, “LastName = ‘Smith’ AND FirstName =
‘John'”) or OR (for example, “LastName = ‘Smith’ OR LastName =
‘Jones'”). Use the following guidelines for criteria strings:

FieldName

Must be a valid field name from the
recordset. If the field name contains spaces, you must enclose the name in
square brackets.

Operator

Must be one of the following: <, >,
<=, >=, <>, =, LIKE.

Value

The value with which you will compare the
field values (for example, ‘Smith’, #8/24/95#, 12.345 or $50.00). Use single
quotes with strings and pound signs (#) with dates. For numbers, you can use
decimal points, dollar signs, and scientific notation. If Operator is LIKE,
Value can use wildcards. Only the asterisk (*) and percent sign (%)
wildcards are allowed, and they must be the last character in the string. Value
may not be Null.

There is no precedence between AND
and OR. Clauses can be grouped within parentheses. However, you cannot
group clauses joined by an OR and then join the group to another clause
with an AND, like this:

(LastName = 'Smith' OR LastName = 'Jones') AND FirstName = 'John'

Instead, you would construct this filter
as:

(LastName = 'Smith' AND FirstName = 'John') OR
(LastName = 'Jones' AND FirstName = 'John')

In a LIKE clause, you can use a
wildcard at the beginning and end of the pattern (for example, LastName Like
‘*mit*’), or only at the end of the pattern (for example, LastName Like
‘Smit*’).

The filter constants make it easier to
resolve individual record conflicts during batch update mode by allowing you to
view, for example, only those records that were affected during the last ADO
Recordset Object UpdateBatch Method
call.

Setting the Filter property itself
may fail because of a conflict with the underlying data (for example, a record
has already been deleted by another user); in such a case, the provider returns
warnings to the ADO Errors
Collection
but does not halt program execution. A run-time error occurs
only if there are conflicts on all the requested records. Use the ADO
Recordset Object Status Property
to locate records with conflicts.

Setting the Filter property to a
zero-length string (“”) has the same effect as using the adFilterNone

constant.

Whenever the Filter property is
set, the current record position moves to the first record in the filtered
subset of records in the recordset. Similarly, when the Filter property
is cleared, the current record position moves to the first record in the
recordset.

See the ADO
Recordset Object Bookmark Property
for an explanation of bookmark values
from which you can build an array to use with the Filter property.

Filter Property Example

This Visual Basic example uses the Filter
property to open a new recordset based on a specified condition applied to an
existing recordset. It uses the RecordCount property to show the number
of records in the two recordsets. The FilterField function is required
for this procedure to run.

Public Sub FilterX()
Dim rstPublishers As ADODB.Recordset
Dim rstPublishersCountry As ADODB.Recordset
Dim strCnn As String
Dim intPublisherCount As Integer
Dim strCountry As String
Dim strMessage As String
` Open recordset with data from Publishers table.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set rstPublishers = New ADODB.Recordset
rstPublishers.CursorType = adOpenStatic
rstPublishers.Open "publishers", strCnn, , , adCmdTable
` Populate the Recordset.
intPublisherCount = rstPublishers.RecordCount
` Get user input.
strCountry = Trim(InputBox( _
"Enter a country to filter on:"))
If strCountry <> "" Then
` Open a filtered Recordset object.
Set rstPublishersCountry = _
FilterField(rstPublishers, "Country", strCountry)
If rstPublishersCountry.RecordCount = 0 Then
MsgBox "No publishers from that country."
Else
` Print number of records for the original
` Recordset object and the filtered Recordset
` object.
strMessage = "Orders in original recordset: " & _
vbCr & intPublisherCount & vbCr & _
"Orders in filtered recordset (Country = '" & _
strCountry & "'): " & vbCr & _
rstPublishersCountry.RecordCount
MsgBox strMessage
End If
rstPublishersCountry.Close
End If
End Sub
Public Function FilterField(rstTemp As ADODB.Recordset, _
strField As String, strFilter As String) As ADODB.Recordset
` Set a filter on the specified Recordset object and then
` open a new Recordset object.
rstTemp.Filter = strField & " = '" & strFilter & "'"
Set FilterField = rstTemp
End Function

Note iconNote

When you know the data you want to select, it’s usually
more efficient to open a recordset with an SQL statement. This example shows
how you can create just one recordset and obtain records from a particular
country.

Public Sub FilterX2()
Dim rstPublishers As ADODB.Recordset
Dim strCnn As String
` Open recordset with data from Publishers table.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set rstPublishers = New ADODB.Recordset
rstPublishers.CursorType = adOpenStatic
rstPublishers.Open "SELECT * FROM publishers " & _
"WHERE Country = 'USA'", strCnn, , , adCmdText
` Print current data in recordset.
rstPublishers.MoveFirst
Do While Not rstPublishers.EOF
Debug.Print rstPublishers!pub_name & ", " & _
rstPublishers!country
rstPublishers.MoveNext
Loop
rstPublishers.Close
End Sub
ADO
Recordset Object LockType Property

The type of locks placed on records during
editing.

LockType Property Return Values

Sets or returns one of the following LockTypeEnum
values:

Constant

Description

adLockReadOnly

Default. Read-only; the data cannot
be modified.

adLockPessimistic

Pessimistic locking, record by
record. The provider does what is necessary to ensure successful editing of
the records, usually by locking records at the data source immediately upon
editing.

adLockOptimistic

Optimistic locking, record by
record. The provider uses optimistic locking, locking records only when you
call the Update method.

adLockBatchOptimistic

Optimistic batch updates. Required
for batch update mode as opposed to immediate update mode.

LockType Property Remarks

Set the LockType property before
opening a recordset to specify what type of locking the provider should use
when opening it. Read the property to return the type of locking in use on an
open Recordset object. The LockType property is read/write when
the recordset is closed and read-only when it is open.

Providers may not support all lock types.
If a provider cannot support the requested LockType setting, it will
substitute another type of locking. To determine the actual locking
functionality available in a Recordset object, use the ADO
Recordset Object Supports Method
with adUpdate and adUpdateBatch.

LockType Property Example

See the ADO
Recordset Object CursorType Property
example.

ADO
Recordset Object MarshalOptions Property

Indicates which records are to be
marshaled back to the server. This is a client-side only property.

MarshalOptions Property Return Values

Sets or returns a Long value that
can be one of the following constants:

Constant

Description

adMarshalAll

Default. All rows are returned to
the server.

adMarshalModifiedOnly

Only modified rows are returned to
the server.

MarshalOptions Property Remarks

When using a client-side (ADOR) recordset,
records that have been modified on the client are written back to the
middle-tier or Web server through a technique called marshaling, the
process of packaging and sending interface method parameters across thread or
process boundaries. Setting the MarshalOptions property can improve
performance when modified remote data is marshaled for updating back to the
middle-tier or Web server.

Remote Data Service Usage: This
property is only used on a client-side (ADOR) recordset.

MarshalOptions Property Example

This Visual Basic example uses the MarshalOptions
property to specify what rows are sent back to the server—All Rows or only
Modified Rows.

Public Sub MarshalOptionsX()
Dim rstEmployees As ADODB.Recordset
Dim strCnn As String
Dim strOldFirst As String
Dim strOldLast As String
Dim strMessage As String
Dim strMarshalAll As String
Dim strMarshalModified As String
` Open recordset with names from Employee table.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set rstEmployees = New ADODB.Recordset
rstEmployees.CursorType = adOpenKeyset
rstEmployees.LockType = adLockOptimistic
rstEmployees.CursorLocation = adUseClient
rstEmployees.Open "SELECT fname, lname " & _
"FROM Employee ORDER BY lname", strCnn, , , adCmdText
` Store original data.
strOldFirst = rstEmployees!fname
strOldLast = rstEmployees!lname
` Change data in edit buffer.
rstEmployees!fname = "Linda"
rstEmployees!lname = "Kobara"
` Show contents of buffer and get user input.
strMessage = "Edit in progress:" & vbCr & _
" Original data = " & strOldFirst & " " & _
strOldLast & vbCr & " Data in buffer = " & _
rstEmployees!fname & " " & rstEmployees!lname & vbCr & vbCr & _
"Use Update to replace the original data with " & _
"the buffered data in the Recordset?"
strMarshalAll = "Would you like to send all the rows " & _
"in the recordset back to the server?"
strMarshalModified = "Would you like to send only " & _
"modified rows back to the server?"
If MsgBox(strMessage, vbYesNo) = vbYes Then
If MsgBox(strMarshalAll, vbYesNo) = vbYes Then
rstEmployees.MarshalOptions = adMarshalAll
rstEmployees.Update
ElseIf MsgBox(strMarshalModified, vbYesNo) = vbYes Then
rstEmployees.MarshalOptions = adMarshalModifiedOnly
rstEmployees.Update
End If
End If
` Show the resulting data.
MsgBox "Data in recordset = " & rstEmployees!fname & " " & _
rstEmployees!lname
` Restore original data because this is a demonstration.
If Not (strOldFirst = rstEmployees!fname And _
strOldLast = rstEmployees!lname) Then
rstEmployees!fname = strOldFirst
rstEmployees!lname = strOldLast
rstEmployees.Update
End If
rstEmployees.Close
End Sub
ADO
Recordset Object MaxRecords Property

The maximum number of records to return to
a recordset from a query.

MaxRecords Property Return Values

Sets or returns a Long value.
Default is zero (no limit).

MaxRecords Property Remarks

Use the MaxRecords property to
limit the number of records the provider returns from the data source. The
default setting of this property is zero, which means the provider returns all
requested records. The MaxRecords property is read/write when the
recordset is closed and read-only when it is open.

MaxRecords Property Example

This Visual Basic example uses the MaxRecords
property to open a recordset containing the 10 most expensive titles in the Titles
table.

Public Sub MaxRecordsX()
Dim rstTemp As ADODB.Recordset
Dim strCnn As String
` Open recordset containing the 10 most expensive
` titles in the Titles table.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set rstTemp = New ADODB.Recordset
rstTemp.MaxRecords = 10
rstTemp.Open "SELECT Title, Price FROM Titles " & _
"ORDER BY Price DESC", strCnn, , , adCmdText
` Display the contents of the recordset.
Debug.Print "Top Ten Titles by Price:"
Do While Not rstTemp.EOF
Debug.Print " " & rstTemp!Title & " - " & rstTemp!Price
rstTemp.MoveNext
Loop
rstTemp.Close
End Sub
ADO
Recordset Object PageCount Property

The number of pages of data the Recordset
object contains.

PageCount Property Return Values

Returns a Long value.

PageCount Property Remarks

Use the PageCount property to
determine how many pages of data are in the Recordset object. Pages

are groups of records whose size equals the ADO
Recordset Object PageSize Property
setting. Even if the last page is
incomplete, because there are fewer records than the PageSize value, it
counts as an additional page in the PageCount value. If the Recordset
object does not support this property, the value will be -1 to indicate that
the PageCount is indeterminable.

See the PageSize and AbsolutePage
properties for more on page functionality.

PageCount Property Example

See the AbsolutePage example.

ADO
Recordset Object PageSize Property

The number of records that constitute one
page in the recordset.

PageSize Property Return Values (ADO Recordset Object)

Sets or returns a Long value, the
number of records on a page. Default is 10.

PageSize Property Remarks (ADO Recordset Object)

Use the PageSize property to
determine how many records make up a logical page of data. Establishing a page
size allows you to use the AbsolutePage property to move to the first
record of a particular page. This is useful in Web-server scenarios when you
want to allow the user to page through data, viewing a certain number of
records at a time.

This property can be set at any time, and
its value will be used for calculating where the first record of a particular
page is.

PageSize Property Example (ADO Recordset Object)

See the AbsolutePage property
example.

ADO Recordset ObjectState Property

Describes the current state of an object.

State Property Return Values (ADO Recordset Object)

Sets or returns a Long value that
can be one of the following constants:

Constant

Description

AdStateClosed

Default. The object is closed.

AdStateOpen

The object is open.

State Property Remarks (ADO Recordset Object)

You can use the State property to
determine the current state of a given object at any time.

ADO
Recordset Object Status Property

Indicates the status of the current record
with respect to batch updates or other bulk operations.

Status Property Return Values (ADO Recordset Object)

Returns a sum of one or more of the
following RecordStatusEnum values:

Constant

Description

adRecOK

The record was successfully updated.

adRecNew

The record is new.

adRecModified

The record was modified.

adRecDeleted

The record was deleted.

adRecUnmodified

The record was not modified.

adRecInvalid

The record was not saved because its
bookmark is invalid.

adRecMultipleChanges

The record was not saved because it
would have affected multiple records.

adRecPendingChanges

The record was not saved because it
refers to a pending insert.

adRecCanceled

The record was not saved because the
operation was canceled.

adRecCantRelease

The new record was not saved because
of existing record locks.

adRecConcurrencyViolation

The record was not saved because
optimistic concurrency was in use.

adRecIntegrityViolation

The record was not saved because the
user violated integrity constraints.

adRecMaxChangesExceeded

The record was not saved because
there were too many pending changes.

adRecObjectOpen

The record was not saved because of
a conflict with an open storage object.

adRecOutOfMemory

The record was not saved because the
computer has run out of memory.

adRecPermissionDenied

The record was not saved because the
user has insufficient permissions.

adRecSchemaViolation

The record was not saved because it
violates the structure of the underlying database.

adRecDBDeleted

The record has already been deleted
from the data source.

Status Property Remarks (ADO Recordset Object)

Use the Status property to see what
changes are pending for records modified during batch updating. You can also
use the Status property to view the status of records that fail during
bulk operations such as when you call the ADO
Recordset Object Resync Method
, ADO
Recordset Object UpdateBatch Method
, or ADO
Recordset Object CancelBatch Method
methods on a Recordset

object, or set the ADO
Recordset Object Filter Property
on a Recordset object to an array
of bookmarks. With this property, you can determine how a given record failed
and resolve it accordingly.

Status Property Example (ADO Recordset Object)

This example uses the Status

property to display which records have been modified in a batch operation
before a batch update has occurred.

Public Sub StatusX()
Dim rstTitles As ADODB.Recordset
Dim strCnn As String
` Open recordset for batch update.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set rstTitles = New ADODB.Recordset
rstTitles.CursorType = adOpenKeyset
rstTitles.LockType = adLockBatchOptimistic
rstTitles.Open "titles", strCnn, , , adCmdTable
` Change the type of psychology titles.
Do Until rstTitles.EOF
If Trim(rstTitles!Type) = "psychology" Then
rstTitles!Type = "self_help"
End If
rstTitles.MoveNext
Loop
` Display Title ID and status.
rstTitles.MoveFirst
Do Until rstTitles.EOF
If rstTitles.Status = adRecModified Then
Debug.Print rstTitles!title_id & " - Modified"
Else
Debug.Print rstTitles!title_id
End If
rstTitles.MoveNext
Loop
` Cancel the update because this is a demonstration.
rstTitles.CancelBatch
rstTitles.Close
End Sub
ADO
Recordset Object Source Property

The source for the data in a Recordset
object (Command object, SQL statement, table name, or stored procedure).

Source Property Return Values (ADO Recordset Object)

Sets a String value or Command
object reference; returns only a String value.

Source Property Remarks (ADO Recordset Object)

Use the Source property to specify
a data source for a Recordset object using one of the following: an ADO Command
Object
variable, an SQL statement, a stored procedure, or a table name. The

Source property is read/write for closed Recordset objects and
read-only for open Recordset objects.

If you set the Source property to a
Command object, the ActiveConnection property of the Recordset

object will inherit the value of the ActiveConnection property for the
specified Command object. However, reading the Source property
does not return a Command object; instead, it returns the CommandText
property of the Command object to which you set the Source

property.

If the Source property is an SQL
statement, a stored procedure, or a table name, you can optimize performance by
passing the appropriate Options argument with the ADO
Recordset Object Open Method
call.

Source Property Example (ADO Recordset Object)

This Visual Basic example demonstrates the
Source property by opening three Recordset objects based on
different data sources.

Public Sub SourceX()
Dim cnn1 As ADODB.Connection
Dim rstTitles As ADODB.Recordset
Dim rstPublishers As ADODB.Recordset
Dim rstTitlesPublishers As ADODB.Recordset
Dim cmdSQL As ADODB.Command
Dim strCnn As String
Dim strSQL As String
` Open a connection.
Set cnn1 = New ADODB.Connection
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
cnn1.Open strCnn
` Open a recordset based on a command object.
Set cmdSQL = New ADODB.Command
Set cmdSQL.ActiveConnection = cnn1
cmdSQL.CommandText = "Select title, type, pubdate " & _
"FROM titles ORDER BY title"
Set rstTitles = cmdSQL.Execute()
` Open a recordset based on a table.
Set rstPublishers = New ADODB.Recordset
rstPublishers.Open "publishers", strCnn, , , adCmdTable
` Open a recordset based on an SQL string.
Set rstTitlesPublishers = New ADODB.Recordset
strSQL = "SELECT title_ID AS TitleID, title AS Title, " & _
"publishers.pub_id AS PubID, pub_name AS PubName " & _
"FROM publishers INNER JOIN titles " & _
"ON publishers.pub_id = titles.pub_id " & _
"ORDER BY Title"
rstTitlesPublishers.Open strSQL, strCnn, , , adCmdText
` Use the Source property to display the source of each recordset.
MsgBox "rstTitles source: " & vbCr & _
rstTitles.Source & vbCr & vbCr & _
"rstPublishers source: " & vbCr & _
rstPublishers.Source & vbCr & vbCr & _
"rstTitlesPublishers source: " & vbCr & _
rstTitlesPublishers.Source
rstTitles.Close
rstPublishers.Close
rstTitlesPublishers.Close
cnn1.Close
End Sub
ADO
Recordset Object RecordCount Property

The current number of records in a Recordset
object.

RecordCount Property Return Values

Returns a Long value.

RecordCount Property Remarks

Use the RecordCount property to
find out how many records are in a Recordset object. The property
returns -1 when ADO

cannot determine the number of records. Reading the RecordCount property
on a closed recordset causes an error.

If the Recordset object supports
approximate positioning or bookmarks—that is, ADO
Recordset Object Supports Method
(adApproxPosition) or Supports
(adBookmark)
, respectively, returns True—this value will be the
exact number of records in the recordset regardless of whether it has been
fully populated. If the Recordset object does not support approximate
positioning, this property may be a significant drain on resources because all
records will have to be retrieved and counted to return an accurate RecordCount

value.

RecordCount Property Example

This Visual Basic example uses the Filter
property to open a new recordset based on a specified condition applied to an
existing recordset. It uses the RecordCount property to show the number
of records in the two recordsets. The FilterField function is required
for this procedure to run.

Public Sub FilterX()
Dim rstPublishers As ADODB.Recordset
Dim rstPublishersCountry As ADODB.Recordset
Dim strCnn As String
Dim intPublisherCount As Integer
Dim strCountry As String
Dim strMessage As String
` Open recordset with data from Publishers table.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set rstPublishers = New ADODB.Recordset
rstPublishers.CursorType = adOpenStatic
rstPublishers.Open "publishers", strCnn, , , adCmdTable
` Populate the Recordset.
intPublisherCount = rstPublishers.RecordCount
` Get user input.
strCountry = Trim(InputBox( _
"Enter a country to filter on:"))
If strCountry <> "" Then
` Open a filtered Recordset object.
Set rstPublishersCountry = _
FilterField(rstPublishers, "Country", strCountry)
If rstPublishersCountry.RecordCount = 0 Then
MsgBox "No publishers from that country."
Else
` Print number of records for the original
` Recordset object and the filtered Recordset
` object.
strMessage = "Orders in original recordset: " & _
vbCr & intPublisherCount & vbCr & _
"Orders in filtered recordset (Country = '" & _
strCountry & "'): " & vbCr & _
rstPublishersCountry.RecordCount
MsgBox strMessage
End If
rstPublishersCountry.Close
End If
End Sub
Public Function FilterField(rstTemp As ADODB.Recordset, _
strField As String, strFilter As String) As ADODB.Recordset
` Set a filter on the specified Recordset object and then
` open a new Recordset object.
rstTemp.Filter = strField & " = '" & strFilter & "'"
Set FilterField = rstTemp
End Function

Note iconNote

When you know the data you want to select, it’s usually
more efficient to open a recordset with an SQL statement. This example shows
how you can create just one recordset and obtain records from a particular
country.

Public Sub FilterX2()
Dim rstPublishers As ADODB.Recordset
Dim strCnn As String
` Open recordset with data from Publishers table.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set rstPublishers = New ADODB.Recordset
rstPublishers.CursorType = adOpenStatic
rstPublishers.Open "SELECT * FROM publishers " & _
"WHERE Country = 'USA'", strCnn, , , adCmdText
` Print current data in recordset.
rstPublishers.MoveFirst
Do While Not rstPublishers.EOF
Debug.Print rstPublishers!pub_name & ", " & _
rstPublishers!country
rstPublishers.MoveNext
Loop
rstPublishers.Close
End Sub

ADO
Recordset Object Remarks


Use Recordset objects to manipulate
data from a provider. In ADO,
data is almost entirely manipulated using Recordset objects. All Recordset

objects are constructed using records (rows) and fields (columns). Depending on
the functionality supported by the provider, some Recordset methods or
properties may not be available.

Recordset objects can also be run
remotely. For example, in a Web-based application, you can open a Recordset
on the client, using the progID “ADOR.” The Remote Data Service
provides a mechanism for local data caching and local cursor movement in remote
recordset data. A client-side recordset can be used in the same way as a server-side
recordset, and supports almost all of the Recordset object’s normal
methods and properties. Recordset methods and properties that are not
supported on a client-side recordset, or that behave differently, are noted in
the topics for those properties and methods.

There are four different cursor types
defined in ADO:

Cursor

Description

Dynamic

Allows you to view additions,
changes and deletions by other users, and allows all types of movement
through the recordset that don’t rely on bookmarks; allows bookmarks if the
provider supports them.

Keyset

Behaves like a dynamic cursor,
except that it prevents you from seeing records that other users add, and
prevents access to records that other users delete. Data change by other
users will still be visible. It always supports bookmarks and therefore
allows all types of movement through the recordset.

Static

Provides a static copy of a set of
records for you to use to find data or generate reports. Always allows
bookmarks and therefore allows all types of movement through the recordset.
Additions, changes, or deletions by other users will not be visible. This is
the only type of cursor allowed when you open a client-side (ADOR) Recordset
object.

Forward-only

Behaves identically to a dynamic
cursor except that it allows you to scroll only forward through records. This
improves performance in situations where you need to make only a single pass
through a recordset.

Set the ADO
Recordset Object CursorType Property
prior to opening the recordset to
choose the cursor type, or pass a CursorType argument with the ADO
Recordset Object Open Method
. Some providers don’t support all cursor
types. Check the documentation for the provider. If you don’t specify a cursor
type, ADO opens
a forward-only cursor by default.

When used with some providers (such as the
Microsoft ODBC Provider for OLE DB in conjunction with Microsoft SQL Server),
you can create Recordset objects independently of a previously defined ADO Connection
Object
by passing a connection string with the Open method. ADO still creates a Connection
object, but it doesn’t assign that object to an object variable. However, if
you are opening multiple Recordset objects over the same connection, you
should explicitly create and open a Connection object; this assigns the Connection

object to an object variable. If you do not use this object variable when
opening your Recordset objects, ADO
creates a new Connection object for each new recordset, even if you pass
the same connection string.

You can create as many Recordset
objects as needed.

When you open a recordset, the current
record is positioned to the first record (if any) and the ADO
Recordset Object BOF, EOF Properties
are set to False. If there are
no records, the BOF and EOF property settings are True.

Use the ADO
Recordset Object MoveFirst, MoveLast, MoveNext, MovePrevious Methods
, as well
as the ADO
Recordset Object Move Method
, and the AbsolutePosition, AbsolutePage,
and ADO
Recordset Object Filter Property
properties to reposition the
current record, assuming the provider supports the relevant functionality.
Forward-only Recordset objects support only the MoveNext method.
When you use the Move methods to visit each record (or enumerate the
recordset), you can use the BOF and EOF properties to see if
you’ve moved beyond the beginning or end of the recordset.

Recordset objects may support two
types of updating: immediate and batched. In immediate updating, all changes to
data are written immediately to the underlying data source once you call the ADO
Recordset Object Update Method
. You can also pass arrays of values as
parameters with the ADO
Recordset Object AddNew Method
and Update methods and simultaneously
update several fields in a record.

If a provider supports batch updating, you
can have the provider cache changes to more than one record and then transmit
them in a single call to the database with the ADO
Recordset Object UpdateBatch Method
. This applies to changes made with the AddNew,

Update, and ADO
Recordset Object Delete Method
methods. After you call the UpdateBatch
method, you can use the ADO
Recordset Object Status Property
to check for any data conflicts in order
to resolve them. Batch updating is not currently supported on UNIX.

Note iconNote

To execute a query without using an ADO Command
Object
, pass a query string to the ADO
Recordset Object Open Method
of a Recordset object. However, a Command

object is required when you want to retain the command text and re-execute
it, or use query parameters.

ADO
Collections


Collections

Description

Errors

Contains all stored Error
objects, all of which pertain to a single operation involving ADO.

Fields

Contains all stored Field
objects of a Recordset object.

Parameters

Contains all the Parameter

objects of a Command object.

Properties

Contains all the Property
objects for the specific instance of an object. This collection is not
currently supported on UNIX.

Methods

Append

Appends a new object to the Parameters
collection.

Clear

Clears the contents of an Errors

collection.

Delete

Deletes an object from the Parameters
collection.

Item

Returns a specific member of a
collection by name or ordinal number.

Refresh

Updates the objects in a collection
to reflect objects available from and specific to the provider.

Properties

Count

The number of objects in a
collection.

In this section:

ADO Errors
Collection

ADO Fields
Collection

ADO Parameters
Collection

ADO Properties
Collection

ADO Collections
Methods

ADO Collections
Properties

ADO Errors
Collection


The Errors collection contains all
stored ADO
Error Object
objects created in response to a single failure
involving the provider.

In this section:

ADO Errors
Collection Remarks

ADO Errors
Collection Remarks


Any operation involving ADO objects can generate one or more provider
errors. As each error occurs, one or more ADO Error Object

objects may be placed in the Errors collection of the ADO Connection
Object
. When another ADO
operation generates an error, the Errors collection is cleared, and the
new set of Error objects may be placed in the Errors collection.

Each Error object represents a
specific provider error, not an ADO
error. ADO
errors are exposed to the run-time exception-handling mechanism. For example,
in Microsoft Visual Basic, the occurrence of an ADO-specific error will trigger
an On Error event and appear in the Err object.

ADO
operations that don’t generate an error have no effect on the Errors
collection. Use the ADO Collections
Clear Method
to manually clear the Errors collection.

The set of Error objects in the Errors

collection describes all errors that occurred in response to a single
statement. Enumerating the specific errors in the Errors collection
enables your error-handling routines to more precisely determine the cause and
origin of an error, and take appropriate steps to recover.

Some properties and methods return
warnings that appear as Error objects in the Errors collection
but do not halt a program’s execution. Before you call the ADO
Recordset Object Resync Method
, ADO
Recordset Object UpdateBatch Method
, or ADO
Recordset Object CancelBatch Method
methods on an ADO Recordset
Object
, or before you set the ADO
Recordset Object Filter Property
on a Recordset object, call the Clear

method on the Errors collection so that you can read the Count
Property of the Errors collection to test for returned warnings.

Note iconNote

See the ADO Error
Object
for a more detailed explanation of the way a single ADO operation can
generate multiple errors.

ADO Fields
Collection


The Fields collection contains all
the Field objects of a Recordset object.

In this section:

ADO Fields
Collection Remarks

ADO Fields
Collection Remarks


An ADO Recordset
Object
has a Fields collection made up of ADO Field Object
objects. Each Field object corresponds to a column in the recordset.
You can populate the Fields collection before opening the recordset by
calling the ADO
Collections Refresh Method
on the collection.

Note iconNote

See the ADO Field
Object
for a more detailed explanation of how to use Field

objects.

ADO
Parameters Collection


The Parameters collection contains
all the Parameter objects of a Command object.

In this section:

ADO Parameters
Collection Remarks

ADO
Parameters Collection Remarks


An ADO Command
Object
has a Parameters collection made up of ADO Parameter
Object
objects. Using the ADO Collections
Refresh Method
on a Command object’s Parameters collection
retrieves provider parameter information for the stored procedure or
parameterized query specified in the Command object. Some providers do
not support stored procedure calls or parameterized queries; calling the Refresh

method on the Parameters collection when using such a provider will
return an error.

If you have not defined your own Parameter
objects and you access the Parameters collection before calling the Refresh
method, ADO

will automatically call the method and populate the collection for you.

You can minimize calls to the provider to
improve performance if you know the properties of the parameters associated
with the stored procedure or parameterized query you wish to call. Use the CreateParameter
method to create Parameter objects with the appropriate property
settings and use the ADO Collections
Append Method
to add them to the Parameters collection. This lets
you set and return parameter values without having to call the provider for the
parameter information. If you are writing to a provider that does not supply
parameter information, you must manually populate the Parameters

collection using this method to be able to use parameters at all. Use the ADO Collections
Delete Method
to remove Parameter objects from the Parameters
collection if necessary.

ADO
Properties Collection


The Properties collection contains
all the Property objects for a specific instance of an object. The Property
collection is not currently supported on UNIX.

In this section:

ADO Properties
Collection Remarks

ADO
Properties Collection Remarks


Some ADO
objects have a Properties collection made up of ADO Property
Object
objects. Each Property object corresponds to a
characteristic of the ADO
object specific to the provider.

Note iconNote

See the ADO Property
Object
topic for a more detailed explanation of how to use Property

objects.

ADO
Collections Methods


This section discusses ADO collections methods.

In this section:

ADO Collections
Append Method

ADO Collections
Clear Method

ADO Collections
Delete Method

ADO Collections
Item Method

ADO Collections
Refresh Method

ADO
Collections Append Method


Appends an object to a collection.

In this section:

Append Method
Applies To

Append Method
Syntax

Append Method
Parameters

Append Method
Remarks

Append Method
Examples

Append Method Applies To

ADO Parameters
Collection

Append Method Syntax

collection.Append object
Append Method Parameters

object

An object variable representing the object
to be appended.

Append Method Remarks

Use the Append method on a
collection to add an object to that collection. This method is available only
on the Parameters collection of a ADO Command
Object
. You must set the ADO
Parameter Object Type Property
of an ADO Parameter
Object
before appending it to the Parameters collection. If you
select a variable-length data type, you must also set the ADO
Parameter Object Size Property
to a value greater than zero.

By describing the parameter yourself, you
can minimize calls to the provider and consequently improve performance when
using stored procedures or parameterized queries. However, you must know the
properties of the parameters associated with the stored procedure or
parameterized query you wish to call. Use the CreateParameter method to
create Parameter objects with the appropriate property settings and use
the Append method to add them to the Parameters collection. This
lets you set and return parameter values without having to call the provider
for the parameter information. If you are writing to a provider that does not supply
parameter information, you must manually populate the Parameters
collection using this method to be able to use parameters at all.

Append Method Examples

This Visual Basic example uses the Append
and CreateParameter methods to execute a stored procedure with an input
parameter.

Public Sub AppendX()
Dim cnn1 As ADODB.Connection
Dim cmdByRoyalty As ADODB.Command
Dim prmByRoyalty As ADODB.Parameter
Dim rstByRoyalty As ADODB.Recordset
Dim rstAuthors As ADODB.Recordset
Dim intRoyalty As Integer
Dim strAuthorID As String
Dim strCnn As String
` Open connection.
Set cnn1 = New ADODB.Connection
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
cnn1.Open strCnn
cnn1.CursorLocation = adUseClient
` Open command object with one parameter.
Set cmdByRoyalty = New ADODB.Command
cmdByRoyalty.CommandText = "byroyalty"
cmdByRoyalty.CommandType = adCmdStoredProc
` Get parameter value and append parameter.
intRoyalty = Trim(InputBox("Enter royalty:"))
Set prmByRoyalty = cmdByRoyalty.CreateParameter("percentage", _
adInteger, adParamInput)
cmdByRoyalty.Parameters.Append prmByRoyalty
prmByRoyalty.Value = intRoyalty
` Create recordset by executing the command.
Set cmdByRoyalty.ActiveConnection = cnn1
Set rstByRoyalty = cmdByRoyalty.Execute
` Open the Authors table to display author names.
Set rstAuthors = New ADODB.Recordset
rstAuthors.Open "authors", cnn1, , , adCmdTable
` Print current data in the recordset, adding
` author names from Authors table.
Debug.Print "Authors with " & intRoyalty & " percent royalty"
Do While Not rstByRoyalty.EOF
strAuthorID = rstByRoyalty!au_id
Debug.Print " " & rstByRoyalty!au_id & ", ";
rstAuthors.Filter = "au_id = '" & strAuthorID & "'"
Debug.Print rstAuthors!au_fname & " " & rstAuthors!au_lname
rstByRoyalty.MoveNext
Loop
rstByRoyalty.Close
rstAuthors.Close
cnn1.Close
End Sub

ADO
Collections Clear Method


Removes all of the objects in a
collection.

In this section:

Clear Method
Applies To

Clear Method
Syntax

Clear Method
Remarks

Clear Method
Examples

Clear Method Applies To

ADO Errors
Collection

Clear Method Syntax

Errors.Clear
Clear Method Remarks

Use the Clear method on the Errors

collection to remove all existing ADO Error Object
objects from the collection. When an error occurs, ADO automatically clears the Errors
collection and fills it with Error objects based on the new error.
However, some properties and methods return warnings that appear as Error
objects in the Errors collection but do not halt a program’s execution.
Before you call the ADO
Recordset Object Resync Method
, ADO
Recordset Object UpdateBatch Method
, or ADO
Recordset Object CancelBatch Method
methods on an ADO Recordset
Object
or before you set the ADO
Recordset Object Filter Property
on a Recordset object, call the Clear

method on the Errors collection. Doing so enables you to read the ADO Collections
Count Property
of the Errors collection to test for returned
warnings as a result of these specific calls.

Clear Method Examples

See the ADO
Command Object Execute Method
.

ADO
Collections Delete Method


Deletes an object from the Parameters
collection.

In this section:

Delete Method
Applies To

Delete Method
Syntax

Delete Method
Parameters

Delete Method
Remarks

Delete Method Applies To

ADO Parameters
Collection

Delete Method Syntax

object.Parameters.Delete ( Index )
Delete Method Parameters

object

A Command object.

Index

A Variant that evaluates either
to the name or to the ordinal number of an object in a collection.

Delete Method Remarks

Using the Delete method on a Parameters
collection lets you remove one of the objects in the collection. This method is
available only on the Parameters collection of an ADO Command
Object
. You must the use ADO Parameter
Object
object’s ADO
Parameter Object Name Property
or its collection index when calling the Delete

method; an object variable is not a valid argument.

ADO
Collections Item Method


Returns a specific member of a collection
by name or ordinal number.

In this section:

Item Method
Applies To

Item Method
Syntax

Item Method
Parameters

Item Method
Return Values

Item Method
Remarks

Item Method Applies To

ADO Errors
Collection
, ADO Fields
Collection
, ADO Parameters
Collection
, ADO Properties
Collection

Item Method Syntax

Set object = collection.Item ( Index )
Item Method Parameters

object

Object reference created.

Index

A Variant that evaluates either
to the name or to the ordinal number of an object in a collection.

Item Method Return Values

Returns an object reference.

Item Method Remarks

Use the Item method to return a
specific object in a collection. If the method cannot find an object in the
collection corresponding to the Index argument, an error occurs. Also,
some collections don’t support named objects; for these collections, you must
use ordinal number references.

The Item method is the default
method for all collections; therefore, the following syntax forms are
interchangeable:

collection.Item (Index)
collection (Index)

ADO
Collections Refresh Method


Updates the objects in a collection to
reflect objects available from and specific to the provider.

In this section:

Refresh Method
Applies To

Refresh Method
Syntax

Refresh Method
Parameters Collection

Refresh Method
Fields Collection

Refresh Method
Properties Collection

Refresh Method
Example

Refresh Method Applies To

ADO Fields
Collection
, ADO Parameters
Collection
, ADO Properties
Collection

Refresh Method Syntax

collection.Refresh
Refresh Method Parameters Collection

Using the Refresh method on a ADO Command
Object
object’s Parameters collection retrieves provider-side
parameter information for the stored procedure or parameterized query specified
in the Command object. The collection will be empty for providers that
do not support stored procedure calls or parameterized queries.

You should set the ActiveConnection property
of the Command object to a valid ADO Connection
Object
, the ADO
Command Object CommandText Property
to a valid command, and the ADO
Command Object CommandType Property
to adCmdStoredProc before
calling the ADO
Collections Refresh Method
.

If you access the Parameters
collection before calling the Refresh method, ADO will automatically call the method and
populate the collection for you.

Note iconNote

If you use the Refresh method to obtain parameter
information from the provider and it returns one or more variable-length data
type ADO
Parameter Object
objects, ADO

may allocate memory for the parameters based on their maximum potential size,
which will cause an error during execution. You should explicitly set the ADO
Parameter Object Size Property
for these parameters before calling the ADO
Command Object Execute Method
to prevent errors.

Refresh Method Fields Collection

Using the Refresh method on the Fields
collection has no visible effect. To retrieve changes from the underlying
database structure, you must use either the ADO
Recordset Object Requery Method
or, if the Recordset object does not
support bookmarks, the ADO
Recordset Object MoveFirst, MoveLast, MoveNext, MovePrevious Methods
method.

Refresh Method Properties Collection

Using the Refresh method on a Properties
collection of some objects populates the collection with the dynamic properties
the provider exposes. These properties provide information about functionality
specific to the provider beyond the built-in properties ADO supports.

The Refresh method accomplishes
different tasks depending on the collection from which you call it.

Refresh Method Example

This Visual Basic example demonstrates
using the Refresh method to refresh the Parameters collection for
a stored procedure Command object.

Public Sub RefreshX()
Dim cnn1 As ADODB.Connection
Dim cmdByRoyalty As ADODB.Command
Dim rstByRoyalty As ADODB.Recordset
Dim rstAuthors As ADODB.Recordset
Dim intRoyalty As Integer
Dim strAuthorID As String
Dim strCnn As String
' Open connection.
Set cnn1 = New ADODB.Connection
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
cnn1.Open strCnn
' Open a command object for a stored procedure
' with one parameter.
Set cmdByRoyalty = New ADODB.Command
Set cmdByRoyalty.ActiveConnection = cnn1
cmdByRoyalty.CommandText = "byroyalty"
cmdByRoyalty.CommandType = adCmdStoredProc
cmdByRoyalty.Parameters.Refresh
' Get paramater value and execute the command,
' storing the results in a recordset.
intRoyalty = Trim(InputBox("Enter royalty:"))
cmdByRoyalty.Parameters(1) = intRoyalty
Set rstByRoyalty = cmdByRoyalty.Execute()
` Open the Authors table to get author names for display.
Set rstAuthors = New ADODB.Recordset
rstAuthors.Open "authors", cnn1, , , adCmdTable
' Print current data in the recordset, adding
' author names from Authors table.
Debug.Print "Authors with " & intRoyalty & " percent royalty"
Do While Not rstByRoyalty.EOF
strAuthorID = rstByRoyalty!au_id
Debug.Print " " & rstByRoyalty!au_id & ", ";
rstAuthors.Filter = "au_id = '" & strAuthorID & "'"
Debug.Print rstAuthors!au_fname & " " & _
rstAuthors!au_lname
rstByRoyalty.MoveNext
Loop
rstByRoyalty.Close
rstAuthors.Close
cnn1.Close
End Sub

ADO
Collections Properties


This section lists ADO collections properties.

In this section:

ADO Collections
Count Property

ADO
Collections Count Property


The number of objects in a collection.

In this section:

Count Property
Applies To

Count Property
Return Values

Count Property
Remarks

Count Property
Example

Count Property Applies To

ADO Errors
Collection
, ADO Fields
Collection
, ADO Parameters
Collection
, ADO Properties
Collection

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: