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 1

I think how this works really depends on the authentication issue and number of users that need to review the data.

The reason I ask?

You can consider using Access 2010 and office 365. This allows you to have linked tables to the cloud, but in fact the tables are also cached local to your Access desktop. This means that real time replication sync of data is used, and this is automatic for Access 2010 (so you don’t' have to write any code).

What this means is while running the Access desktop application, you can pull the plug on the network and it will continue to run. The instant you have a wifi or a connection, then changes local are synced up to office 365. Even better is you can now build web forms in Access.

Data touched or edited (or new records on either side) will come down the pipe to your local computer. So you add reords in Access client, the web users will ALSO see these new reocrds.

So Access 2010 now has web publishing, and this works with the new office 365. The price starts at $6 per month. And if just for a few users, then have them all logon using the same account! This means you can have this all up and running in less time than it took to make this post, and for less then $10 per month!

For those not aware, Access 2010 has web publishing. When you publish the Access forms, then are converted to .net forms (zammel/XAML) forms, and the code is converted to JavaScript. So form code actually runs browser side.

Since the system runs on office 365, then you using some heavy duty iron and you can in theory scale out to millions of users for this setup. When you publish the Access application to office 365, then on the server side you not using mdb or Access files anymore, but what is called Access Web Services. The tables in fact become the equilivant of SharePoint lists . And new for SP 2010 is those lists now have relational features like cascade delete.

The real beauty of this system is you can write and create and do everything inside of Access without have to learn or touch ANY KIND of server side technology. Here is short video of mine, and at the half way point I run the Access application with nothing more than a web browser.

http://www.youtube.com/watch?v=AU4mH0jPntI

There is no activeX or even Silverlight required. In fact my Access applications run fine on a iPad using the safari web browser.

So you could consider to continue using Access, and just publish your application to the web with the new Access 2010 features.

Answer 2

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.

Answer 3

well you can use 2 db and syncronize changes with a sort of web service between them. seperating web server Db (which you could use a modern mysql or whatever) and the current access Db You should build a sort of a Rest Api returning new or changed records against GET method, Deleting against DELETE method etc. using a timestamp in the http method. and then you could query at each side with a scheduled job for new records at the other side (transferring with json) resulting in keeping the records relatively insync.

You could take care of security with exposing the application db only in a certain port and only to http queries coming from the webapp server ip address. also using http auth, hashes etc.. if this isn't a heavy load, high concurrency app (which I guess it isn't since you use access as a Db) this should work.

you could build this kind of mini-api with any python webframework like turbogears 2.1,django or the mini frameworks like bottle or flask

p.s If you prefer python (and why wouldn't you) don't use pyodbc directly, work with python beautiful orm - sqlalchemy is much better