How To: Join an archive class with another table in ArcGIS


Instructions provided describe how to join an archive class with another table in ArcMap or programmatically through the use of the IQueryDef interface.


  • Within ArcMap, an archive class can be opened with the add historical archive command on the geodatabase history toolbar or by using the Geodatabase History Viewer tool and setting specific moments of time. Once the class is present in the map, it can be used in ArcMap joins and relates like any other layer in an ArcMap document.
  • Programmatically, with ArcObjects a query def cursor can be created to join an archive class and another table in the database. This is accomplished by first obtaining the name of the archive class associated with the object class which is archive-enabled. Using the archive class name, it should be applied in the tables property of a query def. Next, depending on what is specified for the where clause property for the query def, a join can be preformed between the archive class and any other objects in the database.

    The code sample below demonstrates how to use a historical marker to obtain a timestamp, which is then used in the where clause of the query def object. The where clause is also used to establish the join condition between the archive class and other tables in the database.

    'Getting the timestamp from a historical marker which will be used in the where clause
    Set historicalVersion = historicalWorkspace.FindHistoricalVersionByName("MarkerName")
    time = historicalVersion.TimeStamp

    'Getting the name of the historical archive table
    Set featureClass = featureWorkspace.OpenFeatureClass("MAPGRID")
    Set archivedClass = featureClass
    Set archiveTable = archivedClass.Archive
    Set dataset = archiveTable
    archivedTableName = dataset.BrowseName

    'Creating the new query def and defining the where clause which uses the date and specifies the join condition
    Set QueryDef = featureWorkspace.CreateQueryDef
    QueryDef.Tables = archivedTableName & ",JoinTable"
    QueryDef.SubFields = ""
    QueryDef.WhereClause = archivedTableName & ".GDB_FROM_DATE <= '" & time & "' AND " & archivedTableName & ".GDB_TO_DATE > '" & time & "' AND " & archivedTableName & ".ID = JoinTable.ID "

    'Executing the join in the database and working with the cursor's result set
    Set rowCursor = QueryDef.Evaluate
    Set Row = rowCursor.NextRow