Comparing Tables
Daniel Dickinson
Hi all,
does anyone know the best way to compare the contents of two tables for
data entry verification ?
Thanks
Dan
Matt Townsend
Daniel,
There are long answers to that question. The short one, though, is to
use the macro wizard to compare tables or at least get the snakey part
of the SQL written. Then modify accordingly.
Its sort of an outer join with an 'is null' to show the mismatches. To
show only matches just do a regular join.
Hope this helps
matt townsend
Daniel Dickinson wrote:
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
9/8/97 PLEASE NOTE THE ADDRESS CHANGE
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Joe We are Borg Foster
In article <01bcbec0$9a1384e0$cb4ea4c2@daniel->, d...@imperial-tobacco.com (Daniel Dickinson) writes:
> Hi all,
> does anyone know the best way to compare the contents of two tables for
> data entry verification ?
> Thanks
> Dan
I have some way old (Access 2.0) code to compare two tables or
query result sets. CompareSchemas can see if the table structures
in two databases are the same or different. CompareTables
compares the data in two tables or SQL result sets, but you must
provide the primary key fields. The key field names must be
separated by Chr$(0), apparently the only easily remembered
character that absolutely, positively canNOT be part of a field
name, if there's more than one key field.
Sub CompareSchemas (DB1 As Database, DB2 As Database)
Dim t1 As Long, t2 As Long
Dim t2lo As Long, t2hi As Long
Dim f1 As Long, f2 As Long
ReDim Matched2Tables(0 To DB2.TableDefs.Count - 1) As Integer
' loop through DB1 looking for tables not in DB2
For t1 = 0 To DB1.TableDefs.Count - 1
t2lo = 0: t2hi = DB2.TableDefs.Count - 1
Do While (t2lo <= t2hi)
t2 = (t2lo + t2hi) \ 2
Dim R As Integer: R = StrComp(DB1.TableDefs(t1).Name, DB2.TableDefs(t2).Name, 1)
If (R = 0) Then Matched2Tables(t2) = True: Exit Do
If (R > 0) Then t2lo = t2 + 1 Else t2hi = t2 - 1
Loop
If (t2hi < t2lo) Then
Debug.Print "Table [" & DB1.TableDefs(t1).Name & "] not found in database 2"
Else
Dim TD1 As TableDef: Set TD1 = DB1.TableDefs(t1)
Dim TD2 As TableDef: Set TD2 = DB2.TableDefs(t2)
If (TD1.Attributes <> TD2.Attributes) Then
Debug.Print "Table [" & TD1.Name & "] attributes are different"
End If
If (((TD1.Attributes And DB_SYSTEMOBJECT) = 0) And ((TD2.Attributes And DB_SYSTEMOBJECT) = 0)) Then
ReDim Matched2Fields(0 To TD2.Fields.Count - 1) As Integer
For f1 = 0 To TD1.Fields.Count - 1
For f2 = 0 To TD2.Fields.Count - 1
If (TD1.Fields(f1).Name = TD2.Fields(f2).Name) Then Matched2Fields(f2) = True: Exit For
Next f2
If (f2 >= TD2.Fields.Count) Then
Debug.Print "Field [" & TD1.Name & "].[" & TD1.Fields(f1).Name & "] not found in database 2"
Else
If (TD1.Fields(f1).Type <> TD2.Fields(f2).Type) Then
Debug.Print "Field [" & TD1.Name & "].[" & TD1.Fields(f1).Name & "] type is different"
End If
If (TD1.Fields(f1).Size <> TD2.Fields(f2).Size) Then
Debug.Print "Field [" & TD1.Name & "].[" & TD1.Fields(f1).Name & "] size is different"
End If
If (TD1.Fields(f1).Attributes <> TD2.Fields(f2).Attributes) Then
Debug.Print "Field [" & TD1.Name & "].[" & TD1.Fields(f1).Name & "] attributes are different"
End If
If (TD1.Fields(f1).OrdinalPosition <> TD2.Fields(f2).OrdinalPosition) Then
Debug.Print "Field [" & TD1.Name & "].[" & TD1.Fields(f1).Name & "] position is different"
End If
End If
Next f1
For f2 = 0 To TD2.Fields.Count - 1
If (Not Matched2Fields(f2)) Then
Debug.Print "Field [" & TD2.Name & "].[" & TD2.Fields(f2).Name & "] not found in database 1"
End If
Next f2
End If
End If
Next t1
' loop through DB2 looking for tables not in DB1
For t2 = 0 To DB2.TableDefs.Count - 1
If (Not Matched2Tables(t2)) Then
Debug.Print "Table [" & DB2.TableDefs(t2).Name & "] not found in database 1"
End If
Next t2
End Sub
Sub CompareTables (DB1 As Database, ByVal Table1Name As String, DB2 As Database, Table2Name As String, ByVal Key As String)
Static KeyDelim As String: If (Len(KeyDelim) = 0) Then KeyDelim = Chr$(0)
Dim DS1 As Dynaset: Set DS1 = DB1.CreateDynaset(Table1Name)
Dim DS2 As Dynaset: Set DS2 = DB2.CreateDynaset(Table2Name)
Dim i As Integer, KeyField As String, KeyValue As Variant, KeyClause As String
' loop through DS1 looking for records not in DS2
If (Not DS1.BOF Or Not DS1.EOF) Then DS1.MoveFirst
While (Not DS1.EOF)
i = 1: KeyClause = "": Do
KeyField = Tokenize(Key, KeyDelim, i)
If (Len(KeyField) = 0) Then Exit Do
KeyValue = DS1(KeyField)
Select Case VarType(KeyValue)
Case V_CURRENCY, V_DOUBLE, V_INTEGER, V_LONG, V_SINGLE
KeyClause = KeyClause & IIf(i > 1, "And", "") & "([" & KeyField & "]=" & Str$(KeyValue) & ")"
Case V_DATE
KeyClause = KeyClause & IIf(i > 1, "And", "") & "([" & KeyField & "]=#" & Format$(KeyValue, DateFormat) & "#)"
Case V_EMPTY, V_NULL
KeyClause = KeyClause & IIf(i > 1, "And", "") & "([" & KeyField & "] Is Null)"
Case V_STRING
KeyClause = KeyClause & IIf(i > 1, "And", "") & "([" & KeyField & "]='" & StrReplaceAll(KeyValue, "'", "''") & "')"
Case Else
Stop
End Select
i = i + 1
Loop
DS2.FindFirst KeyClause
If (DS2.NoMatch) Then
Debug.Print "Key (" & KeyClause & ") not found in table 2"
Else
For i = 0 To DS1.Fields.Count - 1
If (IsNull(DS1(i)) And IsNull(DS2(i))) Then
' Consider two NULL values to be equal
ElseIf (IsNull(DS1(i)) Or IsNull(DS2(i)) Or DS1(i) <> DS2(i)) Then
Debug.Print "Records with key (" & KeyClause & ") don't match on field [" & DS1(i).Name & "]."
End If
Next i
End If
DS1.MoveNext
Wend
' loop through DS2 looking for records not in DS1
If (Not DS2.BOF Or Not DS2.EOF) Then DS2.MoveFirst
While (Not DS2.EOF)
i = 1: KeyClause = "": Do
KeyField = Tokenize(Key, KeyDelim, i)
If (Len(KeyField) = 0) Then Exit Do
KeyValue = DS2(KeyField)
Select Case VarType(KeyValue)
Case V_CURRENCY, V_DOUBLE, V_INTEGER, V_LONG, V_SINGLE
KeyClause = KeyClause & IIf(i > 1, "And", "") & "([" & KeyField & "]=" & Str$(KeyValue) & ")"
Case V_DATE
KeyClause = KeyClause & IIf(i > 1, "And", "") & "([" & KeyField & "]=#" & Format$(KeyValue, DateFormat) & "#)"
Case V_EMPTY, V_NULL
KeyClause = KeyClause & IIf(i > 1, "And", "") & "([" & KeyField & "] Is Null)"
Case V_STRING
KeyClause = KeyClause & IIf(i > 1, "And", "") & "([" & KeyField & "]='" & StrReplaceAll(KeyValue, "'", "''") & "')"
Case Else
Stop
End Select
i = i + 1
Loop
DS1.FindFirst KeyClause
If (DS1.NoMatch) Then
Debug.Print "Key (" & KeyClause & ") not found in table 1"
End If
DS2.MoveNext
Wend
DS1.Close : Set DS1 = Nothing
DS2.Close : Set DS2 = Nothing
End Sub
'Looks like CompareTables uses at least one of my custom string functions:
Function Tokenize (ByVal S As String, ByVal Delimiters As String, ByVal Which As Integer) As String
Static DefaultDelim As String
If (Len(DefaultDelim) = 0) Then DefaultDelim = " " & Chr$(13) & Chr$(10) & Chr$(9)
If (Len(Delimiters) = 0) Then Delimiters = DefaultDelim
Dim SLen As Integer: SLen = Len(S)
Dim pos As Integer: pos = 1
Dim InWord As Integer: InWord = False
Dim TokenStart As Integer: TokenStart = 1
Dim TokenLen As Integer: TokenLen = 0
Do While (Which > 0)
If (pos > SLen) Then Exit Do
If (InStr(1, Delimiters, Mid$(S, pos, 1), 0) = False) Then
InWord = True
If (Which <> 1) Then
' do nothing
ElseIf (TokenLen = 0) Then
TokenStart = pos
TokenLen = 1
Else
TokenLen = TokenLen + 1
End If
ElseIf (InWord) Then
InWord = False
Which = Which - 1
End If
pos = pos + 1
Loop
Tokenize = Mid$(S, TokenStart, TokenLen)
End Function
--
Joe Foster <mailto:j...@bftsi0.gate.net.SPAM.IS.IRRELEVANT.RESISTANCE.IS.FUTILE>
WARNING: I cannot be held responsible for the above They're coming to
because my cats have apparently learned to type. take me away, ha ha!