Check for identical fields in tables (before appending)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How can I check that two tables have the identical fields before I attempt to
append? The append fails if tables don't have identical fields.
 
jsccorps said:
How can I check that two tables have the identical fields before I
attempt to append? The append fails if tables don't have identical
fields.
Click to expand...

Here's a quickie function you might use or adapt for your purposes. Be
warned: it's only had the lightest of testing.

'----- start of code -----
Function TableDefsAreCompatible( _
pstrTable1 As String, _
pstrTable2 As String) _
As Boolean

' Return True if the tables named by <pstrTable1> and <pstrTable2>
' have all fields of the same names, types, and sizes, in the same
' order. Return False if not, or if an error is encountered.

On Error GoTo Err_Handler

Dim tdf1 As DAO.TableDef
Dim tdf2 As DAO.TableDef
Dim fld2 As DAO.Field
Dim db As DAO.Database
Dim intFld As Integer

Set db = CurrentDb

Set tdf1 = db.TableDefs(pstrTable1)
Set tdf2 = db.TableDefs(pstrTable2)

If tdf1.Fields.Count <> tdf2.Fields.Count Then
TableDefsAreCompatible = False
Else
' Innocent until proven guilty.
TableDefsAreCompatible = True

For intFld = 0 To (tdf1.Fields.Count - 1)

Set fld2 = tdf2.Fields(intFld)

With tdf1.Fields(intFld)
If fld2.Name <> .Name Then
TableDefsAreCompatible = False
Exit For
End If
If fld2.Type <> .Type Then
TableDefsAreCompatible = False
Exit For
End If
If fld2.Size <> .Size Then
TableDefsAreCompatible = False
Exit For
End If
End With

Next intFld

End If


Exit_Point:
On Error Resume Next
Set tdf1 = Nothing
Set tdf2 = Nothing
Set db = Nothing
Exit Function

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point

End Function
'----- end of code -----
 
Back
Top