ACC2000: Changes in Use of DISTINCTROW in Microsoft Access 2000

Microsoft Access 2000 and Microsoft Jet database engine version 4.0 use the DISTINCTROW predicate differently than did earlier versions of Microsoft Access. This article discusses the differences in queries that use DISTINCTROW.

The DISTINCTROW predicate is the SQL equivalent of setting the UniqueRecords property in a query to Yes. In Microsoft Access 2000, the default setting for the UniqueRecords property in a query is No, which eliminates DISTINCTROW from the query's SQL statement. In Microsoft Access 2.0 and 7.0, the default setting for the UniqueRecords property is Yes, which adds DISTINCTROW to the query's SQL statement.

Changes in the Microsoft Jet database engine included with Microsoft Access 2000 cause the program to handle the SQL reserved word DISTINCTROW differently than it did in versions earlier than Access 97. Three types of queries are affected by the change: update queries, delete queries, and select queries.

NOTE: Using DISTINCTROW in a query has no effect unless the query contains two or more joined tables.

Update Queries

If you run an update query that you imported from a Microsoft Access 2.0 or 7.0 database, or if you run an existing update query in a database that you converted in Microsoft Access 2000 from Access 7.0 and earlier versions, you may receive the following error message: To resolve this error, set the query's UniqueRecords property to No by following these steps:

1. Open the update query in Design view.
2. If the property sheet is not already open, on the View menu, click Properties.
3. Click an empty area in the upper half of the query window so that the property sheet displays "Query Properties" in the title bar.
4. Set the UniqueRecords property to No.
5. Save the query, close it, and then run the query.

Delete Queries

When a delete query contains more than one table, such as a query that deletes duplicate records from one of the tables, the UniqueRecords property must be set to Yes for all versions of Microsoft Access. However, because the default value for UniqueRecords is No in Access 2000, you must set the value of this property manually when you create a new delete query in Access 2000. To do so, follow these steps:

1. Open the delete query in Design view.
2. If the property sheet is not already open, on the View menu, click Properties.
3. Click an empty area in the upper half of the query window so that the property sheet displays "Query Properties" in the title bar.
4. Set the UniqueRecords property to Yes.
5. Save the query, close it, and then run the query.

For additional information about using a delete query to remove duplicate records, click the article number below to view the article in the Microsoft Knowledge Base:

Select Queries

Some select queries that are updateable in Microsoft Access 2.0 or 7.0 may display the following error message when you try to update them in Microsoft Access 2000: To resolve this error, set the query's UniqueRecords property to No by following these steps:

1. Open the select query in Design view.
2. If the property sheet is not already open, on the View menu, click Properties.
3. Click an empty area in the upper half of the query window so that the property sheet displays "Query Properties" in the title bar.
4. Set the UniqueRecords property to No.
5. Save the query, close it, and then run the query. Note that you may now update the data in the query.

For more information about the DISTINCTROW and DISTINCT predicates, click Microsoft Access Help on the Help menu, type all, distinct, distinctrow, top predicates in the Office Assistant or the Answer Wizard, and then click Search to view the topic.