Advise on Integration with FoxPro

I have a legacy system based in Fox that I need to generate reports from and I would like to use SQL Server to house some report optimized tables that are derived from the Fox data. I am currently looking at several strategies for manipulating and porting the data.

a. Linked Servers - haven't had much success linking to the OLD fox database (DOS version)
b. DTS - can't find a way around dropping and recreating all the tables every time.
c. Visual Basic App - most control but not sure if the best solution
d. Some Combo of the Above

Does anyone have any advice on what the best approach might be? Are there any other solutions to add to the mix?

Thanks for any assistance,

AllenAt this point of time, I think a good sollution can be to write a DLL in VB/VC++ which will communicate with the FOX data and return the resultset in XML format.

Then your actual app can parse through the XML document using XML DOM or SAX API and provide the data to SQL Server database. If your XML file is going to be big enough, consider using SAX API which is a light-weight alternative to XML DOM.|||I have it in production with win2000 server sp2 + sql server 2000 sp3
by using Microsoft OLEDB Provider for ODBC, so it can be done

I have problems when I want to upgrade to VFPOLEDB, and in the environment of win2003 + sql server 2000 sp3. Running OPENQUERY in QA is fine, but can't be run via SQL Agent job with the error msg

Could not initialize data source object of OLE DB provider 'VFPOLEDB'. [SQLSTATE 42000] (Error 7303) [SQLSTATE 01000] (Error 7312) OLE DB error trace [OLE/DB Provider 'VFPOLEDB' IDBInitialize::Initialize returned 0x80040e21]. [SQLSTATE 01000] (Error 7300). The step failed.