ACC97: Inconsistent Results Are Returned from a Query That Is Based on Linked Visual FoxPro Tables

Some queries that are based on one or more linked ODBC Microsoft Visual FoxPro tables may return inconsistent results each time that you run the queries. Examples of queries that might exhibit this behavior are queries that are based on one or more linked Visual FoxPro tables with joins, or queries that are based on one or more linked Visual FoxPro tables that perform calculations.

This problem can occur if you linked the Visual FoxPro table or tables without specifying a unique record identifier and the Fetch data in background check box is selected within the DSN.

NOTE: The Fetch data in background check box is selected by default.

You can resolve this issue by either clicking to clear the Fetch data in background check box or by selecting a unique record identifier when you link the ODBC table.

To clear the Fetch data in background check box, follow these steps.

NOTE: Because there are several versions of Microsoft Windows, the following steps may be different on your computer. If they are, see your product documentation to complete these steps.

1. Click Start, point to Settings, click Control Panel, and then double-click the ODBC Data Sources (32bit) icon.
2. Select your DSN that uses the Microsoft Visual FoxPro driver, and then click Configure.
3. In the ODBC Visual FoxPro Setup window, click Options.
4. Click to clear the Fetch data in background check box.
5. Click OK twice to close the ODBC Administrator.
6. Open your Access database.
7. Delete and re-create the links to your Visual FoxPro tables.

IMPORTANT: Do not use the Link Table Manager because the Link Table Manager does not refresh the links to the tables.

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

Steps to Reproduce the Behavior

1. Click Start, point to Settings, click Control Panel, and then double-click the ODBC Data Sources (32bit) icon.
2. Under the System DSN tab, add a data source that uses the Microsoft Visual FoxPro Driver.
3. Type OrderDetails for Data Source Name.
4. Under Database type, click Free Table directory
5. Type C:\My Documents for the path, and then click OK.
   
   NOTE: This step assumes My Documents is the default database folder.
6. Open the sample database Northwind.mdb.
7. Export the Order Details table by using the OrderDetails DSN that you created earlier.
8. In the Export dialog box, type Order Details.
9. Rename the Order Details table that is in Northwind.mdb.
10. On the File menu, point to Get External Data, and then click Link Tables.
11. In the Files of type box, click ODBC Databases.
12. Select the OrderDetails DSN that you created earlier, and then click OK.
13. Select the Order Details table, and then click OK.
14. In the Select Unique Identifier box, do not select any fields, and then click OK.
15. Create a new query in Design view, click SQL View on the View menu, and then copy and paste the following SQL statement.
    
    SELECT Sum([Quarterly Orders by Product].[Qtr 1]) AS [SumOfQtr 1], Sum([Quarterly Orders by Product].[Qtr 2]) AS [SumOfQtr 2], Sum([Quarterly Orders by Product].[Qtr 3]) AS [SumOfQtr 3], Sum([Quarterly Orders by Product].[Qtr 4]) AS [SumOfQtr 4] FROM [Quarterly Orders by Product];
16. Save and run the query several times and compare the results.