The following notes were taken from the Visual Basic on-line help documentation concerning the Data Access Object. They are meant to provide a start in understanding the DAO Control.

Data Control

Provides access to data stored in databases using any one of three types of Recordset objects. The Data control enables you to move from record to record and to display and manipulate data from the records in bound controls. Without a Data control or an equivalent data source control like the RemoteData control, data-aware (bound) controls on a form can't automatically access data.

Syntax

Data

Remarks

You can perform most data access operations using the Data control without writing any code at all. Data-aware controls bound to a Data control automatically display data from one or more fields for the current record or, in some cases, for a set of records on either side of the current record. The Data control performs all operations on the current record.

If the Data control is instructed to move to a different record, all bound controls automatically pass any changes to the Data control to be saved in the database. The Data control then moves to the requested record and passes back data from the current record to the bound controls where it's displayed.

The Data control automatically handles a number of contingencies including empty recordsets, adding new records, editing and updating existing records, and handling some types of errors. However, in more sophisticated applications, you need to trap some error conditions that the Data control can't handle. For example, if the Microsoft Jet database engine has a problem accessing the database file, doesn't have permission, or can't execute the query as coded, a trappable error results. If the error occurs before your application procedures start or due to some internal errors, the Error event is triggered.

Bound Controls

The DataList, DataCombo, DataGrid, and MSHFlexGrid controls are all capable of managing sets of records when bound to a Data control. All of these controls permit several records to be displayed or manipulated at once.

The intrinsic Picture, Label, TextBox, CheckBox, Image, OLE, ListBox and ComboBox controls are also data-aware and can be bound to a single field of a Recordset managed by the Data control. Additional data-aware controls like the MaskedEdit and RichTextBox controls are available in the Professional and Enterprise Editions and from third-party vendors.

Operation

Once the application begins, Visual Basic uses Data control properties to open the selected database, create a Database object and create a Recordset object. The Data control's Database and Recordset properties refer to the newly created Database and Recordset objects which may be manipulated independently of the Data control -- with or without bound controls. The Data control is initialized before the initial Form_Load event for the form on which it is placed. If any errors occur during this initialization step a non-trappable error results.

When Visual Basic uses the Jet database engine to create a Recordset, no other Visual Basic operations or events can occur until the operation is complete. However, other Windows-based applications are permitted to continue executing while the Recordset is being created. If the user presses CTRL+BREAK while the Jet engine is building a Recordset, the operation is terminated, a trappable error results, and the Recordset property of the Data control is set to Nothing. In design time, a second CTRL+BREAK causes Visual Basic to display the Debug window.

When you use a Data control to create a Recordset object or when you create a Recordset object in code and assign it to the Data control, the Microsoft Jet database engine automatically populates the Recordset object. As a result, bookmarks (and for snapshot-type Recordset objects, recordset data) are saved in local memory; the user doesn't need to manipulate the Data control, and you don't need to invoke the MoveLast method in code. Page locks used to create the Recordset are released more quickly, making it possible for other Recordset objects to access the same data. Recordset objects created in code but not assigned to the Data control aren't automatically populated by the Jet engine. Populate these objects through code. Because of the way that the Data control populates its Recordset in the background, an additional cloned Recordset might be created.

You can manipulate the Data control with the mouse, moving from record to record or to the beginning or end of the Recordset. The EOFAction and BOFAction properties determine what happens when the user moves to the beginning or end of a Recordset with the mouse. You can't set focus to the Data control.

Validation

Use the Validate event and the DataChanged property to perform last minute checks on the records being written to the database.

Data Access Objects

You can use the Database and Recordset data access objects created by the Data control in your procedures. The Database and Recordset objects each have properties and methods of their own, and you can write procedures that use these properties and methods to manipulate your data.

For example, the MoveNext method of a Recordset object moves the current record to the next record in the Recordset. To invoke this method, you could use this code:

Data1.Recordset.MoveNext

The Data control is capable of accessing any of the three types of Jet engine Version 3.0 Recordset objects. If you don't select a recordset type, a dynaset-type Recordset is created.

In many cases, the default type and configuration of the Recordset object created is extremely inefficient. That is, you might not need an updatable, fully-scrollable, keyset-type cursor to access your data. For example, a read-only, forward-only, snapshot-type Recordset might be far faster to create than the default cursor. Be sure to choose the most efficient Type, Exclusive, Options and ReadOnly properties possible for your situation.

Note The constants used to request a specific Recordset type when using the Data control are different than the constants used to determine the type of Recordset created or to create a Recordset using the OpenRecordset method.

