how to compare two tables for changes?

[axilla2]

I'm wondering if there's a clever way within Access or outside access, to compare two tables for any changes.

Take "Compress & Repair" scenario.. I did that earlier today and found that one record got lost in the process. For what reason I dont know.

There are free 'Excel compare' tools out there, i'm not impressed. Hoping you know what i'm talking about and have a clever query in Access that does a similar job.

thanks-

 

[hennep]

I had to do the same comparison for some project last week.
Hennie

See my code:

Dim rs As Recordset, fd As Field
Set rs = CurrentDb.OpenRecordset("Table1", dbOpenDynaset)
For Each fd In rs.Fields
If fd.Name <> &quot;lngPrimaryKey&quot; Then
DoCmd.RunSQL _
&quot; INSERT INTO tblDifferences ( lngPrimaryKey, FieldName, Value1, Value2 )&quot; _
& &quot; SELECT Table1.Salarisnummer&quot; _
& &quot; , '&quot; & fd.Name & &quot;' AS Expr1&quot; _
& &quot; , '' & [Table1].[&quot; & fd.Name & &quot;] AS Expr2&quot; _
& &quot; , '' & [Table2].[&quot; & fd.Name & &quot;] AS Expr3&quot; _
& &quot; FROM Table1 INNER JOIN Table2 ON Table1.lngPrimaryKey = Table2.lngPrimaryKey&quot; _
& &quot; WHERE [Table1].[&quot; & fd.Name & &quot;] <> [Table2].[&quot; & fd.Name & &quot;]&quot; _
& &quot;;&quot;
End If
Next fd
rs.Close
Set rs = Nothing

 

[hennep]

One BUG, &quot;Salarisnummer&quot; should be &quot;lngPrimaryKey&quot;