Reading ODBC connection using QGIS?

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:

Caveat: I've not tried this, so I'm willing to bow to anyone with direct experience.

You can connect to an ODBC datasource in the "add vector layer" dialog, just select "database" as the source type and ODBC as the database type. QGIS uses the ODBC driver for OGR, so the same caveats will apply - that is you will need a table called GEOMETRY_COLUMNS and so on, so your colleague will probably need to modify the database.

I've not used Access since shortly after the release of version 1.0, but as it is not spatially enabled, you will probably find performance to be an issue. Presumably you're not going to just drop Access and start using a spatially enabled database, because that's unlikely to be economically viable, but if your co-worker ever feels the urge for change, I can strongly recommend going over to something a bit more powerful.

While I am still interested in hearing if there are other options available, I did come across a message post that mentioned this was possible using the eVis Plugin.

The documentation describes in the Database Connection section how you can connect to an MS Access or ODBC connection to create XY point locations from the database, without creating a static snapshot. I simply typed "select * from " and eVis popped up a dialog box that asks for the layer name and X-Y fields.

This unfortunately doesn't appear to create a "live-linked" connection to the MS Access db because the next time the project opens, QGIS is confused ("unable to open one or more project layers"). The solution to this, though, is to create an predefined XML query that the user can select.

So while not a perfect solution, this is one step better than exporting to CSV. In the future I think it would be great to see the Delimited Text plugin expanded to accept ODBC connections for this purpose.