Using MS Access and ODBC to connect to a remote PostgreSQL

Question

I currently have an MS Access application that connects to a PostgreSQL database through ODBC. This successfully works on a local network with 20 users (each of which launches its own version of Access). Now I'm thinking about some disaster recovery scenarios, and it seems like a quick and easy way to protect data is to use the delivery log to create a warm wait.

This made me think about putting this warm mode to a remote location, but then I had a question:

Is remote database access available through ODBC? That is, the remote database may be in the same country with the usual ping time, and I have a 1mbit SDSL line.

Answer 1

onnodb,

The PostgreSQL ODBC driver is actively developing , and the Front Access interface in combination with the PostgreSQL server, in my opinion, makes an excellent option on the local network for fast development. I participated in a fairly large system (100+ PostgreSQL tables, 200+ access forms, 1000+ Access queries and reports), and it has been working fine for several years with ~ 20 users. Any queries that are slow because Access does something stupid can usually be resolved with views , and any really intense code in the data can be easily moved to PostgreSQL and then called from Access.

The only ODBC problem with ODBC is that there is no way to kill a slow request from Access, so we often force users to just kill Access, and then massive requests are simply executed on the server.

Answer 2

Yes.

I have no experience using Access to access PostgreSQL from a remote location, but I have successfully used Access as an interface for SQL Server and DB2 from a remote location.

Ironically, you do not want to use Access to access the Access database (mdb) from a remote location using a high latency link. Since clicking on MDB uses file operations, it is quite easy to get a damaged database if you have something more than a trivial db.

Answer 3

It depends on the database you use as background. I had pretty terrible experience working with MySQL as source code . Make sure that the ODBC link you are using is actively developing, stable and complete. This was not the case for MySQL. You can also check for compatibility issues between Access and Postgre. And, of course, it does not hurt to experience extensively.

Oh, and I think it would be great if you could come back here later with your impressions!

Answer 4

PostgreSQL works fine as a server for MS Access, there are several support functions that you should use to simplify the work. See here for more information on this:

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