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

The adventure begins…

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

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:

• • • Option 1: Access your tabular data by establishing an OLE DB connection (read-only) from ArcGIS Pro.

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

• • • Option 2: Access your spatial data by migrating the personal geodatabases to either a file or mobile geodatabases.

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

Choose your workflow adventure!

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 Microsoft Access database (.accdb), and a personal geodatabase (.mdb) using an OLE DB provider
  • Create an OLE DB connection to a MySQL database after configuring a DSN to an ODBC driver
  • Use ArcGIS Pro to work with and use the tabular data from your OLE DB connection

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.

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.

• • • Download the latest 64-bit Microsoft Access Database Engine Redistributable (accessdatabaseengine_X64.exe) file by clicking the Download button, and save the file locally to your machine.

• • • Follow the Install Instructions provided to complete the process of locally installing the downloaded accessdatabaseengine_X64.exe file.

• • • To verify that the driver was installed successfully, in the Windows taskbar Search box, type Add or remove programs, and select Add or Remove programs from the search results.

• • • In the Apps & Features pane, search for Microsoft Access database engine 2016.

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. 

• • • Open the ArcGIS Pro application.

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

• • • In the Catalog pane, right-click Databases and select New OLE DB Connection.

• • • From the Provider tab on the Data Link Properties dialog box, select the Microsoft Office 16.0 Access Database Engine OLE DB Provider installed in Step 1.

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.

• • • Click Next or click the Connection tab.

• • • On the Connection tab, for Data Source, provide the full path to the location where either the .accdb or .mdb file resides.

Examples:

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

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

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

• • • Click Test Connection to verify that you can connect to the database.

• • • Click OK if the connection test was successful.

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

• • • Click OK on the Data Link Properties dialog box. The new OLE DB connection (.odc) appears under Databases in the Catalog pane.

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

Examples:

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

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

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

• • • From your Windows Start menu, in the Search window, type ODBC Data Source and select ODBC Data Sources (64-bit) to open the ODBC Data Source Administrator (64-bit).

• • • On the ODBC Data Source Administrator (64-bit) dialog box, on the User DSN tab, click Add.

• • • On the Create New Data Source dialog box, click MySQL ODBC 8.0 ANSI Driver.

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.

• • • On the MySQL Connector/ODBC Data Source Configuration dialog box, for Data Source Name (DSN), provide a unique name to associate this driver with the data source you want to access.

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.

• • • Ensure TCP/IP Server is selected and provide the name of the machine that contains the MySQL server, along with the Port #.
      Note: Port 3306 is the default port for the classic MySQL protocol (port), which is used by the MySQL client.

• • • Provide the User and Password credentials, select the name of the MySQL Database that you want to connect to, and click OK.

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

• • • The ODBC Data Source Administrator (64-bit) dialog box displays the Data Source Name text you specified, click OK.

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

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.

• • • Start ArcGIS Pro.

• • • From the Catalog pane, right-click the Databases folder and click New OLE DB Connection.

• • • From the Provider tab on the Data Link Properties dialog box, select the Microsoft OLE DB Provider for ODBC Drivers.

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.

• • • On the Connection tab, from the Use data source name drop-down list, select the DSN you created in step 2.

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.

• • • Click Test Connection to verify that you can connect to the database.

• • • Click OK if the connection test was successful.

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

• • • Click OK on the Data Link Properties dialog box. The new OLE DB connection (.odc) appears under Databases in the Catalog pane.

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

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

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.

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

• • Open tabular data
  • Sort records in a table
  • Filter data in the table
  • Hide columns in a table
  • Freeze columns in a table

Select records in tables

• • Select records in a table interactively
  • View all or only selected records
  • Highlight records in a selection set

Create table associations

• • Create a join
  • Create a relate
  • Select related records

Use data as input for geoprocessing tools

• • Add x,y data
  • Create an x,y event layer
  • Create a table view
  • Create a query table
  • Geocode a table of addresses
  • Export tables

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.

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:

• Discover the differences between mobile, file, and personal geodatabases.
• Access a new sample tool to help migrate a personal geodatabase to a file geodatabase.
• Access a new sample tool to help migrate a file geodatabase to a mobile geodatabase.

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