Dabbling with Databases

Barry Gilchrist

Have you ever shipped a product that relies on Access/Jet databases, only to discover that a client has, um, "messed with" the database and inadvertently spoiled things? Barry Gilchrist’s compare utility is just the sort of utility that can save time. And if you don’t need it today, you might very well tomorrow.

Now that your application has been released and is being put to use by real end users, it’s time to relax, perhaps celebrate on a sun-soaked Caribbean island, and think leisurely about the next project. All of the code, database definition, and test data are safely backed up, and everyone on the team is quietly confident.

Unfortunately, just as you were starting to relax, the unwanted call comes through from your boss: Help Desk has taken more than a few puzzling calls from users who report data that isn’t being processed as it should, records in tables that shouldn’t be there, and so on.

Reluctantly, you cut your vacation short and return home. After sifting through the reports and trying to recreate the situation as described, you finally hit on the cause of the problems. Some of your end users have modified the supplied Access database. Nothing much, mind you–maybe a column type was accidentally changed, perhaps a cascade delete was disabled. "Mess with the database? Me? No, I wouldn’t do that."

Having assessed the situation and decided that the manager who didn’t think we should ship a protected database ("customers like ‘open systems’") should be given this month’s Dilbert award, guess who gets to mop up? You need to be able to identify the rogue databases and revise the application to prevent further tampering. The former might seem the more daunting, especially if the database is full of complex relationships, but a simple utility will be able to accomplish most of the required checks.

Maybe the preceding scenario is a little far-fetched, but during the lifetime of any database project, a great number of changes to the tables and relationships will occur. It’s often useful to be able to identify whether the databases in use by the developers and other members of the team–not to mention the end users–are in synch. In order to generate a utility to validate that one Access database is identical to another, you need to be able to show that all of the objects and relationships have the same characteristics. This is reasonably straightforward if you make use of the available DAO objects and properties for the Jet database within VB.

My utility checks the major components of an Access/Jet database structure: tables, fields, indexes, queries, and relationships. As with any programming task, there are a number of solutions that will accomplish the same results. For this example, I took a very simplistic approach and don’t expect any prizes for flair and ingenuity. I refer to the two databases as source and target.

Note that when checking that the target database structure matches that of the source database, you need to ensure that not only does the target database have all of the objects from the source database, but also that it doesn’t have any additional ones. An extra table might not cause any problems, but unexpected relationships can adversely affect the system. Any accidental renaming of tables or fields can be more easily identified if both objects in question are displayed.

Once the databases have been identified–and for the purposes of this article, I used two copies of the familiar Northwind database suitably modified (see Figure 1)–the process button begins the real work by first using the OpenDatabase() method to open each database and taking as many defaults as possible (see Listing 1). The utility then calls a number of routines, each of which verifies a certain area of the database as being sound. The steps involved for each of the areas are straightforward: Retrieve the information from each database, check for any differences, and then report on the differences.

Listing 1. Identify source and target databases and compare tables.

FirstTableCount = firstDB.TableDefs.Count
SecondTablecount = secondDB.TableDefs.Count
AndDisplay " == Tables == FirstDB contains " & _
   FirstTableCount & ", SecondDB contains " & _
   SecondTablecount
ReDim MatchedTableNames(FirstTableCount)
MatchedTableCount = 0
On Error Resume Next
InitProgress "Checking Tables from First DataBase", _
   FirstTableCount
For iFirst = 0 To FirstTableCount - 1
   Err.Clear
' Get next TableName to check from the first database
   tmpTableName = firstDB.TableDefs(iFirst).Name
   If (firstDB.TableDefs(iFirst).Attributes And _
      dbSystemObject) Then
   ' ignore if it's a system table
   Else
      ' Is this name in the second database?
      Set tmpTableDef = secondDB.TableDefs_
         (tmpTableName)
      If Err = 0 Then 'Okay, Table is in both databases
         MatchedTableNames(MatchedTableCount) = _
            tmpTableName
         MatchedTableCount = MatchedTableCount + 1
      Else
         If Not firstErr Then
            AndDisplay "Tables in " & firstDB.Name & _
               " but not in " & secondDB.Name
            firstErr = True
         End If
         AndDisplay "   " & tmpTableName
      End If
   End If
   MoveProgress iFirst