To select a specific type of Recordset, set the Data control's RecordsetType property to:

Important The Data control cannot be used to access Recordset objects created with the dbForwardOnly option bit set.

Professional and Enterprise Editions

As far as data access is concerned, the primary difference between the Learning Edition, Professional and Enterprise Editions of Visual Basic is the ability to create new data access objects. In the Learning Edition, you can't declare (with the Dim keyword) variables as data access objects in code. This means that only the Data control can create Database and Recordset objects.

In the Professional and Enterprise Editions, you can create a new Recordset object and assign it to the Data control's Recordset property. Any bound controls connected to the Data control permit manipulation of the records in the Recordset you created. Make sure that your bound controls' DataField properties are set to field names that are valid in the new Recordset.

Stored Queries

Another important option when using the Data control is the ability to execute stored queries. If you create a QueryDef object beforehand, the Data control can execute it and create a Recordset using the QueryDef object's stored SQL, Connect and other properties. To execute a QueryDef, set the Data control's RecordSource property to the QueryDef name and use the Refresh method.

If the stored QueryDef contains parameters, you need to create the Recordset and pass it to the Data control.

BOF/EOF Handling

The Data control can also manage what happens when you encounter a Recordset with no records. By changing the EOFAction property, you can program the Data control to enter AddNew mode automatically.

You can program the Data control to automatically snap to the top or bottom of its parent form by using the Align property. In either case, the Data control is resized horizontally to fill the width of its parent form whenever the parent form is resized. This property allows a Data control to be placed on an MDI form without requiring an enclosing Picture control.

BOFAction, EOFAction Properties

Returns or sets a value indicating what action the Data control takes when the BOF or EOF properties are True.

Syntax

object.BOFAction [= integer]

object.EOFAction [= integer]

The BOFAction and EOFAction property syntax's have these parts:

Settings

For the BOFAction property, the settings for integer are:

For the EOFAction property, the settings for integer are:

Remarks

These constants are listed in the Visual Basic (VB) object library in the Object Browser.

If you set the EOFAction property to vbEOFActionAddNew, once the user moves the current record pointer to EOF using the Data control, the current record is positioned to a new record in the copy buffer. At this point you can edit the newly added record. If you make changes to the new record and the user subsequently moves the current record pointer using the Data control, the record is automatically appended to the Recordset. If you don't make changes to this new record, and reposition the current record to another record, the new record is discarded. If you use the Data control to position to another record while positioned over this new record, another new record is created.

When you use code to manipulate Recordsets created with the Data control, the EOFAction property has no effect -- it only takes effect when manipulating the Data control with the mouse.

In situations where the Data control Recordset is returned with no records, or after the last record has been deleted, using the vbEOFActionAddNew option for the EOFAction property greatly simplifies your code because a new record is always editable as the current record. If this option is not enabled, you are likely to trigger a "No current record" error.

Data Type

Integer

DatabaseName Property

Returns or sets the name and location of the source of data for a Data control.

Syntax

object.DatabaseName [ = pathname ]

The DatabaseName property syntax has these parts:

Remarks

If your network system supports it, the pathname argument can be a fully qualified network path name such as \\Myserver\Myshare\Database.mdb.

The database type is indicated by the file or directory that pathname points to, as follows:

For ODBC databases, such as SQL Server and Oracle, this property can be left blank if the control's Connect property identifies a data source name (DSN) that identifies an ODBC data source entry in the registry.

If you change the DatabaseName property after the control's Database object is open, you must use the Refresh method to open the new database.

Note For better performance when accessing external databases, it's recommended that you attach external database tables to a Microsoft Jet engine database (.mdb) and use the name of the Jet .mdb database in the DatabaseName property.

Data Type

String

Recordset Property

Returns or sets a Recordset object defined by a Data control's properties or by an existing Recordset object.

Syntax

Set object.Recordset [= value ]

The Recordset property syntax has these parts:

Remarks

The Data control is automatically initialized when your application starts before the initial Form_Load procedure. If the Connect, DatabaseName, Options, RecordSource, Exclusive, ReadOnly and RecordsetType properties are valid, or if you set these Data control properties at run time and use the Refresh method, the Microsoft Jet database engine attempts to create a new Recordset object based on those properties. This Recordset is accessible through the Data control's Recordset property. If, however, one or more of these properties is set incorrectly at design time, an untrappable error occurs when Visual Basic attempts to use the properties to open the specified database and create the Recordset object.

You can use the Recordset property as you would any other Recordset object. For example, you can use any of the Recordset methods or properties and examine the structure of the Recordset object's underlying schema.

