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.
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!
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.
-
-
- 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.exefile.
- Follow the Install Instructions provided to complete the process of locally installing the downloaded
-
-
-
- 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.
- To verify that the driver was installed successfully, in the Windows taskbar Search box, type
-
-
-
- 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.
- Ensure TCP/IP Server is selected and provide the name of the machine that contains the MySQL server, along with the Port #.
-
-
-
- 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
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:
- 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
Commenting is not enabled for this article.