Need design approach for collecting data from multiple remote databases

[Bob Alston]

Looking for design approach ideas for collecting data from multiple
remote, standalone databases. I have built one of these in Access.
Another I am told was built in Access. some others the language and
database is unknown.

These systems are all in small agencies which provide utility assistance
to people who are having trouble paying their utility bills.

A foundation wants to provide funds. However they want a consolidated
database of all services provided. And they want it EVERY NIGHT.
Apparently not negotiable. Also the process must work WITHOUT HUMAN
INTERVENTION ON THE FOUNDATION END.

the individual agencies, mostly very small aren't willing to duplicate
enter the data and they all want to use their existing systems as they
provide food, clothing and other kinds of assistance.

clearly I could build a process to extract data and reformat it as
required from the Access databases.

I could build an automated FTP process to send the data.

On the receiving side, I could build a process to monitor the directory
into which the FTP files are placed and grab the files and load the
database.

Can anyone suggest an alternate approach, preferably much improved???

Bob

[DTecMeister]

On Aug 30, 4:38 pm, Bob Alston <[e-mail]:

Looking for design approach ideas for collecting data from multiple
remote, standalone databases. I have built one of these in Access.
Another I am told was built in Access. some others the language and
database is unknown.

These systems are all in small agencies which provide utility assistance
to people who are having trouble paying their utility bills.

A foundation wants to provide funds. However they want a consolidated
database of all services provided. And they want it EVERY NIGHT.
Apparently not negotiable. Also the process must work WITHOUT HUMAN
INTERVENTION ON THE FOUNDATION END.

the individual agencies, mostly very small aren't willing to duplicate
enter the data and they all want to use their existing systems as they
provide food, clothing and other kinds of assistance.

clearly I could build a process to extract data and reformat it as
required from the Access databases.

I could build an automated FTP process to send the data.

On the receiving side, I could build a process to monitor the directory
into which the FTP files are placed and grab the files and load the
database.

Can anyone suggest an alternate approach, preferably much improved???

Bob

You want to add a process that runs at specified times to verify all
the expected files are in place. This way you can notify the
responsible parties that you're missing their file for the day. This
way misses can be corrected as soon as possible.

You also want to do a record count on the import and verify data is
properly formed as part of that process.

You may think of changing them all to a mysql back-end using linked
tables, but that's another story.