You can also request the type of Recordset to be created by setting the Data control's RecordsetType property. If you don't request a specific type, a dynaset-type Recordset is created. Using the RecordsetType property, you can request to create either a table-, snapshot- or dynaset-type Recordset. However, if the Jet engine can't create the type requested, a trappable error occurs.

In many cases, the default type and configuration of the Recordset object created is extremely inefficient. That is, you might not need an updatable, fully-scrollable, keyset-type cursor to access your data. For example, a read-only, snapshot-type Recordset might be far faster to create than the default Recordset. Be sure to choose the most efficient Type, Exclusive, Options and ReadOnly properties possible for your situation.

The type of Recordset created can be determined at run time by examining the Recordset property's Type property or the Data control's RecordsetType property. Note, however, that the constants used for the type of Recordset created are different. For example:

If Data1.Recordset.Type = dbOpenDynaset Then ...

If Data1.RecordsetType = dbDynasetType Then ...

A Recordset might not be updatable even if you request a dynaset- or table-type Recordset. If the underlying database, table, or field isn't updatable, all or portions of your Recordset may be read-only. Examine the Database and Recordset objects' Updatable property or the Field object's DataUpdatable property to determine if your code can change the records. Even when the DataUpdatable property returns True, there are situations where the underlying data fields might not be updatable if, for example, you do not have sufficient permissions to make changes. Other factors can also prevent fields from being updatable.

The number of records returned by the Recordset can be determined by moving to the last record in the Recordset and examining the Recordset object's RecordCount property. Before you move to the last record, the value returned by the RecordCount property only reflects the number of rows processed by the Jet engine. The following example shows how you can combine the RecordCount property of a Recordset with the Recordset property to display the number of records in a Data control's recordset:

Data1.Recordset.MoveLast

MsgBox "Records: " & Data1.Recordset.RecordCount

Professional and Enterprise Editions

If you create a Recordset object using either code or another Data control, you can set the Recordset property of the Data control to this new Recordset. Any existing Recordset in the Data control, and the Database object associated with it are released when a new Recordset is assigned to the Recordset property.

Note When the Recordset property is set, the Data control doesn't close the current Recordset or Database, but it does release it. If there are no other users, the database is closed automatically. You may wish to consider closing the Recordset and Database associated with the Data control prior to setting the Recordset property using the Close method.

Make sure the DataField properties of the bound controls connected to the Data control are set to match the new Recordset object's field names.

For example, to create a Recordset in code and pass it to an existing Data control:

Dim Db As Database, Rs As Recordset ' Defined as public variables.

Sub ApplyRecordset()

Set Db = Workspaces(0).OpenDatabase("BIBLIO.MDB")

Set Rs = Db.OpenRecordset("AUTHORS") ' Defaults to Table object.

Set Data1.Recordset = Rs ' Assign Recordset.

Data1.Recordset.Index = "PrimaryKey"

Debug.print Rs.Type ' Show type created.

End Sub

You can use this technique to create an MDI parent and child data connection with a single hidden Data control on the MDI parent form and another visible Data control on the MDI child. In the MDI child's Form_Load event, set the child's Data control Recordset property to the parent's Data control Recordset property. Using this technique synchronizes all the child forms and their bound controls with the parent.

Note The Data control doesn't support forward-only Recordset objects. If you try to assign a forward-only Recordset object to the Recordset property of the Data control, a trappable error results.

All Recordset objects created by the Data control are created in Workspaces(0) except ODBCDirect (DefaultType = dbUseODBC) Recordset objects. If you need to use the Data control to manipulate a database in another Workspace, use the technique shown above to open the database in the desired Workspace, create a new Recordset and set the Data control's Recordset property to this new Recordset.

Important You can always reference the properties of the Data control's Recordset by using the Recordset property. By directly referencing the Recordset, you can determine the Index to use with Table objects, the Parameters collection of a QueryDef, or the Recordset type.

Data Type

Recordset

RecordSource Property

Returns or sets the underlying table, SQL statement, or stored procedure.

Syntax

object.RecordSource [= value ]

The RecordSource property syntax has these parts:

Settings

The settings for value are:

Remarks

The RecordSource property specifies the source of the records accessible through bound controls on your form.

If you set the RecordSource property to the name of an existing table in the database, all of the fields in that table are visible to the bound controls attached to the Data control. For table-type recordsets (RecordsetType = vbRSTypeTable), the order of the records retrieved is set by the Index object that you select using the Index property of the Recordset. For dynaset-type and snapshot-type Recordset objects, you can order the records by using a SQL statement with an Order By clause in the RecordSource property of the Data control. Otherwise, the data is returned in no particular order.

