Comparing the Stru of 2 Different Tables

  • Thread starter Thread starter Mike Thomas
  • Start date Start date
M

Mike Thomas

In Access 2000, what would be the best way to compare the structures of two
tables which should always have the same structure? Id like to compare for
field name and data type.

This is a case of inserting records from a temp table into an archive table
using the * character.

INSERT INTO InvoiceArchive
SELECT xTemp.*
FROM xTemp;

It is a new routine, and I am sure the stru of the temp table which feeds
into the archive table will change in the next year, and that I will forget
to change the archive table at some point. I want to be sure the stru of
the archive table is in sync, or else put up a message that the archive
table must be checked and modified before inserting the contents of the temp
table.

Many thanks
Mike Thomas
 
Here's a little function that I just put together. It only checks that the
tables have the same number of fields, that they have the same names (in the
same order), and the datatypes of each one. You could obviously extend to
check just how exact you want the match to be.

Function CompTable(tbl1str As String, tbl2str As String) As Boolean
Dim db As Database
Dim tbl1 As TableDef, tbl2 As TableDef
Dim fld1 As Field, fld2 As Field
Dim i As Integer
Set db = CurrentDb
Set tbl1 = db.TableDefs(tbl1str)
Set tbl2 = db.TableDefs(tbl2str)
CompTable = True
If tbl1.Fields.Count <> tbl2.Fields.Count Then
CompTable = False
Else
For i = 0 To tbl1.Fields.Count - 1
Set fld1 = tbl1.Fields(i)
Set fld2 = tbl2.Fields(i)
If fld1.Name <> fld2.Name Then
CompTable = False
Exit Function
End If
If fld1.Type <> fld2.Type Then
CompTable = False
Exit Function
End If
Next
End If
Set db = Nothing
End Function
 
Back
Top