[consulting] Synchronizing data between a desktop database application and Drupal/mysql

[Jason Flatt]

On Thursday 15 December 2005 09:31, Steve Dondley wrote:
> I've got a client who uses a custom software solution based on FoxPro
> for tracking job trainees.  In the future, he wants to integrate and
> share this data with his web site.  For example, a trainee might visit
> the site and register for a class there.  He'd like to have his local
> database reflect this new applicant.  Or, he might want to create a
> new class in his database locally and have it listed on the site.
>
> A sister organization to my client's was able to achieve this for
> about $7K using ASP.NET.  I'd like to be able to offer an open source
> solution and have it integrated with the Drupal site I'm moving them
> to.
>
> I've done only minimal work synchronizing a proprietary front end
> database program (Microsoft Access) with a mysql database.  This is
> not an area I'm familiar with.  So first I'm wondering if someone
> could comment of the feasability of using FoxPro as a front end to a remote
> mysql database.  Second, I'd be interested to know if anyone
> is interested in being a subcontractor or if anyone knows someone I should
> get in touch with.  Note that this could lead to more work for other sister
> organizations of my client.

Though you didn't really specify the number of computers involved and what 
O/S(s) are running on them, I'll assume at least two PCs, one with Windows 
and one with something else (could be Windows or Linux).  Also, I'm assuming 
that your client created the FoxPro database himself, or had someone do it 
for him (but I could be totally wrong on that point).  Does he have access to 
the inner core of the FoxPro database application?

It's been about 6 yrs since I looked at FoxPro, but IIRC, it will attach to an 
ODBC database, just fine.  If that MySQL server is running on the same PC or 
another PC running Windows, It should be no big deal to setup an ODBC 
connection to it on the FoxPro PC, and then access the tables accordingly.

If that MySQL server is running on Linux, however, it may not be as easy to 
access.  Having never tried that, I would have to play around with it myself, 
before I could even speculate, but I'd still try to do it through ODBC.

IOW:
Assumption 1a: FoxPro database is open source.
Assumption 2a: All OSs involved are MS Windows.
Use ODBC to allow the FoxPro database access to the MySQl database.
Assumption 2b: All OSs involved are not MS Windows.
ODBC might still be an option, but experimentation would be necessary.
Assumption 1b: FoxPro database is closed source.
Need a 3rd program/database/service (FoxPro, OOo Base, MS Access, VB, Gordon's 
solution, etc.) to interact between the two databases.

How attached to that FoxPro database are they?  I think (w/o knowing anything 
about it, of course) I would be inclined to try to get the same functionality 
going in either a custom Drupal module or straight HTML/PHP forms that mimics 
the FoxPro database and functionality.  That way all the data can be in a 
single database server, even if they are in two separate MySQL databases.

-- 
Jason Flatt
http://www.oadae.net/
jason at oadae.net