programatic field reference

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

Guest

Could someone point me to the part of the access object model that allows me
to refer to fields in a table programatically by their name? A code example
would be greatly appreciated.
 
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

Set dbCurr = CurrentDb()
For Each tdfCurr In dbCurr.TableDefs
If (tdfCurr.Attributes And dbSystemObject) = 0 Then
Debug.Print tdfCurr.Name
For Each fldCurr In tdfCurr.Fields
Debug.Print fldCurr.Name
Next fldCurr
Debug.Print
End If
Next tdfCurr
Set dbCurr = Nothing
 
Thanks, that's what I asked for, but I can't quite seem to extract what I
need from it, so let me explain a little more.

I am importing data that I am told may vary in field order, so the first
record is the field list of imported data, the rest is the data.
I import it into a table that has 80 fields, field1, field2, field3... field80

What I need to do is cycle through the 80 fields like so, to figure out
where the data in each column belongs:

dim rs as recordset
..........
for i = 1 to 80
x = rs!field
<look up where the data in this column goes...>
etc.
next i

Is it possible to refer to a field in a recordset indirectly in some way
like this?

Thanks
Fred
 
Fredrated said:
Thanks, that's what I asked for, but I can't quite seem to extract what I
need from it, so let me explain a little more.

I am importing data that I am told may vary in field order, so the first
record is the field list of imported data, the rest is the data.
I import it into a table that has 80 fields, field1, field2, field3... field80

What I need to do is cycle through the 80 fields like so, to figure out
where the data in each column belongs:

dim rs as recordset
.........
for i = 1 to 80
x = rs!field
<look up where the data in this column goes...>
etc.
next i

Is it possible to refer to a field in a recordset indirectly in some way
like this?
Click to expand...


The hard part of this, IMO, is matching up which field in the table
belongs with which column in the spreadsheet, not the import or
anything else. If the columns are named, then you could just use
that. If you link to the Excel file, you could treat it like a normal
Access table.

rsFinal.AddNew '---add a new record to the table
'set field values...
rsFinal.Fields("FieldNameX")=rsXL.Fields("FieldName")
rsFinal.Fields("FieldNameY")=rsXL.Fields(4) '<---fifth column of the
table. (Count from zero.)
rsFinal.Update

check out AddNew in the help... maybe that will help a little.
 
Back
Top