A bit of background first.
We have an existing FMPro application that is being replaced by a web app. The Web app is using MS SQL 2000 as the database. We've made significant changes to the database structure on the new app compared to the FMPro version.
I've been asked to try to import some of the data from the FMPro version. Normally that wouldn't be a problem going from one DB to another, however, the FMPro version is a stand alone app. We have roughly 1200 instances of the application that I have to import data from. There is a fair bit of validation that has to go on before I can import it.
My thought was to create a small VB application to the import for me. To complicate it, I can't trust that end users can export their data to a tab, or csv file, so I was planning on just getting their fp5 file, and getting the data I need through ODBC. Each fp5 file is named the same thing. Again to simplify it for the end user, I was going to instruct them to simply copy it to their personal drive on the network share and I'll grab it from there.
90% of the VB is a nobrainer for me.
Inserting the data into SQL is a nobrainer for me.
My problem is figuring out how to make an ODBC connection to the FMPro part to get the data. I've seen some examples of FMPro going to SQL to get data, but nothing the other way around.
How does one make a file dsn to an FMPro database to get data out?
Ideally I want to throw all the files into a directory structure, iterate through them, validate the data and stick it into SQL. Going from Access to SQL this way is as simple as spilling my coffee down the front of my shirt. I'm not an FMPro person so I'm having trouble getting my head around how FMPro does anything (except crash constantly).
"Every day is like a precious gift, you have to make it count" James Birrell 1993-2001
We have an existing FMPro application that is being replaced by a web app. The Web app is using MS SQL 2000 as the database. We've made significant changes to the database structure on the new app compared to the FMPro version.
I've been asked to try to import some of the data from the FMPro version. Normally that wouldn't be a problem going from one DB to another, however, the FMPro version is a stand alone app. We have roughly 1200 instances of the application that I have to import data from. There is a fair bit of validation that has to go on before I can import it.
My thought was to create a small VB application to the import for me. To complicate it, I can't trust that end users can export their data to a tab, or csv file, so I was planning on just getting their fp5 file, and getting the data I need through ODBC. Each fp5 file is named the same thing. Again to simplify it for the end user, I was going to instruct them to simply copy it to their personal drive on the network share and I'll grab it from there.
90% of the VB is a nobrainer for me.
Inserting the data into SQL is a nobrainer for me.
My problem is figuring out how to make an ODBC connection to the FMPro part to get the data. I've seen some examples of FMPro going to SQL to get data, but nothing the other way around.
How does one make a file dsn to an FMPro database to get data out?
Ideally I want to throw all the files into a directory structure, iterate through them, validate the data and stick it into SQL. Going from Access to SQL this way is as simple as spilling my coffee down the front of my shirt. I'm not an FMPro person so I'm having trouble getting my head around how FMPro does anything (except crash constantly).
"Every day is like a precious gift, you have to make it count" James Birrell 1993-2001