1

I'm trying to set a dynamic connection between QGIS and Microsoft Access database. Knowing that MS Access can't handle spatial data, I put PostgreSQL/Postgis in the chain. So that Access could connect to PostGIS and PostGIS to QGIS.

Is there any module that can allow MS Access to connect dynamic with PostgreSQL/PostGIS?

  • You can use the PostgreSQL ODBC driver to connect Access to PostgreSQL via External Data -> ODBC Database. Unfortunately it does not support PostGIS types. – thibautg Sep 15 '17 at 15:37
3

If you want to connect to an Access Database to Postgres you can install the ogr_fdw extension. This awesome Postgis add-on utilizes the Foreign Data Wrapper functionality of Postgres to connect to external spatial data sources -like Access and many others.

Provided you have the OGR MDB Driver installed, ogr_fdw will allow you to connect to your Access Database by defining a foreign server:

CREATE SERVER access_server
  FOREIGN DATA WRAPPER ogr_fdw
  OPTIONS (
    datasource '/path/to/mymdb.mdb',
    format 'MDB' );

You can define all of the foreign tables in the Access Database in one command via:

CREATE SCHEMA mymdb;
IMPORT FOREIGN SCHEMA ogr_all 
  FROM SERVER access_server 
  INTO mymdb;

Now you can query your Access Database tables as you would any normal Postgres table:

SELECT * FROM mymdb.mytable

Since the Foreign Data Wrapper merely connects to the Access Database -and doesn't actually import the data into Postgres directly -any saved changes you make directly to a table within Access should be IMMEDIATELY visible in Postgres -which hopefully fits your definition of "dynamic". However, if you modify the table definitions within the Access Database, you will obviously need to make corresponding changes to the foreign table definitions in Postgres.

1

I think there is not. You will need a mid-tier between the databases. GDAL/OGR as a driver behind the scenes of QGIS can manage many data sources. If you want to work at this level please read GIS.SE:72426.

In general you have to distinguish between reading and writing tasks. For MDB only the 'read from MDB' and 'write to PSQL' direction is well documented. I think postgres/postgis isn't problematic for QGIS / GDAL at all.

For an orientation what GDAL can do with MDB stuff, please read the gdal/ogr format page for MDB files. Several access variant for MDB can be used:

  • ODBC with MDB Tools read/write without geodata features like PCGEO
  • Jackess with via JAVA read

So what you can do or will use depends on your environment (Windows/Linux/Mac) and the problem you want to solve.

One strange solution in the setup you described, could be to run an MDB connection in a PG-SPI context, with an object mapping engine working like ogr2ogr (GDAL). But is this wise?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.