Home / server / Questions / 2360
Accepted
Guy C
Asked: 2009-05-02 05:18:09 +0800 CST

Is Access connecting to a remote Postgresql database via ODBC usable?

  • 772

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.

microsoft-access postgresql odbc

3 Answers

  1. Best Answer

    Short answer: Yes.

    Long answer: If you are returning smaller data sets, yes. You'll also need to enable specific flags and settings in the pgodbc driver as Access will barf from what is fed back to it from the server:

    KSQO (Keyset Query Optimization) should be on. Yes, I know the docs say it's not required, but what's happening is that MSAccess is passing the server some rather knarly looking SQL. This just de-clutters the SQL statement a wee bit. Don't believe me? Turn on Logging for ODBC, send a single query, turn off the log, plow through several megs of chaff, and 90% of the way down you'll see a nasty looking SQL statement that was generated and passed. No, it's not the ODBC driver, it's Access doing that.

    Use Declare/Fetch should be on if you plan to interactively look at a large data set. This will cause the driver to fetch smaller chunks of your result set, instead of dumping one monster load into it.

    Text as LongVarChar - you may wish to test this. I suspect you will want it "on".

    Unknown Sizes should be set to Maximum.

    Max Varchar is recommended to be at 254 or less, although you can make it more.

    Extra Opts - set to 0x6 (Fake MS SQL Server + Reply in ANSI not Unicode).

    (all of these can be found at http://psqlodbc.projects.postgresql.org/config.html)

    Keep in mind that large data sets will cause you pain, despite the fact that the data returned from the server is generally in a compact binary format.

    • 4

  2. My experience with MSA and WAN databases has never been that great. There has always been some moment where Access decides to pull a lot of data off the database. The two ways I have been successful is either to put the Access front-end on the remote server as well and use Terminal Server. Or to use SQL Passthrough queries so that you are explicitly in control of what passes to/from the database server.

    • 1

  3. For an Access app to work well in this scenario, it has to be architected to retrieve the mininum data, and likely would work best with unbound forms since Access/Jet/ACE needs to ping the remote database every second or so for refreshing the data displayed in bound forms. Since the ODBC refresh interval can be set in Access, one should experiment with that before going the drastic route of going completely unbound, since that would mean you're missing out on 90% of the benefit of using Access as front end.

    In general, I don't see the scenario described as particularly viable. It would mean the tail wagging the dog, with your disaster fallover plan driving the design of your regular app. I would say having Windows Terminal Server hosting of the app somewhere near the remote database server would be a much better solution, since it would require no significant alterations to the Access app.

    • 0