Querying Against Foxpro DB Yields Different Results

[Cheryl]

I've done a make-table query to convert a large amount of data from a single
table, Visual Foxpro database, and now want to validate that the data came in
correctly. The Foxpro table has 45000 rows and 250 cols. However, each time
I do a count of records that may be different in the 2 tables (new and old),
my query yields different results, anywhere from 5-20 records, even if I
select only the identifier (long integer) field in both tables. If I also
include any other field, along witih the corresponding field from the new
table, it will show that the new fields contain different data for the same
ID number. Yet when I look at the tables themselves, they are correct.

Is this a bug?

 

[Allen Browne]

Several things could be going on here.

The table you are importing into may have rules that prevent all records
being imported. Details in:
Why can't I append some records? Trouble-shooting imports
at:
http://allenbrowne.com/casu-19.html

What is the data type of the ID field?
- If Number, the Field Size property matters. For example, a Double or
Single may not match properly when you compare fields.

- If Text, Access is case in-sensitive. It may strip trailing spaces. There
could be spurious (not visible) characters in the field.) And a zero-length
string is not the same as a Null.

Use the Unmatched Query wizard to identify the records in one table that are
not in the other, then use Len(), Asc(), Right(), etc to see if you can
identify the differences.

If you suspect a problem in either db, use repair or pack in Fox before the
import, and use Compact/Repair in Access afterwards (Tools | Database
Utilities.)

It might depend on what you are doing with the query if you say the tables
are correct. See:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

 

[Gary Walter]

I've done a make-table query to convert a large amount of data from a
single
table, Visual Foxpro database, and now want to validate that the data came
in
correctly. The Foxpro table has 45000 rows and 250 cols. However, each
time
I do a count of records that may be different in the 2 tables (new and
old),
my query yields different results, anywhere from 5-20 records, even if I
select only the identifier (long integer) field in both tables. If I also
include any other field, along witih the corresponding field from the new
table, it will show that the new fields contain different data for the
same
ID number. Yet when I look at the tables themselves, they are correct.

Is this a bug?

Hi Cheryl,

In addition to Allen's sage help, try setting the ODBC setting
"BackgroundFetch" to "No" for your Foxpro DSN.

This particular problem goes all the way back to A97...

http://support.microsoft.com/default.aspx?scid=kb;en-us;252666&Product=mdac

<quote>
Click Start, point to Settings, click Control Panel,
and then double-click the ODBC Data Sources (32bit) icon.

Select your DSN that uses the Microsoft Visual FoxPro driver,
and then click Configure.

In the ODBC Visual FoxPro Setup window, click Options.

*******************
Click to clear the Fetch data in background check box.
*******************

Click OK twice to close the ODBC Administrator.

Open your Access database.

Delete and re-create the links to your Visual FoxPro tables.

<unquote>

good luck,

gary