jps services
  • Home Page
  • GIS Training Courses
    • QGIS Training Courses
    • ArcGIS Training Courses
    • MapInfo Pro Training Courses >
      • MapBasic Training Course
  • About
  • Blog

Connecting an Access database to QGIS

30/3/2020

0 Comments

 
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.
Picture
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.

Picture
Adding the data to QGIS will produce a set of points in the Map window.
Picture
To verify that the data is correctly imported you could add a background map from Open Street Maps.
Picture
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.
Picture
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.
Picture
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.  
Picture
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.  
Picture
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.
Picture
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.
Picture
Next click OK and finally click OK to close the ODBC Data Source Administrator (32-bit) dialogue box.
Picture
We have are now ready to use this data source to connect your database to QGIS.
Picture
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.
Picture
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.
Picture
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.
Picture
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.
Picture
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
0 Comments

    Author

    Joe Short BSc has been involved with various mapping solutions for over twenty years.  If you are considering implementing a GIS  or have ArcGIS Pro, MapInfo Pro or QGIS training requirements, jps services would be happy to be of assistance to your organisation. 

    Archives

    April 2020
    March 2020
    October 2019
    September 2019
    August 2019
    July 2019
    March 2019
    November 2018
    October 2018
    August 2018
    July 2018
    November 2017
    October 2017
    September 2017
    July 2017
    February 2017
    January 2017
    December 2016
    November 2016
    October 2016
    May 2016
    February 2016
    September 2015
    August 2015
    April 2015
    February 2015
    November 2014
    October 2014
    July 2014
    June 2014
    May 2014
    March 2014
    February 2014
    December 2013
    November 2013
    October 2013
    September 2013
    August 2013
    June 2013
    May 2013
    April 2013
    February 2013
    December 2012
    October 2012
    September 2012
    August 2012
    July 2012

    Categories

    All
    Arc Gis
    Arcview
    Autodesk
    Cad
    Gis Training
    Local Government
    Mapbasic
    Mapinfo
    Quantum Gis
    Relational Databases
    Saga Gis

    RSS Feed