By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,863 Members | 853 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,863 IT Pros & Developers. It's quick & easy.

Table Field Names

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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

Nov 12 '05 #2

P: n/a
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
Nov 12 '05 #3

P: n/a
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

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.