Next iFirst
HideProgress
firstErr = False
InitProgress "Checking Tables from Second DataBase", _
   SecondTablecount
For iFirst = 0 To SecondTablecount - 1
   Err.Clear
' Get next TableName to check from the second database
   tmpTableName = secondDB.TableDefs(iFirst).Name
   If (firstDB.TableDefs(iFirst).Attributes And _
      dbSystemObject) Then
   ' ignore if it's a system table
   Else
      ' Is this name in the second database?
      Set tmpTableDef = firstDB.TableDefs(tmpTableName)
      If Err = 0 Then 'Table is in both databases
      Else
         If Not firstErr Then
            AndDisplay "Tables in " & secondDB.Name _
               & " but not in " & firstDB.Name
            firstErr = True
         End If
         AndDisplay "   " & tmpTableName
      End If
   End If
   MoveProgress iFirst
Next iFirst
HideProgress

The first items to check are the tables (see Listing 2). You’ll notice the call to AndDisplay, which helps the user see what’s happening, and the three calls to initiate, move, and hide the progress bar–after all, on a large database, the system needs to show that it’s doing something.

Listing 2. Check the fields in each table.

Public Sub ProcessTableFields()
InitProgress "Checking Fields in each Table", _
   MatchedTableCount
For i = 0 To MatchedTableCount - 1
   tmpName = MatchedTableNames(i)
   tmpNum = firstDB.TableDefs(tmpName).Fields.Count
   ReDim firstFields(tmpNum - 1)
   Call LoadTableFields(firstDB, tmpName, firstFields, 
      tmpNum)
   tmpNum = secondDB.TableDefs(tmpName).Fields.Count
   ReDim secondFields(tmpNum - 1)
   Call LoadTableFields(secondDB, tmpName, _
      secondFields, tmpNum)
   Call CheckFieldsEqual(firstFields, secondFields)
   Call ReportFieldDifferences(tmpName, firstFields, 
      secondFields)
   MoveProgress i
Next i
HideProgress
End Sub
Public Sub LoadTableFields(aDb As Database, aName As _
   String, theFields() As myFields, numFields As Integer)
Dim i As Integer
For i = 0 To numFields - 1
   theFields(i).Name = aDb.TableDefs(aName)._
      Fields(i).Name
   theFields(i).fldType = aDb.TableDefs(aName)._
      Fields(i).Type
   theFields(i).Size = aDb.TableDefs(aName)._
      Fields(i).Size
   theFields(i).attr = aDb.TableDefs(aName)._
      Fields(i).Attributes
   theFields(i).Req = aDb.TableDefs(aName)._
      Fields(i).Required
   theFields(i).AllowZ = aDb.TableDefs(aName)._
      Fields(i).AllowZeroLength
   theFields(i).myFlag = False
Next i
End Sub
Public Sub CheckFieldsEqual(firstF() As myFields, _
   secondF() As myFields) 
' Now loop around the fields in each list to check their
' status using the first database as a starting point.
For jF = 0 To UBound(firstF)
  For jS = 0 To UBound(secondF)
     If ((firstF(jF).Name = secondF(jS).Name) And _
         (firstF(jF).fldType = secondF(jS).fldType) And _
         (firstF(jF).Size = secondF(jS).Size) And _
         (firstF(jF).attr = secondF(jS).attr) And _
         (firstF(jF).Req = secondF(jS).Req) And _
         (firstF(jF).AllowZ = secondF(jS).AllowZ)) Then
        firstF(jF).myFlag = True
        secondF(jS).myFlag = True
     End If
  Next jS
