Using MS Access & ODBC to connect to a remote PostgreSQL

Question

I currently have an MS Access application that connects to a PostgreSQL database via ODBC. This successfully runs on a LAN with 20 users (each running their own version of Access). Now I am thinking through some disaster recovery scenarios, and it seems that a quick and easy method of protecting the data is to use log shipping to create a warm-standby.

This lead me to think about putting this warm-standby at a remote location, but then I have the question:

Is Access connecting to a remote database via ODBC usable? I.e. the remote database is maybe in the same country with ok ping times and I have a 1mbit SDSL line.

Answer 1

Yes.

I don't have any experience using Access to hit PostgreSQL from a remote location but I have successfully used Access as a front-end to SQL Server & DB2 from a remote location with success.

Ironically, what you don't want to do is use Access to front-end an Access database (mdb) from a remote location over a high-latency link. Since hitting the MDB uses file-based operations it's pretty easy to end up with a corrupt database if you have anything more than a trivial db.

Answer 2

It depends a lot on the database you're using as a back-end. I've had rather terrible experiences with MySQL as a back-end. Make sure the ODBC link you're using is actively developed, stable and complete --- this was definitely not the case for MySQL. You may also want to check for any compatibility issues between Access and Postgre. And, of course, it won't hurt to test extensively.

Oh, and I think it'd be absolutely great if you could post back here later with your experiences!

Answer 3

onnodb,

The PostgreSQL ODBC driver is actively developed and an Access front-end combined with PostgreSQL server, in my opinion makes a great option on a LAN for rapid development. I have been involved in a reasonably big system (100+ PostgreSQL tables, 200+ Access forms, 1000+ Access queries & reports) and it has run excellently for a few years, with ~20 users. Any queries running slow because Access is doing something stupid can generally just be solved by using views, and any really data-intensive code can easily be moved into PostgreSQL functions and then called from Access.

The only main ODBC-related issue we have is that there is no way to kill a slow running query from Access, so we do often get users just killing Access and then massive queries are just left executing on the server.

Answer 4

PostgreSQL works great as a backend for MS Access, there are a couple of support functions you should use to make things easier. See here for more info on this:

http://www.amsoftwaredesign.com/smf/index.php?board=8.0