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

ID: Q235960

The information in this article applies to:

Microsoft Access 97

Advanced: Requires expert coding, interoperability, and multiuser skills.

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:

http://www.microsoft.com/data

Then, install the Microsoft Jet 3.5 SP3 update. 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:

Q172733 ACC97: Updated Version of Microsoft Jet 3.5 Available for Download

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products 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).

You can obtain the IBM Driver Patch (Fixpack 9) from the IBM FTP site at the following address:

ftp://ftp.software.ibm.com/ps/products/db2/fixes/english-us/db2ntv5/FP9_WR21113/

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.

Additional query words: pra

Keywords : kbdta
Version : WINDOWS:97
Platform : WINDOWS
Issue type : kbbug