How can I get Microsoft Access queries to appear in ArcCatalog?

[EH_Alaska]

Hello Everyone!

I'm attempting to make a connection between an MS Access database and a shapefile in ArcMap. My current plan is this:

• Export data from our software to MS Access.
• Use a query in that Access database to aggregate the fields we need.
• Join the fields in this query to our current polygon shapefile.

Everything was going well until I got the the last step. The query that I built in Access is not visible in ArcCatalog; I can only see the tables. I'm not sure where to go from here. Is there a way to get queries to show in ArcCatalog? Or even a better way to accomplish my goal?

Thanks in advance!

[RhettZufelt · Accepted solution]

Is there a reason the table needs to be dynamic?

Could go into the query in Access in design mode, change to Make Table query and run it.

Now, there will be a table that you can load into ArcMap/Catalog and join on.

R_

[EH_Alaska]

This worked perfectly, thank you! I've used Access for 6+ years and never had to use (or even know about) "Make Table". Once I did that, the table showed up in ArcMap and I joined it to the polygon file without issue. I'll still have to open the Access DB and run the query each time I need to update the file, but that's so much better than what I've been doing. So thank you so much!

[JohnCodd]

Hi All,

What about where Esri states this..."...Access queries are available."   On HERE

"Note: Linked tables in Access are not viewable through an OLE DB connection, although Access queries are available. To use the linked table, create a query in Access that references the linked table and connect to the linked table through the query in the OLE DB connection.

Because OLE DB connections do not have an Object ID field, which is a field maintained by ArcGIS that guarantees a unique ID for each row in the table, you cannot perform certain operations in ArcMap. These include selecting the features in the layer on the map in any way, applying a definition query, creating full relates, or editing the attributes. For more information about working with tables that lack Object IDs, see Fundamentals of Object ID fields.

[curtvprice]

How are you connecting to Access? If you just are double clicking and opening the .mdb file from the catalog this won't work with queries.  To access a query you must set up an ODBC connection.  This is the safest way to connect, because directly opening the mdb file from ArcMap/ArcCatalog can add geodatabase cruft to your MS Access database.

You can use tables from a Microsoft Access database in ArcMap through an OLE DB connection. OLE DB is a standard for sharing data between applications, enabling you to view the Access database in ArcMap. To maintain data integrity, you should only modify an Access database in Access.

All Microsoft Access databases (.mdb) are shown and you can browse to them using ArcCatalog or the Catalog window. You should only browse to and add data directly from personal geodatabases (a type of Microsoft Access database), and use OLE DB connections to nongeodatabase Microsoft Access databases.

ArcMap 10.8 Help: Connect to a Microsoft Access database (.mdb) in ArcGIS

[JohnCodd]

Hi Curt:  Esri says the OLE DB connection allows seeing/using Access Queries.  I have that connection made, but only see my tables.  Any thoughts on this?  

Thanks, 

John

[RobertBorchert]

No there is not. I have wanted MS Access functionality in ArcCatalog ever since File Geodatabases were born.

Just to clarify, when you say MS Database are you creating a Personal Geodatabase with ArcCatalog and using it in MS Access. 

I have quite often performed queries and data updates in MS Access  as it is often quite easier. 

There is an option to look at the SQL coding for a query but it does not translate properly. 

[EH_Alaska]

Hey Robert,

I'm just using the tables in the .mdb to join to a shapefile in order to associate the data with its location. I ended up using Rhett's suggestion higher up in this thread and it worked perfectly for what I needed!