Find Field Name

[Tigar07]

Hi,

I am new to Access and I have a problem in Update Query.

I am working on financial database which contains budget for each quaters.
Every month I get new data by excel files and I have to update existing data
in Access.
Problem is those excel files have different format like one has data up to
2012 and the other has data to 2015.
The table which needed to be updated has fields set up to store data through
2015.

What I try to do in the query is update data if the field exist in excel file.

Is there a way to find out the name of a table's column/field name can be
used in query in Access? or smiliar to Oracle Dictionary or DB2 Catalogue?

Thank you in advance.

 

[Roger Carlson]

If you mean is there a table which holds the names of all the fields in all
the tables, no, there is not. However, this information is stored in the
TableDefs collection which can be access programmatically and you can write
it to a table. Something like this:

Dim dbs As DAO.Database, tdf As DAO.TableDef
Dim fld As DAO.Field, rst As DAO.Recordset

Set dbs = CurrentDb
Set tdf = dbs.TableDefs("Employees")
db.Execute "Delete * from TableFields"
Set rst = dbs.OpenRecordset("TableFields", dbOpenDynaset)
' Enumerate all fields in Fields collection of TableDef object.
For Each fld In tdf.Fields
If fld.Type >= 1 And fld.Type <= 8 Or fld.Type = 10 Then
rst.AddNew
rst!FieldName = fld.Name
rst!FieldType = fld.Type
rst.Update
End If
Next fld

Which will write the field names and field type of the "Employees" table to
another table called "TableFields" that has only two columns: FieldName and
FieldType.

Of course, you wouldn't have to write them to a table. You could simply use
the code to test for the field itself.

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "ChooseReportFields.mdb" which shows this in action. You can
find it here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=385

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Dale Fye]

Tigar,

If you have columns in your Access table labeled 2009, 2010, 2011, ..., 2015
then you have "committed spreadsheet" and are not really going to be able to
take advantage of the relational aspects of an Access database. What I would
encourage you to do is add a single field (call it BudgetYear) and put the
year in this field, rather than having a separate field for each BudgetYear.

Then, when you get the excel file, you could import or link it, step through
each of the columns in that "table" and strip the column number out of the
header and put it in your Access table.

The problem with having all of these fields with BudgetYears is that you
will have to write separate queries for each of the fields. Whereas if you
have a BudgetYear column, it is easy to modify the query so that the criteria
looks at a specific year.

If you post a little more info about the structure of your table and the
Excel file, I would be glad to help you with this.

 

[Tigar07]

Thank you for your reply, Roger.

This is exactly same as Oracle Dictionary and DB2 Catalogue.
I have downloaded your sample database.
Let me try with it and see how it goes.

Thanks again,
Tigar07

 

[Tigar07]

Thank you for your reply.

Here is the details about the database.

The table "Forecast" to be updated has fields like following:
Project ID Project Description Q1 09 Q2 09 ..... Q3 15 Q4
15
1234 Installer SW Installer $1000 $1000
2345 Training Training $5000 $5000 $5000
$5000

Two excel files which are linked are something like
Excel file A
ID Project Manager Description Q1 09 Q2 09 .... Q3 12
Q4 12 1234 Installer James Smith SW Installer $1000 $1000
$2000 $2000

Excel file B
ID Project Sub Project Description Location Q2 09 Q3 09
..... Q3 15 2345 Training Contractor Training CA
$5000 $5000 $5000

Q4 15
$10000

In a query I would like to update Forecast table's Quarter Year field to
excel files if the data exists in the excel files.
The excel files are coming from different departments, and numbers of
columns and order of field are not same. Also they may add more columns in
the files.

Any suggestions and helps greatly appreciated.

Tigar07