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.
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.
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,Fthe "pre query" would be:
Select A,B,C from Test1 order by D,E,FThis "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.)
First, install the latest release of Microsoft Data Access Components MDAC 22.214.171.12402.3 (GA). You can download MDAC 126.96.36.19902.3 (GA) from the following Microsoft Web site:
Microsoft Universal Data Access (MDAC) Web pageThen, 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:
(http://support.microsoft.com/kb/172733/EN-US/ )ACC97: Updated Version of Microsoft Jet 3.5 Available for Download
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.
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
Article ID: 235960 - Last Review: July 18, 2012 - Revision: 6.0