Compare 2 tables (Access 2000)

Topic

New Reply

Sometimes i have 2 identical tables but i need a simple means to compare whether these 2 tables are really identical just
to establish didnt i miss some field in them, and if some field is missing, wich one.
Is there such a function?

Reply | Quote

Replies

Here is a procedure that will compare two tables and list the non-matching field names in the immediate window:

Sub CompareTables(strTable1 As String, strTable2 As String)
Dim dbs As DAO.Database
Dim tdf1 As DAO.TableDef
Dim tdf2 As DAO.TableDef
Dim fld1 As DAO.Field
Dim fld2 As DAO.Field

On Error GoTo ErrHandler

Set dbs = CurrentDb
Set tdf1 = dbs.TableDefs(strTable1)
Set tdf2 = dbs.TableDefs(strTable2)

On Error Resume Next

For Each fld1 In tdf1.Fields
Set fld2 = tdf2.Fields(fld1.Name)
If Not Err = 0 Then
Debug.Print fld1.Name & ” does not occur in ” & strTable2
End If
Err = 0
Next fld1

For Each fld2 In tdf2.Fields
Set fld1 = tdf1.Fields(fld2.Name)
If Not Err = 0 Then
Debug.Print fld2.Name & ” does not occur in ” & strTable1
End If
Err = 0
Next fld2

ExitHandler:
Set fld2 = Nothing
Set fld1 = Nothing
Set tdf2 = Nothing
Set tdf1 = Nothing
Set dbs = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub

To compare two tables named Contacts1 and Contacts2, activate the Immediate window (Ctrl+G) and type

CompareTables “Contacts1”, “Contacts2”

then press Enter.

Reply | Quote

This is good when developing a system that several customers are already using to keep track of changes that occur on the fly.

How would adjust above code to report difference in field size and data type in addition to reporting the field name?

Thanks, John

Reply | Quote

See attached text file.

Reply | Quote

Excellent!

Because I

Reply | Quote

I have found MDBDiff to be invaluable for comparing the structures of two databases;
http://www.downlinx.com/proghtml/72/7220.htm%5B/url%5D

or
http://www.matpie.drw.net/PBSystems/produc…ed/MDBDiff.html%5B/url%5D

Reply | Quote