If you set the RecordSource property to the name of an existing QueryDef in the database, all fields returned by the QueryDef are visible to the bound controls attached to the Data control. The order of the records retrieved is set by the QueryDef object's query. For example, the QueryDef may include an ORDER BY clause to change the order of the records returned by the Recordset created by the Data control or a WHERE clause to filter the records. If the QueryDef doesn't specify an order, the data is returned in no particular order.

Note At design-time, the QueryDef objects displayed in the Properties window for the RecordSource property are filtered out to display only QueryDef objects that are usable with the Data control. QueryDef objects which have parameters, and QueryDef objects which have the following types are not displayed: dbQAction, dbQCrosstab, dbQSQLPassThrough and dbQSetOperation.

If you set the RecordSource property to an SQL statement that returns records, all fields returned by the SQL query are visible to the bound controls attached to the Data control. This statement may include an ORDER BY clause to change the order of the records returned by the Recordset created by the Data control or a WHERE clause to filter the records. If the database you specify in the Database and Connect property isn't a Microsoft Jet engine database, and if the dbSQLPassThrough option is set in the Options property, your SQL query must use the syntax required by that database engine.

Note Whenever your QueryDef or SQL statement returns a value from an expression, the field name of the expression is created automatically by the Microsoft Jet database engine. Generally, the name is Expr1 followed by a three-character number beginning with 000. For example, the first expression would be named: Expr1000.

In most cases you'll want to alias expressions so you know the name of the column to bind to the bound control. See the SQL SELECT statement AS clause for more information.

After changing the value of the RecordSource property at run time, you must use the Refresh method to enable the change and rebuild the Recordset.

At run time, if the Recordset specifies an invalid Table name, QueryDef name, or contains invalid SQL syntax, a trappable error will result. If this error occurs during the initial Form_Load procedure, the error is not trappable.

Note Make sure each bound control has a valid setting for its DataField property. If you change the setting of a Data control's RecordSource property and then use Refresh, the Recordset identifies the new object. This may invalidate the DataField settings of bound controls and cause a trappable error.

Data Type

String

Visible Property

Returns or sets a value indicating whether an object is visible or hidden.

Syntax

object.Visible [= boolean]

The Visible property syntax has these parts:

Settings

The settings for boolean are:

Remarks

To hide an object at startup, set the Visible property to False at design time. Setting this property in code enables you to hide and later redisplay a control at run time in response to a particular event.

Note Using the Show or Hide method on a form is the same as setting the form's Visible property in code to True or False, respectively.

Refresh Method

Forces a complete repaint of a form or control.

Syntax

object.Refresh

The object placeholder represents an object expression that evaluates to an object in the Applies To list.

Remarks

Use the Refresh method when you want to:

· Completely display one form while another form loads.

· Update the contents of a file-system list box, such as a FileListBox control.

· Update the data structures of a Data control.

Refresh can't be used on MDI forms, but can be used on MDI child forms. You can't use Refresh on Menu or Timer controls.

Generally, painting a form or control is handled automatically while no events are occurring. However, there may be situations where you want the form or control updated immediately. For example, if you use a file list box, a directory list box, or a drive list box to show the current status of the directory structure, you can use Refresh to update the list whenever a change is made to the directory structure.

You can use the Refresh method on a Data control to open or reopen the database (if the DatabaseName, ReadOnly, Exclusive, or Connect property settings have changed) and rebuild the dynaset in the control's Recordset property.

Recordset Object

A Recordset object represents the records in a base table or the records that result from running a query.

Remarks

You use Recordset objects to manipulate data in a database at the record level. When you use DAO objects, you manipulate data almost entirely using Recordset objects. All Recordset objects are constructed using records (rows) and fields (columns). There are five types of Recordset objects:

· Table-type Recordset -- representation in code of a base table that you can use to add, change, or delete records from a single database table (Microsoft Jet workspaces only).

· Dynaset-type Recordset -- the result of a query that can have updatable records. A dynaset-type Recordset object is a dynamic set of records that you can use to add, change, or delete records from an underlying database table or tables. A dynaset-type Recordset object can contain fields from one or more tables in a database. This type corresponds to an ODBC keyset cursor.

· Snapshot-type Recordset -- a static copy of a set of records that you can use to find data or generate reports. A snapshot-type Recordset object can contain fields from one or more tables in a database but can't be updated. This type corresponds to an ODBC static cursor.

