I'm looking to build an application that will get data
from an HR system (built in Visual FoxPro 7.0), import it
into SQL Server, and process it there. I'm trying to
figure out what the best approach to doing this would be.
I would like to avoid any development in the FoxPro db -
no views, no stored procedures, no db objects. Here's what
I'm thinking; let me know if this is feasible, and where I
can find the resources/how-to's to make it happen:

1. Replicate the FoxPro tables into MSS. I assume that
should be possible, given that there's an odbc driver for
Visual FoxPro. What are the pitfalls in this? Any issues
with the standard ODBC driver?

2. Programmatically extract the data from FoxPro (I'd be
using java and the jdbc/odbc bridge to do this), then
insert into MSS (via jdbc/odbc). This seems cumbersome,
but was recommended to me by someone who's worked with a
similar situation and said he couldn't get 1. to work.

Are there other options? Anything I'm missing? Is there a
handy document on the net that would be a good start as a
how-to?

Hi Andreas,

Over the years new features have been added to FoxPro tables. Tables using
features that were added in VFP7 and above are not accessible via ODBC (they
are accessible via OLE DB). However, just because an application was written
with VFP7 (or above) doesn't mean that the new data table features have been
used. To determine whether ODBC will work (that no post-VFP6 features have
been used), try something simple like linking the tables to an Access
database via ODBC.


--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@msn.com www.cindywinegarden.com


[quoted text, click to view]

Cindy,
thanks for your reply. Here's where I am at this point:
I've found that there are some tables that are indeed not
compatible with ODBC. My predecessor (whose app I'll be
replacing) used a VB script to get those copied and saved
in a previous version. Not sure how he did that without
any loss of information, but apparently there's a VB
method that does that.

I was hoping to just go the OLE DB route and get the
information into SQL Server - seems like that will be a
feasible route. Would you recommend setting the FoxPro
tables up as linked tables in MSS?

More pressing for me at this point, however, is another
issue - is there a user friendly query client for Visual
FoxPro 7? I'd love it if I could use something similar to
Query Analyzer in MSS2000 that has an object browser so I
can do ad hoc queries and get more familiar with the
database, plus be able to actually test the sql I'll be
using to retrieve data in real time. Anything like that
available? The query functionality in Visual FoxPro 7
seems to require that one build a project first - seems
like a lot of overhead. Am I overlooking something there?

Thanks,
Andreas


[quoted text, click to view]