ArcGIS Pro

Bridging Data: Create and use OLE DB connections in ArcGIS Pro

The adventure begins…

A wooden walking bridge in Frick Park on a sunny summer day, Pittsburgh, Pennsylvania,
A wooden walking bridge in Frick Park on a sunny summer day, Pittsburgh, Pennsylvania. (© woodsnorth - stock.adobe.com).

Recently, while packing for an upcoming 5-day family trip to Pittsburgh, PA, (a.k.a, the steel city) to trace back some of my in-law’s family roots, I realized that I had been to (or through) Pittsburgh only once before.  My only recollection of Pittsburgh occurred more than 30 years ago and lasted all of about 2 minutes as I passed through this city at 65 mph in a bus on my way to a summer youth camp from Florida.  So, the anticipation of our family spending almost a week in what I expected would be more of an industrious smog-laden city seemed a bit (and I dare say), dreadful, and secretly wished we were heading to the beach.

Fast forward a week and as our trip to this city quickly drew to a close, I’ll admit (ehem…listen up as I don’t say this often), I was wrong!  I found Pittsburgh to be a beautiful city and have a whole new appreciation for this city of steel that launched America’s industrial revolution. What pleasantly surprised me the most during our family trip was discovering the dynamic landscape of all its steep hillsides, boroughs, valleys, and rivers that traverse this city. 

As a result of its dynamic landscape, and its location at the convergent point of three large rivers (Monongahela, Allegheny, and Ohio), I discovered that Pittsburgh is surrounded by water and has bridges everywhere!  In fact, Pittsburgh is known as the city of bridges—446 bridges, to be exact, call this city home.

Pittsburgh, Pennsylvania skyline
Pittsburgh, Pennsylvania skyline (© David - stock.adobe.com)

Pittsburgh has bridges for cars, people, and trains.  New bridges, old bridges, decorative bridges, plain bridges, high bridges, low bridges, narrow bridges, and wide bridges, are all equally vital connections not only for local residents of Pittsburgh but also for our nation’s communities and economies.

Data Connections

Underside of Pittsburgh bridge structure
Underside of Pittsburgh bridge structure (©Andrew - stock.adobe.com)

Similar to the vital connections bridges provide, the ability to connect to and access data is just as vital within GIS!

Just like the diverse types and purposes of bridges, and methods to access and use them, there are also different types and purposes of data, and various methods for us to connect and use this data.

Data connections allow us to view, add, update, or remove data.

In the first blog of this series, It’s Not Personal, I explained why personal geodatabases are not supported in ArcGIS Pro.

But wait!

If personal geodatabases (.mdb) are not supported in ArcGIS Pro, how will we be able to connect to and use our data in a personal geodatabase from ArcGIS Pro?

While personal geodatabases are not supported within ArcGIS Pro, meaning you can’t create, update, or delete data from ArcGIS Pro, you can still connect to personal geodatabases (.mdb) and other data sources such as Microsoft Access databases (.accdb).

If you are currently using one or more personal geodatabases in ArcMap, here are some options for using the data in ArcGIS Pro depending on your workflows:

——> Continue reading below for more details and options using OLE DB connections.

——> This workflow, will be addressed in the third blog of this series and will provide access to a set of data migration tools!

 

OLE DB connections

In ArcGIS Pro, you can establish an Object Linking and Embedding Database (OLE DB) connection (read-only) to access tabular data sources from any database that supports OLE DB, and for which you have the driver. This type of connection is most commonly used to connect ArcGIS Pro to data sources that support OLE DB but cannot be accessed from the list of supported database platforms on the Database Connection dialog box.

Click this link to learn more about OLE DB connection terminology

 

OLE DB terminology

Object Linking and Embedding Database (OLE DB) is a Microsoft Windows connectivity method that uses the same core application programming interface (API) to help bridge communication between client applications and a variety of data sources. This bridge consists of an OLE DB provider (a set of .dll files) that uses the services of an ODBC driver to connect and interact with both relational and nonrelational databases in a uniform manner.

OLE DB providers

An OLE DB provider is similar to a language translator that helps bridge communications between two people speaking different languages. For a client application and a data source to successfully communicate with each other, the specific OLE DB provider for the data source you want to connect to must be installed on the same client machine where ArcGIS Pro is installed and will be used to create the OLE DB connection. Installing the appropriate OLE DB provider equips the client application with the correct language needed to communicate with its associated data source.

