MXL5: Using FoxPro Index Files with Microsoft Query

ID: Q129074

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: <path>:<table name>

where <path> is the path to the .DBF file and <table name> 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.

REFERENCES

For more information on FoxPro index files, search for "indexes" in the Q+E ODBC FoxPro Driver Help file located in the Microsoft folder.

Additional query words: 5.0 long time

Keywords : kbinterop xlquery xlmac
Version : MACINTOSH:1.0,5.0,5.0a
Platform : MACINTOSH
Issue type : kbbug