Next jF
End Sub
Public Sub ReportFieldDifferences(theTable As String, _
   firstF() As myFields, secondF() As myFields)
firstErr = False
' Now loop around the fields in each list to check 
' their status and report the differences.
For jF = 0 To UBound(firstF)
   If firstF(jF).myFlag Then ' OK
   Else 'Report difference
      If Not firstErr Then
        AndDisplay vbCrLf & firstDB.Name & " Table " _
          & theTable & " differs from other database ->"
        firstErr = True
      End If
      AndDisplay " * Field " & _
         firstF(jF).Name & " " & _
         ShowFull("FIELD", firstF(jF).fldType) & _
         "( " & firstF(jF).Size & ")" & _
         ", Attribute=" & ShowFull("ATTRIBUTE", _
            firstF(jF).attr) & ", Required=" & _
            firstF(jF).Req & ", AllowZero=" & _
            firstF(jF).AllowZ
   End If
Next jF
firstErr = False
For jS = 0 To UBound(secondF)
   If secondF(jS).myFlag Then ' OK
   Else 'Report difference
      If Not firstErr Then
        AndDisplay vbCrLf & secondDB.Name & " Table " _
           & theTable & " differs from other database ->"
        firstErr = True
     End If
     AndDisplay " * Field " & _
         secondF(jS).Name & " " & _
         ShowFull("FIELD", secondF(jS).fldType) & _
         "( " & secondF(jS).Size & ")" & _
         ", Attribute=" & ShowFull("ATTRIBUTE", _
            secondF(jS).attr) & ", Required=" & _
            secondF(jS).Req & ", AllowZero=" & _
            secondF(jS).AllowZ
   End If
Next jS
End Sub

Each table from the first (reference) database that exists in the second database will be recorded in the array MatchedTableNames. This allows the system to subsequently check field definitions for those tables that exist in both databases. Any table found to be a system object is excluded from the matched list. Any tables that don’t exist in the other database are identified and displayed in the results text box via the AndDisplay routine. At the end of the table-matching process, we’ll have an array of table names that exist in both databases. Each of these tables then needs to be visited and its fields inspected.

Listing 2 contains the basic stages that are required: a control routine, one to load the fields, one to verify them, and one to report on any differences. The type definition myFields (see Listing 3) is used to hold the information we’re checking within each field: the name of the field, type and size, attributes, mandatory and null (zero) statuses, and a flag to indicate whether this field has passed the test. An array of myFields is created for each table and exists until the checks and reports for that table are completed. There’s no real need to hold all of the information twice–after all, it exists in the database definition–but the array of myFields does help to show which elements are being validated.

Listing 3. Structure definition.

' Holds the name, type, size of the fields, and an 
' indicator to show which fields were matched.
Type myFields
  Name As String
  fldType As Integer
  Size As Integer
  attr As Integer
  myFlag As Boolean
  Req As Boolean
  AllowZ As Boolean
End Type

Once the field information for the table in question has been loaded from each database via LoadTableFields, the routine CheckFieldsEqual examines each of the fields from the first database with the ones from the second. When a match is made, the flags for both source and target are set to true. Any fields from the first or second database that didn’t match the other database will be displayed with an appropriate message. The internal numeric codes for field types and attributes are also expanded into English. As any unmatched item from each database is shown, you can identify the difference relatively easily. A further refinement would be to list only the item that’s different, rather than all of the items, as I do here.

The same basic principles are used when checking for other database objects, including indexes, queries, and relationships. The accompanying Download file contains sample code for all of these areas.

Wrap-up

This utility can be reduced or expanded to cater to your individual situation. You can extend the system to ensure that certain of your tables contain the correct meta data information–or that any forms, modules, and so on are indeed intact and haven’t been altered along with the database structure. Armed with the results of the compare process, you’re in a much better position to forward the reported problem to the developers as a real issue with the code–or call the client back and tell him exactly where he didn’t mess with the database!

Download COMPARE.exe