Innovative Database Solutions Inc
Tableupdate Connectivity Error w VFP ODBC Driver
Save to folder
I am trying to write a data conversion program that moves data from a large free table to three, better normalized tables in a VFP dbc. Originally, I wrote the program to convert data directly from the old tables to the new -- and that worked fine. But since the application we are writing will support both client server and VFP backends, I am trying to update the conversion program to use remote views and the VFP ODBC connection to the new tables.
The destination tables are empty when the process begins. To speed up the testing, I limited the conversion to only the first 500 source records. And I got the thing to work just file. But when I populated the source data files with "Live" data (approx. 20,000 records), I get the error:
Connectivity error:[Microsoft][ODBC Visual FoxPro Driver] Variable Q121P39 is not found.
This happens on the last tableupdate command issued (the 2 other updates work fine). The table with the problems is the largest of the 3 (88 columns [fields] - 443 bytes per record). All the views mirror the destination table, the primary keys are set, and I am using a shared connection for all three views. I am using the VFP ODBC 6.0 and I have installed the "missing" resource file.
I have tried everything I can think of and search UT and MS Knowledgebase -- but have not found the awnser yet.
I have tried both opt. record buffering with the transactions property set to automatic and and opt. table buffering with the transactions property set to manual. With the table buffering, I wrapped the tableupdate with the begin transaction-rollback-end transaction and used the SQLcommit and sqlrollback. Again, if I only update the 1st two tables, everything works great.
I have recreated the views and the connection. I have tried pointing the connection to a different copy of the destination tables. I've packed and reindexed the source table. I always get the same result.
When I used opt. row buffering, I was able to see which source record the "automatic" tableupdate failed on. It always happens on record 637. There is nothing wrong with the record. If I delete it, it just fails on the next record. If I delete the first 10 records (that successfully updated), then the failure occurs 10 records further down the list -- always at record 637.
Right now, I am going to try using offline views to see if I can get around this problem. But if you have any answers, I would love to hear from you.