· Forward-only-type Recordset -- identical to a snapshot except that no cursor is provided. You can only scroll forward through records. This improves performance in situations where you only need to make a single pass through a result set. This type corresponds to an ODBC forward-only cursor.

· Dynamic-type Recordset -- a query result set from one or more base tables in which you can add, change, or delete records from a row-returning query. Further, records other users add, delete, or edit in the base tables also appear in your Recordset. This type corresponds to an ODBC dynamic cursor (ODBCDirect workspaces only).

You can choose the type of Recordset object you want to create using the type argument of the OpenRecordset method.

In a Microsoft Jet workspace, if you don't specify a type, DAO attempts to create the type of Recordset with the most functionality available, starting with table. If this type isn't available, DAO attempts a dynaset, then a snapshot, and finally a forward-only type Recordset object.

In an ODBCDirect workspace, if you don't specify a type, DAO attempts to create the type of Recordset with the fastest query response, starting with forward-only. If this type isn't available, DAO attempts a snapshot, then a dynaset, and finally a dynamic- type Recordset object.

When creating a Recordset object using a non-linked TableDef object in a Microsoft Jet workspace, table-type Recordset objects are created. Only dynaset-type or snapshot-type Recordset objects can be created with linked tables or tables in Microsoft Jet-connected ODBC databases.

A new Recordset object is automatically added to the Recordsets collection when you open the object, and is automatically removed when you close it.

Note If you use variables to represent a Recordset object and the Database object that contains the Recordset, make sure the variables have the same scope, or lifetime. For example, if you declare a public variable that represents a Recordset object, make sure the variable that represents the Database containing the Recordset is also public, or is declared in a Sub or Function procedure using the Static keyword.

You can create as many Recordset object variables as needed. Different Recordset objects can access the same tables, queries, and fields without conflicting.

Dynaset-, snapshot-, and forward-only-type Recordset objects are stored in local memory. If there isn't enough space in local memory to store the data, the Microsoft Jet database engine saves the additional data to TEMP disk space. If this space is exhausted, a trappable error occurs.

The default collection of a Recordset object is the Fields collection, and the default property of a Field object is the Value property. Use these defaults to simplify your code.

When you create a Recordset object, the current record is positioned to the first record if there are any records. If there are no records, the RecordCount property setting is 0, and the BOF and EOF property settings are True.

You can use the MoveNext, MovePrevious, MoveFirst, and MoveLast methods to reposition the current record. Forward-only-type Recordset objects support only the MoveNext method. When using the Move methods to visit each record (or "walk" through the Recordset), you can use the BOF and EOF properties to check for the beginning or end of the Recordset object.

With dynaset- and snapshot-type Recordset objects in a Microsoft Jet workspace, you can also use the Find methods, such as FindFirst, to locate a specific record based on criteria. If the record isn't found, the NoMatch property is set to True. For table-type Recordset objects, you can scan records using the Seek method.

The Type property indicates the type of Recordset object created, and the Updatable property indicates whether you can change the object's records.

Information about the structure of a base table, such as the names and data types of each Field object and any Index objects, is stored in a TableDef object.

To refer to a Recordset object in a collection by its ordinal number or by its Name property setting, use any of the following syntax forms:

Recordsets(0)

Recordsets("name")

Recordsets![name]

Note You can open a Recordset object from the same data source or database more than once, creating duplicate names in the Recordsets collection. You should assign Recordset objects to object variables and refer to them by variable name.

AbsolutePosition Property

Sets or returns the relative record number of a Recordset object's current record.

Settings and Return Values

The setting or return value is a Long integer from 0 to one less than the number of records in the Recordset object. It corresponds to the ordinal position of the current record in the Recordset object specified by the object.

Remarks

You can use the AbsolutePosition property to position the current record pointer to a specific record based on its ordinal position in a dynaset- or snapshot-type Recordset object. You can also determine the current record number by checking the AbsolutePosition property setting.

Because the AbsolutePosition property value is zero-based (that is, a setting of 0 refers to the first record in the Recordset object), you cannot set it to a value greater than or equal to the number of populated records; doing so causes a trappable error. You can determine the number of populated records in the Recordset object by checking the RecordCount property setting. The maximum allowable setting for the AbsolutePosition property is the value of the RecordCount property minus 1.

If there is no current record, as when there are no records in the Recordset object, AbsolutePosition returns -1. If the current record is deleted, the AbsolutePosition property value isn't defined, and a trappable error occurs if it's referenced. New records are added to the end of the sequence.

