• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• Compares two tables and finds the fields in the first table that are not in the second table

Compares two tables and finds the fields in the first table that are not in the second table

Compares two tables and finds the fields in the first table that are not in the second table

Rate Compares two tables and finds the fields in the first table that are not in the second table

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

'*******************************************************
'FindMissingFields
'   Returns a list of fields that are in TblName1 and not
'   in TblName2.
'*******************************************************
Public Function FindMissingFields(TblName1 As String, TblName2 As String, Optional Delimeter As String = vbCrLf)
Dim tdf As TableDef, tdf1 As TableDef, tdf2 As TableDef, fld As Field
Dim n As Long, res As String, FieldFound As Boolean

If TblName1 = "" Or TblName2 = "" Or StrComp(TblName1, TblName2, vbTextCompare) = 0 Then Exit Function

'find the table definitions
For Each tdf In CurrentDb.TableDefs
    If StrComp(tdf.Name, TblName1, vbTextCompare) = 0 Then
        Set tdf1 = tdf
    ElseIf StrComp(tdf.Name, TblName2, vbTextCompare) = 0 Then
        Set tdf2 = tdf
    End If
Next

'now find the fields in the first table that are in
'not in the second one
If tdf1 Is Nothing = False And tdf2 Is Nothing = False Then
    For Each fld In tdf1.Fields
        'assume the field doesnt exist in the second table
        'then confirm this
        FieldFound = False
        
        For n = 0 To tdf2.Fields.Count - 1
            If StrComp(fld.Name, tdf2.Fields(n).Name, vbTextCompare) = 0 Then
                FieldFound = True
                Exit For
            End If
        Next
        
        If FieldFound = False Then
            'make note of the field since its missing from the second table
            res = res & fld.Name & Delimeter
        End If
    Next
End If

'strip off trailing delimeter when returning result
If Len(res) > 1 Then
    FindMissingFields = Left(res, Len(res) - Len(Delimeter))
End If
End Function