Comparing databases

[DanWallace]

Hi there,

I need to write some code that will compare two (hopefully) identical databases for differences.

The main thing I want it to focus on is comparing record count and columns. It needs to check to make sure the column names are the same in both databases respective tables and that the record count is the same. I would like it to be dynamic in that I don't reference tables or column names in the code itself.

Does anyone have any suggestions to get me started on this project? How do I reference column names, access separate databases, etc.?

Thank you!

 

[Guus2005]

It's a start

Option Compare Database
Option Explicit

Public Sub CompareDatabase(strDatabasename1 As String, strDatabasename2 As String)
    Dim db1  As Database
    Dim db2  As Database
    Dim tdf1 As TableDef
    Dim tdf2 As TableDef
    Dim fld1 As Field
    Dim fld2 As Field
    
    Set db1 = OpenDatabase(strDatabasename1)
    Set db2 = OpenDatabase(strDatabasename2)

    'Compare tables
    For Each tdf1 In db1.TableDefs
        For Each tdf2 In db2.TableDefs
            If tdf1.Name = tdf2.Name Then 'Name match
                For Each fld1 In tdf1.Fields
                    For Each fld2 In tdf2.Fields
                        If fld1.Name = fld2.Name Then
                            'Fieldnames match
                            Exit For 'Next fld1
                        End If
                    Next fld2
                Next fld1
            End If
        Next tdf2
    Next tdf1
    

End Sub

This code loops through all tables and fields in both databases.

It is a start.

HTH

 

[boblarson]

Or perhaps this free tool will assist:
http://download.cnet.com/StarInix-Database-Compare/3000-10254_4-10614062.html

 

[DanWallace]

Re: It's a start

Thanks for the response!

How would I define the database names? Will it just check the directory that it's running from? If I have a database called data1.accdb and a database called data2.accdb in the same folder, will it find the right one if I pass those names? Do I need to include the extensions?

Option Compare Database
Option Explicit

Public Sub CompareDatabase(strDatabasename1 As String, strDatabasename2 As String)
    Dim db1  As Database
    Dim db2  As Database
    Dim tdf1 As TableDef
    Dim tdf2 As TableDef
    Dim fld1 As Field
    Dim fld2 As Field
    
    Set db1 = OpenDatabase(strDatabasename1)
    Set db2 = OpenDatabase(strDatabasename2)

    'Compare tables
    For Each tdf1 In db1.TableDefs
        For Each tdf2 In db2.TableDefs
            If tdf1.Name = tdf2.Name Then 'Name match
                For Each fld1 In tdf1.Fields
                    For Each fld2 In tdf2.Fields
                        If fld1.Name = fld2.Name Then
                            'Fieldnames match
                            Exit For 'Next fld1
                        End If
                    Next fld2
                Next fld1
            End If
        Next tdf2
    Next tdf1
    

End Sub

This code loops through all tables and fields in both databases.

It is a start.

HTH

 

[DanWallace]

Or perhaps this free tool will assist:
http://download.cnet.com/StarInix-Database-Compare/3000-10254_4-10614062.html

Looks like it doesn't support 2007 unfortunately. Most of my stuff is in 2007.

 

[Guus2005]

The databasenames should include a full path and an extension.