In ArcGIS Pro, how do you make an OLE DB connection to a database like you can in ArcCatalog?

5407
18
09-24-2018 10:52 AM
CherylTrine
Occasional Contributor II

I am using ArcGIS Pro 2.2.1.  I have a non-spatial table in a MySQL database that I need to be able to join to a table from a file geodatabase.  In ArcCatalog this was done using a separate command (Add OLE DB Connection) and filling in the MySQL database info.  I've had it set up for years and it works fine (I don't edit the data, just read it).   How do I do it in ArcGIS Pro?

Cheryl

18 Replies
Robert_LeClair
Esri Frequent Contributor

Cheryl - in reviewing the Support Services website about OLE DB - I found this "ArcGIS Pro does not have the functionality to create OLE DB connections to connect to RDBMS databases."  Further I found  good thread on ArcGIS Ideas here.

CherylTrine
Occasional Contributor II

That is very disappointing! Why was that done?

I knew people were upset about something to do with MS Access databases and I had always assumed it had to do with Esri dropping support for personal geodatabases; because I don't use personal geodatabases I hadn't paid much attention.  I had not realized that OLE DB for ODBC connections were dropped in Pro! 

The MySQL data is used in real-time joins, so transforming and importing is not practical.  I only have one table that I have been using this way in GIS currently, but we were planning more.  I can see how other organizations could have many connections to such data and this could be quite a hardship.  Guess I'll have to stay with ArcMap until I find a solution, or this functionality is brought to Pro.  But I can't stay with ArcMap, because I need the Utility Network . . .

That thread is referring specifically to Microsoft Access (accdb) support.  If that were solved, would it solve my issue with MySQL?  If so, I'll add my use case, but I don't want to be off-topic.

Cheryl

MichaelVolz
Esteemed Contributor

How about bringing your MySQL data into Pro as a Query layer and then joining it to your GIS data?

0 Kudos
Robert_LeClair
Esri Frequent Contributor

It could be that OLE DB connections may be supported in the future for AGP.  Perhaps the developers have not yet added the functionality.  In addition to the query layer suggestion from Michael, would a database view work for the join?

0 Kudos
CherylTrine
Occasional Contributor II

 The ArcGIS Pro documentation says, "Before you can create a query layer, you must first make a connection to the database."  And there's the rub.   I expect the same is true for a database view. 

My SQL RDBMS experience is limited and I have not worked at all with MySQL;  Is there a way to create a query layer or view from the MySQL database that Pro can use that works more or less in real-time?

0 Kudos
Robert_LeClair
Esri Frequent Contributor

Hmmm...if your data is in a SQL Server RDBMS, you can make a Database connection to the database via AGP.  From there, open the DB connection in AGP, right click on the DB, select New->Database View and create the SQL statement to create the DB view for viewing (and maybe a join - haven't done that before with a DB View).  But if you're data is in MS Access, then we're back to square one.

0 Kudos
CherylTrine
Occasional Contributor II

Exactly! My geospatial data is in a SQL Server RDBMS and I have no problems making connections to it.  The non-spatial data that I'm trying to bring in comes from a  MySQL database over which I have no jurisdiction.

0 Kudos
Robert_LeClair
Esri Frequent Contributor

Understand and I presume there's no way to copy the MySQL tables over to the SQL Server RDBMS?

0 Kudos
MichaelVolz
Esteemed Contributor

Do you plan on using the file gdb fc with the joined table in a mapservice to be used in a webmap?

0 Kudos