This is a shot in the dark but maybe someone has had a similar problem.
I have a table in Fox Pro with 600,000 records in it. It is not mine and I cannot change the design of this table. In access I have some code that selects everything (i.e. SELECT * FROM tblFoxPro from this table. My code successfully loops through all 600,000 records. but not all get into the destination table. say 599950 records make it in. I don't get any error messages regarding the 50 records. The strange thing is when i rerun the code again, a few more records make it into the destination table. Anyone have any ideas on this one?
The client wants to keep a historical record of all transactions over time. The records in the fox pro system are purged every once in a while. As a result the historical requirement cannot be satisfied by that table. Thus we do not use it directly. Also as an aside we cannot modify that system due to legal considerations.
On occasion records do not come across for reasons apparently of their own, have you run an unmatched query yet. I have found in the past that by running an unmatched query a pattern emerges of the records that won't transfer.
You are soo right. Yes i've tried the unmatched query. To my horror the table actually broke the query engine in access. Something in the data is preventing the unmatched query from running. I discovered this morning that the date field in the destination transaction table which is set to SmallDateTime does not properyly accept the seconds in a date from fox pro. I.e.
FOX PRO --> SQL Server (linked table)
1/1/2000 12:01:59 1/1/2000 12:01:00 ' Small DateTime
1/1/2000 12:01:59 1/1/2000 12:01:59 ' DateTime
Since the destination table uses that date field in its PK I believe that's what is causing it though that still doesn't explain why 1-2 records creep in when i rerun. I've since changed that date type to "DateTime" anyhow it i'm currently rerunning the utility program. Fingers are crossed.
Just finished running the utility. Yep it was the Data types. the dates foxpro date field does not translate into SQL server Small Date/time fields properly. Also learned something else new. If you're updating a SQL Server table with a large number of records it helps to index the fields that you'll be searching on otherwise there is a chance it will error out with a generic "Error executing SQL string" in Access.