Unanswered: Need script/ideas for exporting data to .dbf files
I'm trying to integrate some systems together. We have some scheduling data in a commercial Paradox-based system. I need to access this data daily from a home-built Access97 solution that I'm building. I've fought the ODBC/link battle between Paradox and Access for several months, and am convinced that Access is too finicky about the file format and there is no way on earth, that I'll be able to succesfully link into the live data from Access. I can do so with a handful of the .db files, but mostly very small or empty ones. Access requires primary indexes to be the first and only the first field, chokes on memo fields, etc.
Now, I can manually copy and paste the data into any number of intermediate containers (eg. Excel, or dBaseIV tables created by Access, etc), but I need to be able to push a button in Paradox once every day and have it export the data from half a dozen tables to (for example) dBaseIV format files, overwriting the previous export files. I can then link into these external files from Access and write the code to process the data.
I've used Access97 extensively, but know next to nothing about Paradox. I have version 8. I don't want to spend half a year learning Paradox, in my limited spare time, just for this one need.
Can any kind souls suggest procedures, and point me to examples that I can tinker with to accomplish the above-stated goal?
Unless, a different approach is better, the script/macro should:
Export all data*in a specified Paradox table, using an unchanging path/name, (overwriting existing file if exists) to a dBaseIV format file.
(repeat with a different table, several times)
*For a couple of the tables, it would also be nice to be able to filter out records which have a certain date field whose value is less than the current date.
The goal of this project is take event scheduling data from ACS Ministry Scheduler and to automatically adjust the HVAC room schedules of Delta Controls devices, using BACNET protocol. I'll be using the SCADA engine in Access97 to write the new schedule data to the devices. Currently, I have to print a report from ACS and spend half a day manually adusting a week's worth of schedules using Delta's OrcaView front end pkg. Then a few days later, I reprint the same report, compare the old and new for changes, and then manually adjust the changes into the device schedules.
That's because Paradox exports to dBase IV intrinsically, courtesy of using the BDE. Just copy a table and make the name end with the extension .dbf instead of .db and it'll happen like magic. It works the other way around too.