Compare Two Databases Table by Table and Field by Field 3

123FakeSt · Sep 21, 2005

I'm trying to complete a function that will create a report of any differences between two versions of the same database in terms of table structure.

It will report new or discontinued tables, and for tables that remain it will compare the tables field by field to make sure the fields and data types are consistent.

This is what I have so far. I was hoping someone would have a better idea so I don't end up having three databases open simultaneously to compare fields.

Code:

Public Function CompareDatabaseTables(strDatabaseMaster As String, strDatabaseProtege As String) As String

Dim strSQL As String
Dim rst1 As ADODB.Recordset
Dim rst2 As ADODB.Recordset
Dim strReport As String
Set rst1 = New ADODB.Recordset
Set rst2 = New ADODB.Recordset

Dim db1 As DAO.Database
Dim tdf1 As DAO.TableDef
Dim fld1 As DAO.Field
Dim db2 As DAO.Database
Dim tdf2 As DAO.TableDef
Dim fld2 As DAO.Field

'Loop through all tables in the Master Database, for each table
strSQL = "SELECT * FROM msysobjects IN """ & strDatabaseMaster & """" & _
        " WHERE type IN (1,6)"
rst1.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockReadOnly
If Not rst1.EOF Then
    rst1.MoveFirst
    Do Until rst1.EOF
        'Check if the table exists in the Protege Database
        strSQL = "SELECT * FROM msysobjects IN """ & strDatabaseProtege & """ WHERE type IN (1,6) AND name = """ _
                & rst1!name & """"""
        rst2.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockReadOnly
        If rst2.EOF Then
            'If not report it
            strReport = rst!name & " does not exist in protege." & Chr(13)
        Else
            'If it does
            rst2.MoveFirst
                'Loop through all of the fields in the Master Table
                    'Check if the field exists in the Protege Database
                    'If not Report it
                    'If it does Compare the data types to see if they match
                        'If they do continue
                        'If they don't report it
                'Loop through all of the fields in the Protege Table
                    'Check if the field exists in the master
                        'If it doesn't report it
                        'If it does continue
        End If
        rst2.Close
        rst1.MoveNext
    Loop

'Loop through all of the tables in the Protege Database, for each table
    'Check if the table exists in the Master Database
        'If not report it
        'If it does continue
End If
Set rst1 = Nothing
End Function

The early bird gets the worm, but the second mouse gets the cheese.

MichaelRed · Sep 23, 2005

I would S T R O N G L Y suggest that you look int the resources available in the various MSys* objects (USUALLY HIDDEN table type objects in JET) and a part of the deffinition of a relational database. While there is somewhat of a learning curve associated with these, the reward it the ease and speed of obtaining the results you want -after you understand them.

As a SIMPLLISTIC and trivial example:

Instantiace this in a database which has a "close Kin". Use the File -> get external data to add a like to the "Kissing Cousin" dbs' MSysObjects Table.

SELECT MSysObjects.Name, MSysObjects1.Name
FROM MSysObjects LEFT JOIN MSysObjects1 ON MSysObjects.Name = MSysObjects1.Name
WHERE (((MSysObjects1.Name) Is Null))
WITH OWNERACCESS OPTION;

If you have done it properly. you will get a list of objects in the "Native" db which have no exact (name) counterpart in the "Kissiing Cousin" db.

From that point, you need to liik into the remaining info stores available and deduce the logic to get what ever you are interested in.

MichaelRed

AlanJordan · Sep 23, 2005

Hi,

What you want to do is fairly complicated, but you just lucked out. Some time ago I wrote an Access97 program that does what you need.

You can probably run it with no problem if you do *not* convert the database to the latest version of Access. Or, if you do, there will probably be a few conversion errors that you'll need to resolve.

Also, you'll need to place the program in a particular directory C:\AuditIt! (or change the path in the program.) You also need to have the folder Logs in the same directory as the program. All of that said, I think this will save you at least a week of work.

Here is a link to the zip file, which I have uploaded to my server

If you do update or improve the program, all that I ask is that you create a link to the improved version and upload that link to this site.

http://www.alanjordan.com/TekTips/AuditIt3.zip

Oh, and please do *not* sell the program.

Sincerely,

Alan Jordan

123FakeSt · Sep 26, 2005

Thanks for the input. I went ahead and coded it myself though it is pretty ugly. It's just a one time thing for now so I'll probably just use it as is.

Code:

Public Function CompareDatabaseTables(strDatabaseMaster As String, strDatabaseProtege As String)
'Compares two Access Databases and writes discrepencies to "H:\CompareDatabaseLog.txt"
Dim strSQL As String
Dim blnFound As Boolean
Dim rst1 As ADODB.Recordset
Dim rst2 As ADODB.Recordset
Set rst1 = New ADODB.Recordset
Set rst2 = New ADODB.Recordset

