MXL5: Using FoxPro Index Files with Microsoft Query |
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
where <path> is the path to the .DBF file and <table name> is the name of the .DBF file being accessed.[Q+E Software][ODBC dBase driver][dBase][Production MDX or CDX file not found for table: <path>:<table name>
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.
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.
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.
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 ordersHowever, 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.
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
Technology :
|
Last Reviewed: November 2, 1999 © 2000 Microsoft Corporation. All rights reserved. Terms of Use. |