ms access - Reading ODBC connection using QGIS?

I have a co-worker who manages a large MS Access database that contains X & Y fields for various locations he tracks. We have previously exported the data to CSV and used the QGIS Delimited Text Plugin to plot the locations.

We would now like to be plot this data directly in QGIS by connecting to the MS Access db via an ODBC connection (as opposed to export to CSV).

1. Can QGIS consume the data from the ODBC connection to plot the X-Y locations?


2. Can it be "live linked" to the ODBC connection so that when a project is re-opened, the layer "replots" all old and newly added locations dynamically (as opposed to exporting to shapefile, which would create a snapshot in time)?


3. Can you create tabular ODBC connections that can be joined to spatial tables within QGIS?

You can do all of this in ArcGIS ("Add XY Data" tool), so it would be great if this capability existed in QGIS. I just cannot find anywhere that mentions its existence.

I am not planning to move to a spatial database. As a GIS person myself, yes I know that it makes sense to move to a spatial database, and yes I have used PostGIS to manage this kind of data. But these are not options right now. The user is not a GIS person nor wants to be, and he manages his own database in MS Access and that will not change. I am and am pulling him kicking and screaming into the GIS world and want to make it easy as possible. He can live with exporting to CSV, I was just hoping there is a better solution USING ODBC FOR MS ACCESS and displaying the XY data in QGIS.

Answer

With the new "Processing" tools available in QGIS 2, this becomes a bit easier. While I have still not found a way to "live-link" the data (so that if you change the values in the MS Access table, the point moves automatically), this method seems to work pretty well.

Credit goes to "Christina" and "Bernd" in the comments section of this page (and of course @underdark for the blog) for how to create the ODBC connection to your MS Access database:

Create an ODBC Connection:

For Win7, 32 bit: Go to control panel/administrtive tools/data sources (ODBC)

For Win7, 64 bit: Instead of using the default “control panel/administrative tools/data sources (ODBC)”-way, with 64bit versions, you have to use C:\Windows\SysWOW64\odbcad32.exe !

2. Click on the user DSN tab and then ‘add’


3. Scroll down to Microsoft Access Driver (*mdb) and click ‘finish’


4. Give the connection a name then choose ‘select’ to browse to the location of the database, then click ‘ok’ to exit the dialogue and again to close the dsn window.

Load the layer into QGIS:

5. Go to add vector layer and select database option.


6. Ensure the database ‘type’ box says ‘ODBC’ and click ‘new’


7. In the name box type a name for connection


8. The host is localhost



9. The database name is the name of the dsn you created in step 4


10. All other boxes should be blank but you can tick ‘save password’ box.


11. Test the connection and make a note of any errors. When successful, click ‘ok’ to return to the ‘add vector layer’ dialogue


12. Click ‘open’ to open this connection, and if asked for password, click ‘ ok’. Choose your layer (table from database), bearing in mind it may not have any geometry.

Convert the XY data to Points:

13. With the table in the TOC, go to "Processing"--> Toolbox--> Geoalgorithms--> Vector--> Creation--> "Points layer from table".


14. Fill out the form as seen below: