ACC2000: Why Microsoft Access Does Not Use Record Numbers

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

Many database products, such as Microsoft FoxPro, dBASE IV, and Paradox, use a record number that is physically associated with a particular record. For example, the first record in a table is record number 1, the second record is 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, a form, or a 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, click Microsoft Access Help on the Help menu, type bookmark property in the Office Assistant or the Answer Wizard, and then click Search to view the topic.