There are specific providers for each type of data source supported by OLE DB. To make an OLE DB connection from a 64-bit ArcGIS client such as ArcGIS Pro, install the appropriate 64-bit drivers on your ArcGIS Pro machine to connect to these data sources using OLE DB.

Open Database Connectivity

Open Database Connectivity (ODBC) is a connectivity method supported on Windows, Linux, Mac, and UNIX.   The Microsoft® ODBC Data Source Administrator application manages database drivers and data sources and is located in the Windows Control Panel under Administrative Tools.

ODBC drivers

An ODBC driver is the component that processes ODBC requests and returns data to the application. If necessary, drivers modify an application’s request into a form that is understood by the data source. You must use the driver’s setup program to add or delete a driver from your computer.

Data sources

A data source is the database or file accessed by an ODBC driver and is identified by a data source name (DSN).

Data source name

A data source name (DSN) is a unique name that you can create to associate a particular ODBC driver with the data source you want to access. You can use the Microsoft® ODBC Data Source Administrator to add, update, and delete DSNs. A DSN also stores the connection details to the data source, like its database name, directory, database driver, UserID, password, and so on.

Once a DSN has been created, you can use ArcGIS Pro to create an OLE DB connection using the Data Link Properties dialog box, where you can specify your newly created unique DSN to request a connection to an ODBC data source.

Fantastic!  Now that you’re all caught up on OLE DB terminology, let’s move on…

 

Choose your workflow adventure!

Tigbao swinging bridge in Bohol, Philippines
Tigbao swinging bridge in Bohol, Philippines (©12ee12 - stock.adobe.com)

For the remainder of this blog, I’m going to help guide you through these steps as we explore how to set up and use OLE DB connections in ArcGIS Pro as a bridge for establishing a read-only connection to a variety of data sources listed below.  Plus, we’ll learn how to use this data once an OLE DB connection has been established.

Let’s explore how to…

 

Create an OLE DB connection to a personal geodatabase (.mdb) and a Microsoft Access database (.accdb) using an OLE DB provider

The Microsoft Access Database Engine technology helps bridge communication and data transfers between files that are proprietary to the Microsoft Office system and other non-Microsoft Office applications such as ArcGIS Pro.

When using the ArcGIS Pro (64-bit) client application to create an OLE DB connection to a personal geodatabase (.mdb) or a Microsoft Access database (.accdb), you must have the 64-bit version of Microsoft Access Database Engine Redistributable driver installed.  If this driver is installed properly, it will appear under the Providers tab in the Data Link Properties dialog box.

Note: Without using OLE DB, ArcGIS Pro cannot directly access or read the Microsoft Access format.

Click this link to learn more about Microsoft Access database (.accdb) and a personal geodatabase (.mdb)

 

Microsoft Access database (.accdb) and a personal geodatabase (.mdb) Overview

A personal geodatabase is an ArcGIS-specified GIS database that is stored in a Microsoft Access (.mdb) format. A personal geodatabase can store, query, and manage both spatial and non-spatial data. Because they are stored in Access databases, personal geodatabases have a maximum size of 2 GB.

A Microsoft Access database is the default database format developed by Microsoft and is used when saving or creating a Microsoft Access database (.accdb).

Excellent!  Now that you’ve had a brief overview of a Microsoft Access database (.accdb) and a personal geodatabase (.mdb), let’s move on…

IMPORTANT: Before beginning, ensure your ArcGIS Pro application is closed.

Step 1: Install the appropriate OLE DB provider locally on the machine where ArcGIS Pro resides.

From Microsoft's website, select the MS Access redistributable x64 bit driver to download.
Verify MS Access redistributable x64 bit driver was installed successfully.
Add or remove programs from the Operating System settings.
Verify Microsoft Access database engine x64 bit redistributable driver was installed.

Step 2: Create an OLE DB connection from ArcGIS Pro using the Data Link Properties dialog box to select the OLE DB provider and connect to the data source. 

IMPORTANT: If ArcGIS Pro was opened while installing the driver, close and reopen ArcGIS Pro.

Create a new OLE DB Connection from the Catalog pane.
Selecting New OLE DB Connection will open the Data Link Properties dialog box.

