ACC: Microsoft Access Does Not Use Record Numbers

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

Novice: Requires knowledge of the user interface on single-user computers.

This article discusses the reasons why Microsoft Access does not use record numbers, as do some other database products.

MORE INFORMATION

Many database products, such as Microsoft FoxPro, dBASE IV, and Paradox, use a record number that is physically associated with the record. For example, the first record in a table has record number 1, the second record has record number 2, and so on. The record numbers remain part of the record as long as the record exists. The first record entered into a table is always record number 1, even if the table is sorted and the record appears somewhere other than the first position in the table.

Microsoft Access does not use this approach. When you open a table in Datasheet view, a number appears at the bottom of the form. This number indicates the position of the record in the recordset, not the record's natural position in the table. For example, if a record with the key value "Anderson" is the first record in a recordset, the number 1 appears at the bottom of the form. However, if you sort the same records on a different key, the record may no longer appear as the first record and a different number may appear at the bottom of the form.

When you open a table, form, or report, Microsoft Access creates a dynaset, a dynamic copy of the appropriate recordset, and assigns numbers to the records therein. These numbers are used only by the user interface to provide a visually unique identifier for each record.

Microsoft Access uses this approach because assigning a physical record number to each record is not "relationally correct." According to relational database theory, data may appear in any order at any time. Therefore, the key value, not a random record number, provides the only reliable method to identify a record.

Because no record number is available, Microsoft Access provides another method to identify records in a recordset. Using the Bookmark property, you can write a procedure to find a target record, store its bookmark value in a variable, move to other records, and return to the original record. For more information about bookmarks, search on "Bookmark" using the Help menu.

dBASE IV and Paradox are manufactured by Borland International, Inc., a vendor independent of Microsoft; we make no warranty, implied or otherwise, regarding this product's performance or reliability.