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

Creating joins to Microsoft Access database tables with ArcGIS Pro

24/11/2018

0 Comments

 
Joining an Access table to a table in a personal geodatabase within ArcGIS software is currently only feasible with ArcMap. However, as discussed in a previous blog, you could import your Access database tables into a format which is acceptable to ArcGIS Pro. In this case creating a join to Access tables in ArcGIS Pro would be possible.
Prior to discussing how to join Access tables in ArcGIS Pro it is relevant to quickly review the history of personal geodatabases and a possible reason for ArcGIS Pro currently not supporting Access tables.
Personal geodatabases were introduced with version 8 of ArcMap. Personal geodatabases use the Microsoft Access .mdb format and are only available on the Windows operating system. A personal geodatabase has on average an effective size of up to 500 mb. Whilst the maximum size of Access tables is theoretically 2GB in reality because of performance issues 500 mb is recommended as the optimum size. Additionally, Esri has also now developed the file geodatabase which gives better scalability, performance as well as cross platform use.  
Since ESRI introduced ArcGIS Pro, Access database connection availability has been limited to ArcMap. Some users may choose to use ArcMap for Access connection and ArcGIS Pro for other work. ArcGIS Pro has many attractive features, which take advantage of the new Windows 10 ribbon interface, which are superior to the menu based system used by ArcMap.
ArcGIS Pro may not support Access database connection because of problems experienced with connecting Access databases to ArcMap. This could be because personal geodatabases and older Access tables share the same .mdb format. As the personal geodatabases use .mdb files they could be accidentally corrupted by users modifying these tables within the Access software.
As mentioned in the previous blog you can easily import Access databases into SQL Server and then access these tables in ArcGIS Pro.
Here is a screen shot of the Observatory database, which has been imported from Access, into SQL Server Management Studio. Here we can see the table which we can use within an ArcGIS project.
Picture
In ArcGIS Pro, a server connection which was previously made, is accessible in the Catalog pane within the Favorites tab. By right clicking on the server connection we can add it to our project as shown in the next screen shot.
Picture
Now within the Project tab we have access to the server tables as shown in the next screen shot.  
​
Picture
We can add the Observatory.dbo.Secteurs_OBS_dep table to the map by right clicking on it and selecting the Add To Current Map option. This will bring up the New Query Layer dialogue box as shown in the next screen shot. 
Picture
Selecting the three fields will enable the Finish button. Clicking on Finish adds this table to the map as shown in the next screen shot.
​
Picture
In this case we can join the Observatory.dbo.Secteurs_OBS_dep file to the Departments_Metro file as they both have columns containing department code information as shown in the next screen shot.
Picture
We can now join the two tables by right clicking on the Departments_Metro layer and selecting Joins and Relates Add Join as shown in the next screen shot.
Picture
Clicking on Add Join brings up the Geoprocessing Pane. Here clicking on Run, after selecting Dep_Code, joins the Departments_Metro table to the Observatory.DBO.Secteurs_OBS_Dep table, as shown in the following screen shot.
Picture
The Departments_Metro table now has additional columns from the other table as shown in the next screen shot.
​

Picture
Now we can carry out tasks which could only be done with this join in place. Selecting the Geoprocessing Pane we can search for the Dissolve tool. This Dissolve tool will enable the Zone field from the joined table to be used to create a new layer for further analysis. We now have a new layer which contains seven zones as shown in the following screen shot.
​
Picture
Next we have joined the Departments_Metro_Dissolve layer to the Departments_Metro layer with the Zone attribute as the common field. Then using the Graduated Colors option we have created a map of the visits by each zone as shown in the next screen shot of the Symbology pane.   
​
Picture
We can now create a thematic map showing visits by each individual zone. For this purpose the Graduated Colors option is appropriate as shown in the following screen shot. We have also labelled the map with the Zones attribute field.
Picture
We have been able to create this thematic map as the Departments_Metro layer is joined to the Observatory.DBO.Secteurs_OBS_dep layer. In turn the Departments_Metro layer is also joined to the Departments_Metro_Dissolve layer by the Zones attribute.   
​As well as joining layers we could have copied data from the joined tables by creating new columns for the Zones and Visits in our Departments_Metro layer. Then further analysis could be carried out without relying on joined tables.
So by importing Access database tables into SQL server format we have been able to perform a number of tasks in ArcGIS Pro which would not be possible directly with Access because ArcGIS Pro does not currently support connection to Access database tables.
0 Comments

Your comment will be posted after it is approved.


Leave a Reply.

    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