You shouldn't use this property as a surrogate record number. Bookmarks are still the recommended way of retaining and returning to a given position and are the only way to position the current record across all types of Recordset objects. In particular, the position of a record changes when one or more records preceding it are deleted. There is also no assurance that a record will have the same absolute position if the Recordset object is re-created again because the order of individual records within a Recordset object isn't guaranteed unless it's created with an SQL statement by using an ORDER BY clause.

Notes

· Setting the AbsolutePosition property to a value greater than zero on a newly opened but unpopulated Recordset object causes a trappable error. Populate the Recordset object first with the MoveLast method.

· The AbsolutePosition property isn't available on forward-only-type Recordset objects, or on Recordset objects opened from pass-through queries against Microsoft Jet-connected ODBC databases.

Bookmark Property

Sets or returns a bookmark that uniquely identifies the current record in a Recordset object.

Settings and Return Values

The setting or return value is a string expression or variant expression that evaluates to a valid bookmark. The data type is a Variant array of Byte data.

Remarks

For a Recordset object based entirely on Microsoft Jet tables, the value of the Bookmarkable property is True, and you can use the Bookmark property with that Recordset. Other database products may not support bookmarks, however. For example, you can't use bookmarks in any Recordset object based on a linked Paradox table that has no primary key.

When you create or open a Recordset object, each of its records already has a unique bookmark. You can save the bookmark for the current record by assigning 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.

There is no limit to the number of bookmarks you can establish. To create a bookmark for a record other than the current record, move to the desired record and assign the value of the Bookmark property to a String variable that identifies the record.

To make sure the Recordset object supports bookmarks, check the value of its Bookmarkable property before you use the Bookmark property. If the Bookmarkable property is False, the Recordset object doesn't support bookmarks, and using the Bookmark property results in a trappable error.

If you use the 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 can be used interchangeably. However, you can't use bookmarks from different Recordset objects interchangeably, even if they were created by using the same object or the same SQL statement.

If you set the Bookmark property to a value that represents a deleted record, a trappable error occurs.

The value of the Bookmark property isn't the same as a record number.

Filter Property

Sets or returns a value that determines the records included in a subsequently opened Recordset object (Microsoft Jet workspaces only).

Settings and Return Values

The setting or return value is a String data type that contains the WHERE clause of an SQL statement without the reserved word WHERE.

Remarks

Use the Filter property to apply a filter to a dynaset-, snapshot-, or forward-only-type Recordset object.

You can use the Filter property to restrict the records returned from an existing object when a new Recordset object is opened based on an existing Recordset object.

In many cases, it's faster to open a new Recordset object by using an SQL statement that includes a WHERE clause.

Use the U.S. date format (month-day-year) when you filter fields containing dates, even if you're not using the U.S. version of the Microsoft Jet database engine (in which case you must assemble any dates by concatenating strings, for example, strMonth & "-" & strDay & "-" & strYear). Otherwise, the data may not be filtered as you expect.

If you set the property to a string concatenated with a non-integer value, and the system parameters specify a non-U.S. decimal character such as a comma (for example, strFilter = "PRICE > " & lngPrice, and lngPrice = 125,50), an error occurs when you try to open the next Recordset. This is because during concatenation, the number will be converted to a string using your system's default decimal character, and Microsoft Jet SQL only accepts U.S. decimal characters.

RecordCount Property

Returns the number of records accessed in a Recordset object, or the total number of records in a table-type Recordset or TableDef object.

Return Values

The return value is a Long data type.

Remarks

Use the RecordCount property to find out how many records in a Recordset or TableDef object have been accessed. The RecordCount property doesn't indicate how many records are contained in a dynaset-, snapshot-, or forward-only-type Recordset object until all records have been accessed. Once the last record has been accessed, the RecordCount property indicates the total number of undeleted records in the Recordset or TableDef object. To force the last record to be accessed, use the MoveLast method on the Recordset object. You can also use an SQL Count function to determine the approximate number of records your query will return.

Note Using the MoveLast method to populate a newly opened Recordset negatively impacts performance. Unless it is necessary to have an accurate RecordCount as soon as you open a Recordset, it's better to wait until you populate the Recordset with other portions of code before checking the RecordCount property.

As your application deletes records in a dynaset-type Recordset object, the value of the RecordCount property decreases. However, records deleted by other users aren't reflected by the RecordCount property until the current record is positioned to a deleted record. If you execute a transaction that affects the RecordCount property setting and you subsequently roll back the transaction, the RecordCount property won't reflect the actual number of remaining records.

