Access - compare headers on two tables

Question

I have two tables. Each should have the same headers. However, we find that one table will be missing a column (reports received by a 3rd party)

is there a way to check that all the headers on table 1 are on table 2? and vice versa?

I know how to do in excel, but wondered about doing in access

Answer 1

Ok, code like this should work:

Sub CheckTables()

    Dim db              As DAO.Database
    Set db = CurrentDb

    Dim sTable1         As String
    Dim sTable2         As String
    
    sTable1 = "tblHotelsB"
    sTable2 = "tblHotelsA"
    
    Dim Table1          As DAO.TableDef
    Set Table1 = db.TableDefs(sTable1)
    
    Dim Table2          As DAO.TableDef
    Set Table2 = db.TableDefs(sTable2)

    Dim cT1     As New Collection
    Dim cT2     As New Collection
    
    Dim f       As DAO.Field
    Dim f2      As DAO.Field
    
    
    For Each f In Table1.Fields
        On Error Resume Next
        Set f2 = Table2.Fields(f.Name)
        If Err.Number <> 0 Then
             cT1.Add (f.Name)
             Err.Clear
        End If
    Next
    
    For Each f In Table2.Fields
        On Error Resume Next
        Set f2 = Table1.Fields(f.Name)
        If Err.Number <> 0 Then
            cT2.Add (f.Name)
            Err.Clear
        End If
    Next
    
    
    Dim v           As Variant
    If cT1.Count = 0 And cT2.Count = 0 Then
         Debug.Print "Tables are same"
    Else
        If cT1.Count > 0 Then
            Debug.Print "Table1 is missing these fields from table2"
            For Each v In cT1
                Debug.Print "Table 1 missing field " & v
            Next
        End If
                 
        If cT2.Count > 0 Then
            Debug.Print "Table2 is missing these fields from table1"
             
            For Each v In cT2
                Debug.Print "Table 2 missing field " & v
             Next
        End If
    End If
    

End Sub

Output:

Table1 is missing these fields from table2
Table 1 missing field FullName

Table2 is missing these fields from table1
Table 2 missing field CreateDate
Table 2 missing field City_ID
Table 2 missing field Balcony
Table 2 missing field WaterView