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.
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.
Now within the Project tab we have access to the server tables as shown in the next screen shot.
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.
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.
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.
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.
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.
The Departments_Metro table now has additional columns from the other table as shown in the next screen shot.
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.
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.
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.
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.
RSS Feed