The RecordCount property of a snapshot- or forward-only-type Recordset object isn't affected by changes in the underlying tables.

A Recordset or TableDef object with no records has a RecordCount property setting of 0.

When you work with linked TableDef objects, the RecordCount property setting is always -1.

Using the Requery method on a Recordset object resets the RecordCount property just as if the query were re-executed.

AddNew Method

Creates a new record for an updatable Recordset object.

Syntax

recordset.AddNew

The recordset placeholder is an object variable that represents an updatable Recordset object to which you want to add a new record.

Remarks

Use the AddNew method to create and add a new record in the Recordset object named by recordset. This method sets the fields to default values, and if no default values are specified, it sets the fields to Null (the default values specified for a table-type Recordset).

After you modify the new record, use the Update method to save the changes and add the record to the Recordset. No changes occur in the database until you use the Update method.

Caution If you issue an AddNew and then perform any operation that moves to another record, but without using Update, your changes are lost without warning. In addition, if you close the Recordset or end the procedure that declares the Recordset or its Database object, the new record is discarded without warning.

Note When you use AddNew in a Microsoft Jet workspace and the database engine has to create a new page to hold the current record, page locking is pessimistic. If the new record fits in an existing page, page locking is optimistic.

If you haven't moved to the last record of your Recordset, records added to base tables by other processes may be included if they are positioned beyond the current record. If you add a record to your own Recordset, however, the record is visible in the Recordset and included in the underlying table where it becomes visible to any new Recordset objects.

The position of the new record depends on the type of Recordset:

· In a dynaset-type Recordset object, records are inserted at the end of the Recordset, regardless of any sorting or ordering rules that were in effect when the Recordset was opened.

· In a table-type Recordset object whose Index property has been set, records are returned in their proper place in the sort order. If you haven't set the Index property, new records are returned at the end of the Recordset.

The record that was current before you used AddNew remains current. If you want to make the new record current, you can set the Bookmark property to the bookmark identified by the LastModified property setting.

Note To add, edit, or delete a record, there must be a unique index on the record in the underlying data source. If not, a "Permission denied" error will occur on the AddNew, Delete, or Edit method call in a Microsoft Jet workspace, or an "Invalid argument" error will occur on the Update call in an ODBCDirect workspace.

FindFirst, FindLast, FindNext, FindPrevious Methods

Locates the first, last, next, or previous record in a dynaset- or snapshot-type Recordset object that satisfies the specified criteria and makes that record the current record (Microsoft Jet workspaces only).

Syntax

recordset.{FindFirst | FindLast | FindNext | FindPrevious} criteria

The Find methods have these parts.

Remarks

If you want to include all the records in your search -- not just those that meet a specific condition -- use the Move methods to move from record to record. To locate a record in a table-type Recordset, use the Seek method.

If a record matching the criteria isn't located, the current record pointer is unknown, and the NoMatch property is set to True. If recordset contains more than one record that satisfies the criteria, FindFirst locates the first occurrence, FindNext locates the next occurrence, and so on.

Each of the Find methods begins its search from the location and in the direction specified in the following table.

When you use the FindLast method, the Microsoft Jet database engine fully populates your Recordset before beginning the search, if this hasn't already happened.

Using one of the Find methods isn't the same as using a Move method, however, which simply makes the first, last, next, or previous record current without specifying a condition. You can follow a Find operation with a Move operation.

Always check the value of the NoMatch property to determine whether the Find operation has succeeded. If the search succeeds, NoMatch is False. If it fails, NoMatch is True and the current record isn't defined. In this case, you must position the current record pointer back to a valid record.

Using the Find methods with Microsoft Jet-connected ODBC-accessed recordsets can be inefficient. You may find that rephrasing your criteria to locate a specific record is faster, especially when working with large recordsets.

In an ODBCDirect workspace, the Find and Seek methods are not available on any type of Recordset object, because executing a Find or Seek through an ODBC connection is not very efficient over the network. Instead, you should design the query (that is, using the source argument to the OpenRecordset method) with an appropriate WHERE clause that restricts the returned records to only those that meet the criteria you would otherwise use in a Find or Seek method.

When working with Microsoft Jet-connected ODBC databases and large dynaset-type Recordset objects, you might discover that using the Find methods or using the Sort or Filter property is slow. To improve performance, use SQL queries with customized ORDER BY or WHERE clauses, parameter queries, or QueryDef objects that retrieve specific indexed records.

You should use the U.S. date format (month-day-year) when you search for fields containing dates, even if you're not using the U.S. version of the Microsoft Jet database engine; otherwise, the data may not be found. Use the Visual Basic Format function to convert the date. For example:

