How do I create a relate to an OLE DB connected table (one to many)?

wightman_akibby

We have a client that is trying to integrate some utility information into their parcel dataset from an external database.  This will be a one (parcel) to many (utility customers) relationship.  We have successfully created an OLE database connection to this SQL database table.  When I try to execute the relate, I get a grayed-out dialog in the attribute table. 

After doing some research, i am guessing this is caused because my SQL table does not have an ObjectID field.  How do I get around this?  Can I generate an ObjectID field in the SQL table?  I have exported the table to a DBF and the relate works just fine, but I am trying to avoid that extra step of exporting the original table. 

Any thoughts?

Please and thank you.    

JoshuaBixby

The lack of an ObjectID field, in and of itself, doesn't prevent the user from relating a table.  As the Essentials of relating tables states:

If you relate to a table that does not have an ObjectID column (such as delimited text files or OLE DB tables), you will not be able to apply selections using the relate. The Related Tables command from the Table window's Table Options menu will list the relate, but it will be unavailable. The Identify tool, however, can still be used to find related records.

If you are being prevented from making a relate, it isn't the lack of an ObjectID field that is causing the issue.

If you want to have an ObjectID field so there is more functionality with your relates, you don't have to add one to the other table.  Several Esri tools work with dynamic ObjectIDs, i.e., they create a dynamic/non-permanent ObjectID field.  If you are working with OLE DB connection, Make Query Table (Data Management) should work.  Assuming you have a primary key for the table, the default option (USE_KEY_FIELDS) will work.

Although Make Query Table and OLE DB connections should work for you, they are both older tools or ways of working with data.  Query Layers (not just for spatial data) would be a more current approach to working with the data, and Make Query Layer (Data Management) also supports dynamic ObjectIDs.