Connecting an Access database to QGIS

From time to time you may want to use information held within an Access database for a QGIS project. QGIS can join Access databases to a layer’s attribute table if there is a common field. For example, if you had a Postcode field in your Access database this could be linked to a postcode CSV file downloaded from the internet. Then your Access data could be geocoded.

In QGIS you can insert the downloaded postcode CSV file by selecting the Points coordinates option and Latitude and Longitude for the X and Y fields in the dialogue box. The Sample Data will confirm that you have successfully imported the file.

Adding the data to QGIS will produce a set of points in the Map window.

To verify that the data is correctly imported you could add a background map from Open Street Maps.

Now we have successfully imported our Bristol postcode data we can join an Access database table in order to geocode our Access data.

In the Data Source Manager | Vector dialogue box select the Database option and select ODBC from the Type drop down box.

If you haven’t set up an Access connection on your computer you will have to type ODBC in the Windows search box which will bring up a couple of options.

If you are using the 32 bit version of QGIS then you can select this option. If you are using the 64 bit version then you may find that there are no entries for Access database drivers. In this case you would probably think you could use the 32 bit drivers as shown in the next screen shot.

If you select the Add option you will find a number of Access drivers. As my Access database file is fairly old I would choose the .mdb option.  

Click on Finish to bring up the ODBC Microsoft Access Setup dialogue box. You should enter a meaningful name in the Data Source Name box then click on the Select button.  

Next as you are in the systems directory double click on the c:\ to get to the root directory. Now find your Access file and highlight it.

Click OK to confirm that the database is correctly identified. Click on the Advanced option if you need to provide a Log in and Password.

Next click OK and finally click OK to close the ODBC Data Source Administrator (32-bit) dialogue box.

We have are now ready to use this data source to connect your database to QGIS.

Select New and complete the Connection Information box as shown in the above screen shot. If you test your connection you will get the Test Connection dialogue box stating that the connection has failed.

If you do an internet search to resolve the problem you will come up with something like the above screen shot. It appears that QGIS 64 bit doesn’t support 32 bit Access drivers. One solution offered was to uninstall the 64 bit version and install the 32 bit version of QGIS to get round this compatibility issue. Alternatively, you could just do a search for 64 bit Access drivers.

Once you have downloaded the 64 bit driver and installed it by clicking on the executable you will able to create a data source in the 64 bit ODBC dialogue box.

Now if you create a new connection you will find that the Test will be successful. Click Add in the Data Source Manager | Vector dialogue box and your Access tables will be available for use within QGIS.

You can select a specific table or all available tables for use within QGIS. Remember that these tables are read only and therefore you need to create fields within the layer you are joining your Access tables to. This will ensure that any data you wish to keep is copied across to your attribute table and not lost when the join is removed