TIP: The Provider tab allows you to identify the data provider you want to use to establish a connection to a data source. The data provider indicates the type of data source.

From the Provider tab on the Data Link Properties dialog box, the Microsoft Office 16.0 Access Database Engine OLE DB Provider has been selected.

Examples:

The following is a full path data source example to a personal geodatabase (.mdb): C:\Data\OLEDB\PGDB\Transportation_PGDB.mdb

From the Connection tab on the Data Link Properties dialog box, the full path to a .mdb file is shown for the Data Source.

The following is a full path data source example to an Access database (.accdb): C:\PE\Data\OLEDB\AccessDB\AccessDB\XYEventData.accdb 

From the Connection tab on the Data Link Properties dialog box, the full path to a .accdb file is shown for the Data Source.

Note: If the database has a username and password, provide that information. If it does not contain a username and password, leave it empty.

From the Data Link Properties dialog box, testing a connection to a personal geodatabase.
Testing a connection to a personal geodatabase.

Note: If the connection fails, ensure that the settings are correct. For example, spelling errors and case sensitivity can cause failed connections.

Examples:

I renamed my OLE DB connection to my personal geodatabase (.mdb), Transportation_PersonalGDB_OLEDB.odc

OLE DB connection to a personal geodatabase (.mdb) appears in the Catalog pane under the Database folder.

I renamed my OLE DB connection to my Access database (.accdb), XYEventData_AccessDB_OLEDB.odc

OLE DB connection to a Microsoft Access database (.accdb) appears in the Catalog pane under the Database folder.

Now that you’ve successfully established an OLE DB connection to a personal geodatabase (.mdb) and an Access database (.accdb) directly within ArcGIS Pro, you’re ready to start working with and using the tabular data.

 

Create an OLE DB connection to a MySQL database after configuring a DSN to an ODBC driver

IMPORTANT: Before beginning, ensure your ArcGIS Pro application is closed.

Step 1: Download and install the MySQL ODBC client (mysql-8.0.32-win64.zip) locally on the machine where ArcGIS Pro resides.

Step 2: Configure a data source name (DSN) using the ODBC Data Source Administrator (64-bit).

Open the ODBC Data Source Administrator (64-bit)
Add User Data Source Name (DSN)

Note: If you had previously installed the 64-bit Microsoft Access Database Engine Redistributable and used the Microsoft Office 16.0 Access Database Engine OLE DB Provider to create an OLE DB connection to a MS Access database (.accdb) or a personal geodatabase (.mdb), you will see that the Microsoft Access Driver (*.mdb, *.accdb) is also installed. If needed, you would follow these same steps to create an OLE DB connection to a personal geodatabase (.mdb) or a Microsoft Access (.accdb) database after configuring a DSN to this ODBC driver.

Select a driver to use when setting up a data source name.

Example:

I’ve typed the name MySQL_ODBC_8032_Driver (shown below) for my DSN.   You can use the same name used in my example, or you can type in a different name.

Create Data Source Name
Server name and port # for DSN
User, password and database name credentials for DSN

Tip: A DSN also stores the connection details to the data source, like its database name, directory, database driver, UserID, password, and so on.   

For my example, I used the name MySQL_ODBC_8032_Driver for my DSN.

Creating my User DSN is complete.

Step 3: In ArcGIS Pro, when you create an OLE DB connection, use the Data Link Properties dialog box to create the OLE DB connection to MySQL database.

Create a new OLE DB Connection from the Catalog pane.
Selecting New OLE DB Connection will open the Data Link Properties dialog box.

Tip: The Provider tab allows you to identify the data provider you want to use to establish a connection to a data source. The data provider indicates the type of data source.

From the Data Link Properties dialog box, selecting Provider and Connection DSN to MySQL database

Tip: Remember, a DSN stores the connection details to the data source, like its database name, directory, database driver, UserID, password, and so on. Therefore, you don’t need to provide any additional information.

Using a DSN in the Data Link Properties dialog box to test a connection to MySQL database.

Note: If the connection fails, ensure that the settings are correct. For example, spelling errors and case sensitivity can cause failed connections.

Optionally, right-click this connection to rename your OLE DB connection to something more meaningful.

Example:

