[AccessD] Compare table structures

AD and Arthur,

The extract of sample code has references to a Form. Also, the code as shown
does not deal with indexes. Just a quick observation.
jack 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D.Tejpal
Sent: Thursday, February 12, 2009 12:32 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Compare table structures

Arthur,

    Interestingly, this very topic came up in MS_Access_Professionals group
at Yahoo today. Extract of the routine suggested by Clive Williams is placed
below.

    You might like to try it out. (It has not yet been tested at my end).

Best wishes,
A.D. Tejpal
------------

' Extract of sample code (by Clive Williams) to compare 
' two tables. It compares the FieldTypes then displays the 
' Filenames and FieldTypes in the DebugWindow:
'======================================
Private Sub cmdCompareTableStructures_Click()
' Compare the FieldTypes of the source table and the specified table
Dim db      As DAO.Database
Dim tbli    As DAO.TableDef
Dim tblj    As DAO.TableDef
Dim i       As Integer
Dim booMismatch   As Boolean
On Error GoTo Err_cmdCompareTableStructures_Click

   If vbYes = MsgBox("Comparing Table Structures. Stop, Yes/No", 
vbYesNo + vbDefaultButton2) Then Stop
   Set db = CurrentDb
   Set tbli = db.TableDefs(Me.RecordSource)

   Set tblj = db.TableDefs(Me.cboTables.Column(0)) (from ComboBox)
Debug.Print "Tables: "; tbli.Name & "  " & tblj.Name
   If tbli.Fields.Count <> tblj.Fields.Count Then
      MsgBox tbli.Name & " has " & tbli.Fields.Count & _
            " fields" & vbCrLf & vbCrLf & _
            tblj.Name & " has " & tblj.Fields.Count & _
            " fields." & vbCrLf & " Quitting..."
   Else   
      For i = 0 To tbli.Fields.Count - 1
         Debug.Print "Names", tbli.Fields(i).Name, _
             tblj.Fields(i).Name, ;
         Debug.Print "Types", tbli.Fields(i).Type, _
            tblj.Fields(i).Type
         If tbli.Fields(i).Type <> tblj.Fields(i).Type Then
            Debug.Print "", "", "", "   Mismatch>", "***", "***"
            booMismatch = True
         End If
      Next i
      If booMismatch Then
         DoCmd.RunCommand acCmdDebugWindow
      Else
         MsgBox "Compared Successfully."
      End If
   End If

Exit_cmdCompareTableStructures_Click:
   Set tblj = Nothing
   Set tbli = Nothing
   Set db = Nothing
   Exit Sub
Err_cmdCompareTableStructures_Click:
   MsgBox Err.Description
   Resume Exit_cmdCompareTableStructures_Click
End Sub
'========================================

  ----- Original Message ----- 
  From: Arthur Fuller 
  To: Access Developers discussion and problem solving 
  Sent: Thursday, February 12, 2009 15:48
  Subject: [AccessD] Compare table structures


  Is there a free tool that will compare two table structures and report the
  differences? I'd do it manually but in this case there are over 100 fields
  and it would be very tedious to do it by hand.

  TIA,
  Arthur
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com