by VB-Kung-Fu (19 Submissions)
Category: Databases/Data Access/DAO/ADO
Compatability: VB 6.0
Difficulty: Unknown Difficulty
Originally Published: Tue 9th December 2003
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
Compares two tables and finds the fields in the first table that are not in the second table
VBcoders
'*******************************************************
'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
No comments have been posted about Compares two tables and finds the fields in the first table that are not in the second table. Why not be the first to post a comment about Compares two tables and finds the fields in the first table that are not in the second table.
