Connecting Microsoft Access databases to ArcGIS Pro

If you are using ArcMap and regularly need connection to Access databases you should be aware that if you decide to upgrade to ArcGIS Pro there is currently no Access database connection facility.

There have been some suggestions that since ESRI introduced a ribbon based interface, issues with Windows 10 are the reason for not having an Access connection. However, MapInfo Pro is now also using the same new ribbon based interface with Windows 10 and has no problems with connecting to Access databases.
​
Microsoft Access 2007 introduced the new  .accdb file extensions which replaced the .mdb file extension.  Currently ArcGIS Pro does not connect to the .accdb format.  However,  ArcMap can connect to this newer Access format.

You can work with Access tables of an .mdb format in ArcMap via an OLE DB connection. OLE DB is the standard for sharing data between applications. This enables connection to Access databases in ArcMap. However to ensure data is not corrupted changes should only be made within Access and not via ArcMap.  

For the same reason, modifying a personal geodatabase should only take place within ArcMap. Modifying these files, which are in an Access .mdb format, within Access could lead to the geodatabase file being corrupted.

Access is not recommended for editing personal geodatabases as it was not created for use with the ArcMap geodatabase format. In addition, unlike many other relational databases Access does not support geometry. The ArcMap geodatabase has to keep track of the table changes whenever a user update occurs. For this reason modifying these tables in Access could corrupt the geodatabase.

As there have been problems with Access connections to ArcMap geodatabase files, there is some logicality in ESRI currently not providing an Access database connection in ArcGIS Pro.

This obviously creates a dilemma for current ArcMap users who want to upgrade to ArcGIS Pro but also need to continue to connect to Access databases. One solution would be to use ArcMap for Access connection and use ArcGIS Pro where Access is not required. Running two different versions of gis software could lead to compatibility issues so this is not an ideal solution. The other problem is that ArcMap will not be supported forever and there will come a point where a decision to switch to ArcGIS Pro will become inevitable to avoid working with unsupported software. Currently, there is no information as to whether ESRI will provide Access database connection in ArcGIS Pro.

An interim solution could be to convert Access databases to another database format that ArcGIS Pro does support. Access is a Microsoft product and therefore the obvious route would be to use a Microsoft conversion tool for moving Access tables to another relational database such as SQL Server 17 Express.  

According to Microsoft the maximum size of an Access database is 2GB whilst the max size of SQL Server 17 Express is 10GB. So any current Access database should be well within the limits of the SQL Server 17 Express database.

SQL Server 2017 Express is a free edition of SQL Server, ideal for development and production for desktop, web, and small server applications. You can download the Express version here.  ​You should also download SQL Server Management Studio (SSMS) which you can use to query, design, and manage your databases, either on your local computer, or in the cloud.

Most importantly SSMS is also free! You can download it here.

Start up SQL Express and then SSMS. A dialogue box for connecting to SQL Express will appear as shown in the following screen shot.

Accept the default settings and you will be connected to the server as shown in the next screen shot.

The next step is to create a database for your Access tables. Right click in the Object Explorer pane on the Database folder. Select and click on the New Database option. 

The New Database dialogue box appears as in the next screen shot. Give your database a name and click OK. In this case I have called the database Observatory.

We now have an empty database called Observatory within SSMS as shown in the next screen shot.

Having confirmed that you have created a new database the next stage is to import the Access tables to the Observatory database. Next select SQL Server Import and Export from your SQL server 17 software options. The dialogue box for the wizard appears as in the next screen shot.

Click on the Next button and choose the Access database connection option and browse to your Access database location as shown in the next screen shot.

Select Destination, Server Name and the Observatory database as shown in the next screen shot.

Click on the Next button and accept the default as in the next screen shot.

Next select the table or tables you wish to import as shown in the next screen shot.

Click on Next to show the next dialogue box as shown in the next screen shot.

The final dialogue box confirms the actions requested and imports the data into the SQL Server database as shown on the next screen shot.

Here is a screen shot of the Observatory tables within SQL Server Management Studio.

The Access tables are now available within SQL Server Management Studio. Now we can connect the imported Access tables to ArcGIS Pro.

Create a new project in ArcGIS Pro called Observatory and click on the Add New Item icon in the Favorites section in the Insert tab. Next choose the New Database Connection option as shown in the next screen shot.

This brings up the Database Connection dialogue box. Choose the Observatory database from the drop down list of the Database section as in the next screen shot.

Click on the OK button. Next within the Catalog pane right click on the server connection as shown in the next screen shot and add it to the project.   

In the Catalog pane click on the Project tab and open the tables available in the server connection as shown in the next screen shot.

Next right click on the Observatory.dbo.Mag_GPS table and add it to the map view. Then open the Geoprocessing pane. Search for and select the XY Table to Point option. Then select the Observatory table and the other default settings as shown in the next screen shot.

Click on Run to create the new table. This will add a points table to your map as shown in the next screen shot.

Now with the newly created points table you can modify the map to meet your requirements such as changing point size, colour and labelling.​ Here is a screen shot showing the map with labelling and the attribute table view.

​You can also import Access tables in order to join them to tables that already contain geometry. You can obviously also move data from your Access tables into tables within your projects where this is appropriate.

​As mentioned earlier the lack of connection to Access tables with ArcGIS Pro is a problem but there are a number of ways to solve this. You can work with Access within ArcMap and keep ArcGIS Pro for other projects. Alternatively you could experiment with transferring your Access tables into a relational database format supported by ArcGIS Pro.