ACC2000: Query on a Table That Is Named with SQL Reserved Words Causes Error (252740)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q252740
Moderate: Requires basic macro, coding, and interoperability skills.

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

SYMPTOMS

If you create a query based on a table that has a name beginning with the following Jet SQL reserved words

ALL (for example, "All YourName")
DISTINCT (for example, "Distinct YourName")
DISTINCTROW (for example, "Distinctrow YourName")
TOP n (for example, "Top 5 YourName")

followed by a space before the rest of the table name, you may receive the following error message when you run the query:
The Microsoft Jet database engine does not recognize 'YourName.*' as a valid field name or expression.

CAUSE

This behavior occurs because "ALL," "DISTINCT," "DISTINCTROW," and "TOP n" are Microsoft Jet SQL reserved words. Access interprets the SQL statement incorrectly, and does not initially recognize the reserved word as part of the table's name.

RESOLUTION

To resolve this problem, obtain Microsoft Office 2000 Service Release 1/1a (SR-1/SR-1a).

To obtain SR-1/SR-1a, click the article number below to view the article in the Microsoft Knowledge Base:

245025 OFF2000: How to Obtain and Install Microsoft Office 2000 Service Release 1/1a (SR-1/SR-1a)

To temporarily work around this problem, use one of the following methods.

Method 1: Save the Query in SQL View

Save the query in SQL view before running it. To save the query in SQL view, follow these steps:
  1. On the View menu, click SQL View.
  2. On the File menu, click Save.
The SQL statement should appear similar to the following example

SELECT [All YourName].* FROM [All YourName];

where YourName is the rest of the name of the table that is used in the query.

Method 2: Rename the Table

You can also rename the table and omit the reserved word, or combine it with the next part of the table name (for example, "AllYourName" or "All_YourName").

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was corrected in Microsoft Office 2000 SR-1/SR-1a.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Open a new blank Access database.
  2. Create a new table and save it as All MyName.
  3. Create a new query in Design view.
  4. In the Show Table dialog box, click All MyName, click Add, and then click Close.
  5. In the query design grid, double-click the asterisk (*) in the All MyName field list.
  6. Save the query as qryTest, and then run the query.

    Note that you receive the following error message:
    The Microsoft Jet database engine does not recognize 'MyName.*' as a valid field name or expression.

REFERENCES

For more information about Microsoft Jet SQL reserved words, click Microsoft Access Help on the Help menu, type sql reserved words in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type:MinorLast Reviewed:3/10/2003
Keywords:kbbug kberrmsg KB252740
©2002 Microsoft Corporation. All rights reserved.