How to update a remote ms access database?
Question: i need to create a webapp to show and allow editing for a set of data.

This data is contained in an Access Database file, used by another application
(a desktop application).

I'm evaluating the best way to carry out this job.

Unfortunatly my purpose to migrate to another database solution (rdbms such as
MySQL or Postgres) was rejected by the customer.

The issue here is how to keep data integrity and syncronized between the
server and the desktop that executes the application that also uses this data.

All I need to do is, read data, store edited or new data, give to authorized
users an interface to review this new inserted data -thus validating it-, and
import this to the original access database.

I've found the following possible solutions (to update the desktop mdb copy),
but each of them has pros and cons:

  * remote access to the windows machine 
    * exposes the machine to unauthorized access
  * use rsync to keep files syncronized (once a day) 
    * if the mdb on the client has been edited with the desktop application there will be data loss
    * can be update only when all data has been validated
    * there won't be real syncronized data (until rsync will run)
  * client-server applications 
    * can use secure layers to protect data against attackers
    * a 3rd application (on the desktop) is required
    * syncronization requires authorized users to use this 3rd application to import data (that will query the remote db and update the local mdb)

Do you know some other way that could help me to get this done? I'm oriented
on the client-server model, also if this would be more expensive, but it's the
only way I see to make this work.

Do you see some other pros/cons of the purposed solution?

I didn't choose the PL to develop this, but I was thinking to use either PHP
and/or Python. The remote environment (for the server) can either be Windows
or *nix (preferred).

Thanks.


Answer: The first idea:

>
>     exposes the machine to unauthorized access
>  

This is not really a valid argument. Everything you put on the Internet is
exposed. An it is not like it cannot be further protected via SSL/TLS. Even
RDP can be secured via a SSH tunnel, for example.

To my mind, the easiest way and most elegant way to do is by using web
services (SOAP). Write the server code that does inserts/updates on the Access
database with something like a Python or Java. Generate a WSDL from the
working code. From the WSDL you can generate a client for PHP/Python. Now all
you have to do is to write the web interface that uses the PHP/Python client.

For security using SSL and Basic authentication should be enough (supported by
SOAPpy in the case of Python, for example). You can use `pyodbc` to connect to
the Access database.