Dim objFSO As Object
Dim objLogFile As Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objLogFile = objFSO.CreateTextFile("H:\CompareDatabaseLog.txt", True)

Dim db1 As DAO.Database
Dim fld1 As DAO.Field
Dim db2 As DAO.Database
Dim fld2 As DAO.Field
Set db1 = DBEngine.Workspaces(0).OpenDatabase(strDatabaseMaster, True)
Set db2 = DBEngine.Workspaces(0).OpenDatabase(strDatabaseProtege, True)

'Loop through all tables in the Master Database, for each table
strSQL = "SELECT * FROM msysobjects IN """ & strDatabaseMaster & """" & " WHERE type IN (1,6) AND name NOT LIKE ""MSys*"""
rst1.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockReadOnly
If Not rst1.EOF Then
    rst1.MoveFirst
    Do Until rst1.EOF
        If Left(rst1!name, 4) <> "MSys" Then
            'Check if the table exists in the Protege Database
            strSQL = "SELECT * FROM msysobjects IN """ & strDatabaseProtege & _
                    """ WHERE type IN (1,6) AND name = """ & rst1!name & """"
            rst2.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockReadOnly
            If rst2.EOF Then
                'If not report it
                objLogFile.WriteLine rst1!name & " does not exist in protege." & Chr(13)
            Else
                'If it does Loop through all of the fields in the Master Table
                For Each fld1 In db1.TableDefs(rst1!name).Fields
                    'Check if the field exists in the Protege Database
                    blnFound = False
                    For Each fld2 In db2.TableDefs(rst1!name).Fields
                        If fld1.name = fld2.name Then
                            'If it does Compare the data types
                            If fld1.Type <> fld2.Type Then
                                objLogFile.WriteLine rst1!name & "." & fld1.name & " has type " & fld2.Type & _
                                        " instead of " & fld1.Type & Chr(13)
                            End If
                            blnFound = True
                        End If
                    Next
                    'If not found report it
                    If Not blnFound Then
                        objLogFile.WriteLine rst1!name & "." & fld1.name & " does not exist in Protege." & Chr(13)
                    End If
                Next
                'Loop through all of the fields in the Protege Table
                For Each fld2 In db2.TableDefs(rst1!name).Fields
                    'Check if the field exists in the master
                    blnFound = False
                    For Each fld1 In db1.TableDefs(rst1!name).Fields
                        If fld2.name = fld1.name Then
                            blnFound = True
                        End If
                    Next
                    'If not found report it
                    If Not blnFound Then
                        objLogFile.WriteLine rst1!name & "." & fld2.name & " does not exist in Master." & Chr(13)
                    End If
                Next
            End If
            rst2.Close
        End If
        rst1.MoveNext
    Loop
End If
rst1.Close

'Loop through all of the tables in the Protege Database, for each table
strSQL = "SELECT * FROM msysobjects IN """ & strDatabaseProtege & """" & " WHERE type IN (1,6)"
rst1.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockReadOnly
If Not rst1.EOF Then
    rst1.MoveFirst
    Do Until rst1.EOF
        'Check if the table exists in the Protege Database
        strSQL = "SELECT * FROM msysobjects IN """ & strDatabaseMaster & _
                """ WHERE type IN (1,6) AND name = """ & rst1!name & """"
        rst2.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockReadOnly
        If rst2.EOF Then
            objLogFile.WriteLine rst1!name & " does not exist in master." & Chr(13)
        End If
        rst2.Close
        rst1.MoveNext
    Loop
rst1.Close
End If

objLogFile.Close
Set objLogFile = Nothing
Set objFSO = Nothing
Set rst2 = Nothing
Set rst1 = Nothing
Set fld1 = Nothing
Set fld2 = Nothing
Set db1 = Nothing
Set db2 = Nothing

End Function

The early bird gets the worm, but the second mouse gets the cheese.

MichaelRed · Sep 26, 2005

ACtually, seems like a trasonable start. You might just look into doing the compares via queries as opposed to the "brut force" code in the loops. But the overall concept of using the MSys* tables at least starts on the "right" foot.

MichaelRed

123FakeSt · Sep 27, 2005

Yes I actually indicated I was using the Msys* in the original post ... I agree that queries would be a more elegant solution. What was knew to me was using the tabledefs object. Next time i'll make it better...

Thanks!

The early bird gets the worm, but the second mouse gets the cheese.

Dor100 · Sep 27, 2005

I can hardly wait to have more time to implement this. Great thread.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Compare Two Databases Table by Table and Field by Field 3

123FakeSt

IS-IT--Management

MichaelRed

Programmer

AlanJordan

Programmer

123FakeSt

IS-IT--Management

MichaelRed

Programmer

123FakeSt

IS-IT--Management

Dor100

Technical User

Similar threads

Part and Inventory Search

Sponsor