>Have you considered just giving up on the VFP driver and going to SQL?
Yes, I have tested that. The SQLClient data provider is extremely fast. Based on my results, it drives at about 3 times faster. This is when I migrated some of the data temporary to SQL Server. But, I am not ready to go to SQL Server because of the infrastructure I would have to support in term of maintenance and cost. I neither have one of those available presently.
>If you plan on having a high volume application like this, the VFP OleDb provider is just not the right choice. It never has been - heck it took and VFP 9 to even get a multithreaded driver and apparently that driver has issues now...
I agree.
>The only reason why you wouldn't use SQL Server is cost and given the time you're pouring into trying to make the VFP OleDb driver work I doubt that the cost of SQL Server is going to be an issue, especially given no matter what you do you will never be able to fully administer the data using the VFP OleDb driver entrirely (Index, Pack, etc are difficult or impossible to do especially on a live database plus the whole hell of DELETEd records in DBF files).
I succeeded from the application administration interface to implement everything necessary to recreate the tags and pack. For most of those commands, it goes through ExecScript().
>I realize that you think this should just work and I agree. But the driver has never worked well and I would never trust it for anything but small applications... And even then it's such a throw back compared to the integrated SQL support in VS.NET and the SQL Tools.
Some issues I would have to get more knowledge in SQL Server presently are:
1. Auto increment primary key field - when I built the package, this came up as regular field and it didn't work quite well when I tried to insert record when the data was SQL Server
2. The issue with the date I have which goes centuries in the back. I would have to split the date time field into a date time field + an integer field to be able to support dates such as April 1st, 1460.
3. Overall knowledge of data maintenance, doing backups, recreate tags, etc.
Then, to that, I have an issue with the licensing issues. Some recommended to go with the Express version. I am really not sure about that. I also have two CPUs. So, this would mean it would cost me double as far as licensing goes.