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

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:

http://msdn.microsoft.com/dataaccess

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

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

Click Start, point to Settings, and then click Control Panel.
In Control Panel, double-click the ODBC Data Sources (32-bit) icon.
In the ODBC Data Source Administrator dialog box, click the Tracing tab, and then click Start Tracing Now.
Open Query Manager in SQL Server 7.0 or ISQL/w in SQL Server 6.x.

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')

Open an Access 97 database and link a table to the SQL Server table that you created in step 4.
Create a new query called Query1 in Design view without adding any tables.
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
Save and run Query1.
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"
Close the database and delete the ODBC trace log.