Solution on MS Access and Informix Sorting

Miles Roper · 2001-02-13T01:59:49Z

Hi All, Here after 2 years we finally have a solution with MS Access and infromix sorting problems. This is a reference from a technet cd from mirosoft. I posted this problem a while back but didn't get a solution. After some more digging we found this below. The fixes work. PSS ID Number: Q235960 Article last modified on 11-04-2000 WINDOWS:97 ====================================================================== ------------------------------------------------------------------------------- The information in this article applies to: - Microsoft Access 97 ------------------------------------------------------------------------------- Advanced: Requires expert coding, interoperability, and multiuser skills. SYMPTOMS ======== When you run a query…

Posted in 2001

Miles Roper — Tue, 13 Feb 2001 14:59:49 +1300 — source: Informix-list mailing list archive (1991-1998)

<!doctype html public "-//w3c//dtd html 4.0 transitional//en"> <html> Hi All, Here after 2 years we finally have a solution with MS Access and infromix sorting problems.  This is a reference from a technet cd from mirosoft.  I posted this problem a while back but didn't get a solution.  After some more digging we found this below.  The fixes work.   PSS ID Number: Q235960 Article last modified on 11-04-2000   WINDOWS:97         ====================================================================== ------------------------------------------------------------------------------- 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:   <A HREF="http://www.microsoft.com/data">http://www.microsoft.com/data</A>;   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:   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:   <A HREF="ftp://ftp.software.ibm.com/ps/products/db2/fixes/english-us/db2ntv5/FP9_WR21113/">ftp://ftp.software.ibm.com/ps/products/db2/fixes/english-us/db2ntv5/FP9_WR21113/</A>   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.   Additional query words: pra   ====================================================================== Keywords : kbdta Version : WINDOWS:97 Issue type : kbbug Solution Type : kbfix ============================================================================= Copyright Microsoft Corporation 2000.    </html>