Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Compare 2 tables (Access 2000)

    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Compare 2 tables (Access 2000)

    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.

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare 2 tables (Access 2000)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Compare 2 tables (Access 2000)

    See attached text file.

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare 2 tables (Access 2000)

    Excellent!

    Because I

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    631
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare 2 tables (Access 2000)

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

    or
    http://www.matpie.drw.net/PBSystems/produc...ed/MDBDiff.html
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •