Table Field Names

[Tom]

Data is collected over time in an Excel worksheet with 20 columns. The Excel
worksheet starts out as a copy of a template as is filled in as data is
collected. Eventually the worksheet file is saved and an Access table is linked
to the Excel worksheet. Different people collect the data in the Excel
worksheet than who use the database.

The fields in the table are the labels in the first row of the worksheet which
is what I want. The fields in the table also must be the same field names as
what was in the original Excel template and the order of the fields in the table
must be the same as the order of the columns in the original Excel template. I
need to build a routine in Access that cycles through the field names in the
linked table to check that the field names were not changed from the original
names in the template and the order of the fields were not changed from the
order of the columns in the original Excel template by the people collecting the
data.

How do I cycle through the field names in the table?

Thanks!

Tom

[Phobos]

Dim and Set your recordset

For each fld in rs.fields
Debug.print fld.name
next
"Tom" <th*******@bellsouth.net> wrote in message
news:fz****************@newsread2.news.atl.earthli nk.net...

Data is collected over time in an Excel worksheet with 20 columns. The Excel worksheet starts out as a copy of a template as is filled in as data is
collected. Eventually the worksheet file is saved and an Access table is linked to the Excel worksheet. Different people collect the data in the Excel
worksheet than who use the database.

The fields in the table are the labels in the first row of the worksheet which is what I want. The fields in the table also must be the same field names as what was in the original Excel template and the order of the fields in the table must be the same as the order of the columns in the original Excel template. I need to build a routine in Access that cycles through the field names in the linked table to check that the field names were not changed from the original names in the template and the order of the fields were not changed from the order of the columns in the original Excel template by the people collecting the data.

How do I cycle through the field names in the table?

Thanks!

Tom

[Pieter Linden]

Tom,

This should work... if you're using a version of Access later than 97,
you'll need a reference to DAO 3.6 (Open VB IDE, Tools, References...)

Public Function FieldsMatch(ByVal strTable1 As String, ByVal strTable2
As String) As Boolean

Dim db As DAO.Database
Dim tdf1 As DAO.TableDef
Dim tdf2 As DAO.TableDef
Dim fld As DAO.Field
Dim intCounter As Integer

Set db = CurrentDb
Set tdf1 = db.TableDefs(strTable1) 'Access Table
Set tdf2 = db.TableDefs(strTable2) 'XLS table

If tdf1.Fields.Count <> tdf2.Fields.Count Then
'Different number of fields. So fail and exit here.
Debug.Print "Different number of fields!"
FieldsMatch = False
Else

FieldsMatch = True 'assume success... if test fails, switch
to false.

For intCounter = 0 To tdf1.Fields.Count - 1
If tdf1.Fields(intCounter).Name =
tdf2.Fields(intCounter).Name Then
'--Field Counts match so now check names.
Else
'--DOesn't match, so fail
FieldsMatch = False
Exit For
End If
Next intCounter
End If

Set fld = Nothing
Set tdf2 = Nothing
Set tdf1 = Nothing
Set db = Nothing

End Function

The function will return True if the two tables have identical field
names, and they're in the same order. Otherwise, it will fail (return
False).

HTH,
Pieter

[Tom]

Pieter,

Thanks a lot!!!

Tom
"Pieter Linden" <pi********@hotmail.com> wrote in message
news:bf**************************@posting.google.c om...

Tom,

This should work... if you're using a version of Access later than 97,
you'll need a reference to DAO 3.6 (Open VB IDE, Tools, References...)

Public Function FieldsMatch(ByVal strTable1 As String, ByVal strTable2
As String) As Boolean

Dim db As DAO.Database
Dim tdf1 As DAO.TableDef
Dim tdf2 As DAO.TableDef
Dim fld As DAO.Field
Dim intCounter As Integer

Set db = CurrentDb
Set tdf1 = db.TableDefs(strTable1) 'Access Table
Set tdf2 = db.TableDefs(strTable2) 'XLS table

If tdf1.Fields.Count <> tdf2.Fields.Count Then
'Different number of fields. So fail and exit here.
Debug.Print "Different number of fields!"
FieldsMatch = False
Else

FieldsMatch = True 'assume success... if test fails, switch
to false.

For intCounter = 0 To tdf1.Fields.Count - 1
If tdf1.Fields(intCounter).Name =
tdf2.Fields(intCounter).Name Then
'--Field Counts match so now check names.
Else
'--DOesn't match, so fail
FieldsMatch = False
Exit For
End If
Next intCounter
End If

Set fld = Nothing
Set tdf2 = Nothing
Set tdf1 = Nothing
Set db = Nothing

End Function

The function will return True if the two tables have identical field
names, and they're in the same order. Otherwise, it will fail (return
False).

HTH,
Pieter