Microsoft KB Archive/207761

= ACC2000: Changes in Use of DISTINCTROW in Microsoft Access 2000 =

Article ID: 207761

Article Last Modified on 6/24/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q207761



Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).



SUMMARY
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.



MORE INFORMATION
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:

Operation must use an updateable query.

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:

209183 ACC2000: How to Delete Duplicate Records from a Table

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:

This Recordset is not updateable.

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.