rstEmployees.FindFirst "HireDate > #" _

& Format(mydate, 'm-d-yy' ) & "#"

If criteria is composed of a string concatenated with a non-integer value, and the system parameters specify a non-U.S. decimal character such as a comma (for example, strSQL = "PRICE > " & lngPrice, and lngPrice = 125,50), an error occurs when you try to call the method. This is because during concatenation, the number will be converted to a string using your system's default decimal character, and Microsoft Jet SQL only accepts U.S. decimal characters.

Notes

· For best performance, the criteria should be in either the form "field = value" where field is an indexed field in the underlying base table, or "field LIKE prefix" where field is an indexed field in the underlying base table and prefix is a prefix search string (for example, "ART*").

· In general, for equivalent types of searches, the Seek method provides better performance than the Find methods. This assumes that table-type Recordset objects alone can satisfy your needs.

MoveFirst, MoveLast, MoveNext, MovePrevious Methods

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

Syntax

recordset.{MoveFirst | MoveLast [dbRunAsync] | MoveNext | MovePrevious}

The recordset placeholder is an object variable that represents an open Recordset object.

Remarks

Use the Move methods to move from record to record without applying a condition.

Caution If you edit the current record, be sure you use the Update method to save the changes before you move to another record. If you move to another record without updating, your changes are lost without warning.

When you open a Recordset, the first record is current and the BOF property is False. If the Recordset contains no records, the BOF property is True, and there is no current record.

If the first or last record is already current when you use MoveFirst or MoveLast, the current record doesn't change.

If you use MovePrevious when the first record is current, the BOF property is True, and there is no current record. If you use MovePrevious again, an error occurs, and BOF remains True.

If you use MoveNext when the last record is current, the EOF property is True, and there is no current record. If you use MoveNext again, an error occurs, and EOF remains True.

If recordset refers to a table-type Recordset (Microsoft Jet workspaces only), movement follows the current index. You can set the current index by using the Index property. If you don't set the current index, the order of returned records is undefined.

Important You can use the MoveLast method to fully populate a dynaset- or snapshot-type Recordset to provide the current number of records in the Recordset. However, if you use MoveLast in this way, you can slow down your application's performance. You should only use MoveLast to get a record count if it is absolutely necessary to obtain an accurate record count on a newly opened Recordset. If you use the dbRunAsync constant with MoveLast, the method call is asynchronous. You can use the StillExecuting property to determine when the Recordset is fully populated, and you can use the Cancel method to terminate execution of the asynchronous MoveLast method call.

You can't use the MoveFirst, MoveLast, and MovePrevious methods on a forward-only-type Recordset object.

To move the position of the current record in a Recordset object a specific number of records forward or backward, use the Move method.

Update Method

Saves the contents of the copy buffer to an updatable Recordset object.

Syntax

recordset.Update (type, force )

The Update method syntax has the following parts.

Settings

You can use the following values for the type argument. You can use the non-default values only if batch updating is enabled.

Remarks

Use Update to save the current record and any changes you've made to it.

Caution Changes to the current record are lost if:

· You use the Edit or AddNew method, and then move to another record without first using Update.

· You use Edit or AddNew, and then use Edit or AddNew again without first using Update.

· You set the Bookmark property to another record.

· You close recordset without first using Update.

· You cancel the Edit operation by using CancelUpdate.

To edit a record, use the Edit method to copy the contents of the current record to the copy buffer. If you don't use Edit first, an error occurs when you use Update or attempt to change a field's value.

In an ODBCDirect workspace, you can do batch updates, provided the cursor library supports batch updates, and the Recordset was opened with the optimistic batch locking option.

In a Microsoft Jet workspace, when the Recordset object's LockEdits property setting is True (pessimistically locked) in a multiuser environment, the record remains locked from the time Edit is used until the Update method is executed or the edit is canceled. If the LockEdits property setting is False (optimistically locked), the record is locked and compared with the pre-edited record just before it is updated in the database. If the record has changed since you used the Edit method, the Update operation fails. Microsoft Jet-connected ODBC and installable ISAM databases always use optimistic locking. To continue the Update operation with your changes, use the Update method again. To revert to the record as the other user changed it, refresh the current record by using Move 0.

Note To add, edit, or delete a record, there must be a unique index on the record in the underlying data source. If not, a "Permission denied" error will occur on the AddNew, Delete, or Edit method call in a Microsoft Jet workspace, or an "Invalid argument" error will occur on the Update call in an ODBCDirect workspace.