If you have the OGR_FDW we discussed in OGR FDW Windows first taste built with ODBC support,
then you can access most any ODBC datasource from PostgreSQL. This is especially useful for Windows users. Two of the data sources I've been experimenting with are SQL Server
and MS Access. In this article, I'll demonstrate how to connect to MS Access with PostgreSQL running on a windows box. I think there is an Access driver for Unix/Linux most robust utilizes java. I won't go there.
Registry hack should no longer be needed for PostGIS 2.2 ogr_fdw bundle and up since these come packaged with newer GDAL libraries that have the fix.
Querying MS Access with OGR_FDW
There are two ways you can specify an MS Access connection with OGR and by extension OGR FDW.
- You can specify just by the filename if your libgdal is version 1.10+, if its a .MDB file, AND you have a driver that happens to be called Microsoft Access Driver (*.mdb).
Note that if you are on 64-bit windows or using the newer MS Access, your driver is probably called Microsoft Access Driver (*.mdb, *.accdb). I have a hack to get around this issue, but there is a committed fix for it which should make it in next release of GDAL documented here: http://trac.osgeo.org/gdal/ticket/5594. I'll probably recompile the windows builds with latest trunk to get this fix at some point.
- You can prefix with ODBC:the system dsn name goes here. This you can use for any datasource and requires you create a System DSN entry. Note there are 2 ODBC managers if you are on a 64-bit windows box. The one in path C:\Windows\SysWOW64\odbcad32.exe is for using with 32-bit system (so if you are using PostgreSQL 32-bit on a windows 64-bit, you'd use this one. The one in path C:\Windows\System32\odbcad32.exe (is the 64-bit version on a Windows-64bit OS and for 32-bit (is the only ODBC manager and 32-bit). Confused yet?
There is one big gotcha that affects many 64-bit windows users. So I'll highlight it here. Sadly I don't know an easy fix for this one. It's the same issue if you wanted to query an MS Access database or Excel file from SQL Server 64-bit. Luckily at least as far as ogr_fdw is concerned, ogr_fdw doesn't need ODBC to query excel files, so you are only screwed if you need to connect to MS Access. SQL Server 64-bit folks are doubly screwed since SQL Server needs the driver for both MS Access and Excel :). Of course a SQL Server user could do something somewhat Rube Goldbergish and install PostgreSQL, create a Foreign table to Excel from PostgreSQL, and then do a linked server or openquery call to the Foreign table excel file from SQL Server (I should chart this :) ). So here it is:
If you want to query MS Access from a 64-bit PostgresSQL, you will either need MS Access 64-bit or the freely available MS Access 2013 64-bit runtime or runtimes MS Access 2010 64-bit version. Make sure you install the 64-bit version. If you for some reason you have 32-bit Office installed on your server and you have PostgreSQL 64-bit, you are kinda screwed since the 64-bit runtime will not install on a machine with 32-bit Office. I should also add that MS Access 2013 runtime requires Windows 2008 R2 or above. So if you have Windows 2008 or lower, you need to use the MS Access 2010 or lower runtime.
Another bit of a gotcha, if you have your MS Access database on a network share, yes UNCs of the form \\myserver\myshare\myfolder\mydatabase work fine, if and only if your PostgreSQL server service account can access that path. That means generally you need to run your PostgreSQL service under a network account or at least an account with same user and password as is on the other server.
The registry hack
If you have the newer MS Access drivers and want to be able to use a DSNLess connection for MDB files, and aren't running GDAL trunk, you can get away
by creating a text file with a .reg extension that has contents below and then right click install. You want to first verify the ACEODBC.dll path matches what you have installed.
In my case I have the MS access 2013 64-bit runtine installed on my server. If you are using 32-bit this is a non-issue since Windows ships with the 32-bit MS Access driver.
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\Microsoft Access Driver (*.mdb)]
"Microsoft Access Driver (*.mdb)"="Installed"
When you go to ODBC manager in Control Panel (or via the paths I mentioned) -- you should see a new entry in Drivers section.
DSNLess (AKA the file path) Connection to MS Access
You can use the ogr_fdw_info tool to query MS Access (good old Northwind which I snagged an old version from http://www.geeksengine.com/article/northwind.html without a registered DSN with something like this:
ogr_fdw_info -s "C:/fdw_data/northwind.mdb"
And you'll get an output like this:
Alphabetical List of Products
Category Sales for 1997
Current Product List
Customers and Suppliers by City
Employee Sales by Country
Order Details Extended
Product Sales for 1997
Products Above Average Price
Products by Category
Quarterly Orders by Product
Sales by Category
Sales by Year
Ten Most Expensive Products
This SQL Statement using the hack function I mentioned in prior article works in this case to:
To get the Orders table, structure do this
C:\Program Files\PostgreSQL\9.3\bin>ogr_fdw_info -s "C:/fdw_data/northwind.mdb" -l "Orders"
Which would output something like this (which I change myserver to Northwind):
CREATE SERVER northwind
FOREIGN DATA WRAPPER ogr_fdw
format 'ODBC' );
CREATE SCHEMA northwind;
CREATE FOREIGN TABLE northwind.orders (
shipcountry varchar )
OPTIONS ( layer 'Orders' );
CREATE FOREIGN TABLE northwind.products
(fid integer ,
geom bytea ,
productid integer ,
productname character varying ,
supplierid integer ,
categoryid integer ,
quantityperunit character varying ,
unitprice real ,
unitsinstock integer ,
unitsonorder integer ,
reorderlevel integer ,
discontinued character varying )
OPTIONS (layer 'Products');
UPDATE: With ogrfdw packaged with PostGIS 2.2 bundle this issue is fixed so you can do:
SELECT ogr_fdw_sql_table('C:/fdw_data/northwind.mdb','Orders'); to get below output.
Sadly trying to read the table structure with my hack function from within PostgreSQL just returns NULL. Also note the unwelcomed geom column (which was represented as type geometry) and I changed to bytea, tagging along for the ride. To be fair MS Access databases are structures repurposed by GIS companies like ESRI and GeoMedia, so OGR blindly treats them as having spatial content. There is actually spatial content in the form of raster in the categories table, but that sadly has got an OLE wrapper around it that would require stripping the OLE header before you can use the PostGIS raster functions on them.
UPDATE: With PostGIS 2.2 ogrfdw packaged bundle this issue is fixed so datetime come thru fine. This was fixed in ogrfdw source code before PostGIS 2.2 release.
There is a current issue with the ogr_fdw driver that is can't handle timestamps for some reason so replace all timestamp with varchar. This is a similar issue with when connecting to SQL Server with ogr_fdw, though using the SQL Server 2008+ date type works and maps fine. I'll post new binaries once this issue is resolved. Cursory tests seem to suggest this timestamp issue is not limited to windows or ODBC though is more easily triggerable in this combination.
Very insane use case for this
Upon learning about being able to query MS Access from PostgreSQL, Leo's first reaction and excitement.
You mean I can finally use all these cool PostgreSQL functions from within MS Access?
Didn't quite know what he meant by that until I saw him in action. Paul -- here's a wrap-around for you, Leo style :)
- Create a PostgreSQL foreign table link to MS Access table.
- Write a complicated view involving this foreign table and any of the gazillions of functions PostgreSQL has to offer in PostgreSQL.
- Create a linked table back to this view from MS Access
- Alas magic -- you update your MS Access table, the view data changes. A little slow for a lot of records, but still acceptable in many cases. Leo only tried a 30,000 record table.