ACC97: "ORDER BY Clause Is Not Valid..." Error with Linked Table

Article translations Article translations
Article ID: 235960 - View products that this article applies to.
This article was previously published under Q235960
Advanced: Requires expert coding, interoperability, and multiuser skills.

Expand all | Collapse all

On This Page

Symptoms

When you run a query that uses an ORDER BY statement on a linked table, you may receive the following error message:
The ORDER BY clause is not valid because column, "col name," is not part of the result table.

Cause

You are unable to use the ORDER BY statement on a non-Primary Key field. Microsoft Jet does not make the necessary ODBC call, SQLGetInfo(SQL_ORDER_BY_COLUMNS_IN_SELECT), to retrieve the information necessary to include ORDER BY columns in the SELECT statement.

When you run a query using Jet 3.5x, Jet obtains table indexes and issues a "pre-query" that selects all the indexed columns and retains the remaining part of the query issued by Access. For example, if the following query is issued from Access against a table called Test1 with fields A, B, C, D, E, and F, having a unique index on A, B, and C
Select D,E,F from Test1 order by D,E,F
the "pre query" would be:
Select A,B,C from Test1 order by D,E,F
This "pre-query" extends beyond the ANSI-92 standards and is rejected by some of the relational database management systems, such as DB2. Other relational database management systems, such as Microsoft SQL Server, ORACLE, and others, allow this "pre-query" to succeed. (Strict ANSI-92 compliance dictates that ORDER BY columns must also be in the SELECT list.)

Resolution

First, install the latest release of Microsoft Data Access Components MDAC 2.1.2.4202.3 (GA). You can download MDAC 2.1.2.4202.3 (GA) from the following Microsoft Web site:
Microsoft Universal Data Access (MDAC) Web page
Then, if you are running Microsoft Windows 95, Microsoft Windows 98, or and Microsoft Windows NT, install the Microsoft Jet 3.5 SP3 update. This is not necessary if you are running Microsoft Windows 2000. For additional information about how to obtain the Microsoft Jet 3.5 SP3 update, please click the article number below to view the article in the Microsoft Knowledge Base:
172733 ACC97: Updated Version of Microsoft Jet 3.5 Available for Download

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

More information

In addition to obtaining the Jet update listed in the "Resolution" section, if you are using the IBM DB2 OS/390 ODBC driver, you must also install an IBM Driver Patch (Fixpack 9). The IBM DB2 OS/390 ODBC driver version v5.1.2 incorrectly returns "N" instead of "Y" when responding to a call to SQLGetInfo(SQL_ORDER_BY_COLUMNS_IN_SELECT).

Steps to Reproduce Problem

  1. Click Start, point to Settings, and then click Control Panel.
  2. In Control Panel, double-click the ODBC Data Sources (32-bit) icon.
  3. In the ODBC Data Source Administrator dialog box, click the Tracing tab, and then click Start Tracing Now.
  4. Open Query Manager in SQL Server 7.0 or ISQL/w in SQL Server 6.x.
  5. To create a new table, run the following code:
    CREATE TABLE test1
    
        ( 
    
         Field1 char(4),
    
         Field2 char(4),
    
         Field3 char(4),
    
         Field4 char(4),
    
         Field5 char(4),
    
         Field6 char(4)
    
        )
    
    GO
    
    CREATE UNIQUE INDEX testCompIdx
    
        ON Test1 (Field1,Field2,Field3)
    
    GO
    
    INSERT INTO Test1 (Field1,Field2,Field3,Field4,Field5,Field6)
    
           VALUES ('A','B','C','D','E','F')
    					
  6. Open an Access 97 database and link a table to the SQL Server table that you created in step 4.
  7. Create a new query called Query1 in Design view without adding any tables.
  8. In the Query1 query, on the View menu, select SQL, and type the following:
    SELECT Field4,Field5,Field6
    FROM dbo_Test1
    ORDER BY Field4,Field5,Field6
  9. Save and run Query1.
  10. Open the ODBC trace file in Notepad to review the query. You see the following text:
    SELECT "dbo"."Test1"."Field1","dbo"."Test1"."Field2","dbo"."Test1"."Field3" FROM "dbo"."Test1" ORDER BY "Field4" ,"Field5" ,"Field6"
  11. Close the database and delete the ODBC trace log.

Properties

Article ID: 235960 - Last Review: July 18, 2012 - Revision: 6.0
Applies to
  • Microsoft Access 97 Standard Edition
Keywords: 
kbbug kbfix KB235960
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com