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 3 18497
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
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: toedipper |
last post by:
Hello,
I am designing a table of vehicle types, nothing special, just a list of
unique vehicle types such as truck, lorry, bike, motor bike, plane, tractor
etc etc
For the table design I am...
|
by: Dirk Van de moortel |
last post by:
Hi,
We have a database with some tables with (what I would
denote as) 'referred field names'.
Like this:
DataTable1 with fields F1, F2, F3
DataTable2 with fields F3, F4, F5
DataTable3 with...
|
by: Randy |
last post by:
Access= 2002
I'm NOT a Programmer, but I have used VB in the past to do some things
( Spaghetti Code King) so I have some understanding of Coding
I need to replace a text field (teacher) in...
|
by: bitoulis |
last post by:
Hi,
is it possible to use the records of a table as the field names of
another table?
If yes, how is it done?
Thanks in advance
Laertes
|
by: jjturon |
last post by:
Can anyone help me??
I am trying to pass a Select Query variable to a table using Dlookup
and return the value to same select query but to another field.
Ex.
SalesManID ...
|
by: Senna_Rettop |
last post by:
Hello,
I'm new at Access and ran into a problem. I have a table with a
field for customer's names. I want to make a lookup field out of the
names by linking it to a table that holds all the...
|
by: Tim Hunter |
last post by:
Hi I am using WinXP and Access 2003
Is it possible to store the field names of a table in an array and then
loop through the array and update the table using the field names stored
in the array? I...
|
by: Bob |
last post by:
Hi all,
I've got a table that I've imported and it has junk at the top of the
table, so after import I run a delete query to remove the junk lines
then I'm left with the field names I want for...
|
by: banderson |
last post by:
Hello,
I have a data entry form for a table with information about buildings and am having a problem making a combo box do what I want. I would like to make the combo box show a list of unique bldg...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |