Microsoft KB Archive/129074

{|
 * width="100%"|

MXL5: Using FoxPro Index Files with Microsoft Query

 * }

-

The information in this article applies to:


 * Microsoft Excel for the Macintosh, versions 5.0, 5.0a
 * Microsoft Query for the Macintosh, version 1.0

-

SYMPTOMS
When you access data from a FoxPro 2.5 or 2.6 data source in Microsoft Query or from a Microsoft Excel 5.0 Visual Basic for Applications macro, the query may seem slow even if an index for the table is being used.

You may also fail to receive any results when you use the SQLRequest and SQLRetrieve functions in a macro or the SQL.REQUEST function on a worksheet to access a FoxPro data source. If you are using SQLRequest or SQLRetrieve in a macro and SQLError is used to trap ODBC errors, you may be able to trap and generate the following error message

[Q+E Software][ODBC dBase driver][dBase][Production MDX or CDX file not found for table: :

where is the path to the .DBF file and is the name of the .DBF file being accessed.

CAUSE
In Microsoft FoxPro 2.5 or 2.6, indexes are created as tag names that are stored in a single .CDX file. The .CDX file has the same base name as the database file (for example, ORDERS.CDX is the index file for the ORDERS.DBF database). Microsoft FoxPro uses index files to ensure faster querying and sorting of database tables.

If the .CDX file is not located in the same directory as the .DBF file, you will receive an Error 2042 (#N/A) when retrieving data using the SQLRequest, SQLRetrieve, or SQL.REQUEST functions. This error occurs because the .DBF file header created by FoxPro contains a pointer to the .CDX file. If the .CDX file isn't in the same directory, it will cause the error message mentioned above.

RESOLUTION
To resolve this error, make sure that an index file has been created for the .DBF file in question and that the index file is located in the same folder as the .DBF file.

STATUS
Microsoft has confirmed this to be a problem in the versions of Microsoft Excel listed above. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION
The Q+E and Microsoft FoxPro Driver creates a file called QEDBF.INI. This file contains information about the index files for each table and is located in the same folder as the .DBF file.

An index is only useful when a WHERE clause is being used in a SELECT statement. For example, the following SQL statement does not use an index to speed up the query:

"SELECT * FROM orders" However, you can modify this statement as shown below to take advantage of using an index file:

"SELECT * FROM orders WHERE (order_id='10000')" If the index file uses an index based on the ORDER_ID field, then you will see a marked improvement in the speed of the query.