I renamed my OLE DB connection, MySQLDB_OLEDB.odc

OLE DB connection to a MySQL database appears in the Catalog pane under the Database folder.

Now that you’ve successfully established an OLE DB connection to a MySQL database directly within ArcGIS Pro, you’re ready to start working with and using the tabular data.

 

Use ArcGIS Pro to work with and use the tabular data from your OLE DB connection

Import data

Once you’ve established an OLE DB connection, if needed, you can now import a table from your OLE DB connection into a geodatabase.

In ArcGIS Pro, in the Catalog pane, right-click the geodatabase, and on the context menu, click Import and then click Table(s).

 

Import tables into a geodatabase from the Catalog pane in ArcGIS Pro
Import tables into a geodatabase from the Catalog pane in ArcGIS Pro.

This will open the Table To Geodatabase tool.

Using this tool, you can import one or more tables (e.g., dBASE, OLE DB, or geodatabase tables) into a file, mobile, or enterprise geodatabase.

 

Table to Geodatabase geoprocessing tool

Note: When you import several tables at the same time with the Table To Geodatabase tool, each table imports into a new table. The tool automatically corrects any illegal or duplicate field names.

If you have many tables to import and want to chain multiple tools together, you can create and run a model instead of manually repeating the import from the context menu. A model helps automate importing by allowing you to save and reuse environment settings and tool parameters. Once you’ve created a model, you can import data, edit the model to specify other input data, modify parameters, and rerun the model.

Learn more about models and model building

Note: The fields you create in the new tables are named the same as the fields you’re importing. However, any invalid characters in the field names are automatically replaced. For example, a hyphen is replaced with an underscore.

Data usage options

Here are some additional common tabular workflows and usage options available to use with the read-only tabular data from your specified data source.

Display tables

Select records in tables

Create table associations

Use data as input for geoprocessing tools

 

Summary

We’ve set up OLE DB connections from ArcGIS Pro to a Microsoft Access database (.accdb), personal geodatabase (.mdb), and MySQL database in this blog. The same general process can be followed to make an OLE DB connection to any database, such as Denodo DB, Vertica DB, Maria DB, and many other data sources that support OLE DB but cannot be accessed from the list of supported database platforms on the Database Connection dialog box in ArcGIS Pro.

The following table reflects a summary of the data source types we explored in this blog along with the client drivers to install to successfully create an OLE DB connection from ArcGIS Pro. While this table is not inclusive of all the data source options, the same general process can be followed to connect to any database via OLE DB.

Data Source Type Provider / Driver to install Configure DSN (Y/N) prior to OLE DB connection Data Link Properties
“Provider” to select “Connection” details to provide
Personal Geodatabase (.mdb) Microsoft Access Database Engine 2016 Redistributable N Microsoft Office 16.0 Access Database Engine OLE DB Provider Provide the fully qualified path location to the data source you want to access**
Microsoft Access Database (.accdb) Microsoft Access Database Engine 2016 Redistributable N Microsoft Office 16.0 Access Database Engine OLE DB Provider Provide the fully qualified path location to the data source you want to access**
MySQL MySQL ODBC 8.0 Driver Y Microsoft OLE DB Provider for ODBC Drivers Select previously configured MySQL DSN
**Fully qualified data source path location = File path + database/file name + file extension.

What’s Next?

I’ve walked you through how to set up and use OLE DB connections in ArcGIS Pro as a bridge for establishing read-only connections to various data sources and reviewed the options available to work with and use this tabular data.

Moving forward, we will continue our quest to uncover and explore the following:

Now, if you’re ready…

Our adventure continues with the third blog in this series, Migrating Data!

This article was originally published on May 18th, 2023, and was last updated on July 1st, 2023.

Banner photo © Orlando Florin Rosu – stock.adobe.com

About the author

Elaine is a Product Engineer on the Geodatabase team and has worked with the geodatabase in various capacities at Esri since 2001. Elaine is passionate about finding opportunities to add valuable and creative content for the geodatabase technology that empowers ArcGIS users. A southerner born and bred, she's a mom, rose gardening enthusiast, aficionado of fine teas, baking, bagpipe music and lover of all things pineapple.

Connect:

Next Article

Tighten Up Your Edits with Editing Constraints in ArcGIS Online

Read this article