Automated field names

[GaelicFatboy]

Hi Chaps & Chapesses,

I have some code to perform a lengthy task of reading a text file and adding the data to fields within various table's record. The text file is long, the field names are varied and the tables are numerous.

I have some code to load the text file data into a collection which stores the table name, field name and data value. I then open the associated table and add the data in the following manner…

“clsImport” is a class storing table names.
“colSD” is a class collection storing the system data.

Set daoDBase = CurrentDb
Set daoRecSet = daoDBase.OpenRecordset(clsImport.Tblname, dbOpenDynaset)
With daoRecSet
.AddNew
![Name] = colSD(“name”).DataVal
![Address1] = colSD(“address1”).DataVal
![Address2] = colSD(“address2”).DataVal
![Pcode] = colSD(“pcode”).DataVal

{so on and so forth for each table at a time}

.Update
.Close
End With
Set daoRecSet = Nothing
daoDBase.Close
set daoDBase = Nothing
Set colSD = Nothing
Set clsImport = Nothing


What I would like to do instead of having to type each of the field names is to replace them with a variable or property stored in a collection and shorten the code to this…

Set daoDBase = CurrentDb
Set daoRecSet = daoDBase.OpenRecordset(clsImport.Tblname, dbOpenDynaset)
With daoRecSet
.AddNew
For intX = 1 to colSD.Count
![colSD(intX).FieldName] = colSD(intX).DataVal
Next
.Update
.Close
End With
Set daoRecSet = Nothing
daoDBase.Close
set daoDBase = Nothing
Set colSD = Nothing
Set clsImport = Nothing

I’ve tried various options for the “.FieldName” but none of my attempts have been successful. Any ideas?

Thanks for your help.

Cheers

D

 

[Ranman256]

Can you not use the command,
TransferText
And do it all on one command,instead of all that code?

 

[gemma-the-husky]

You can do what you want

I would use .createfield method of the tabledef object

some variation of this. The problem comes with how you decide to type the field you are adding. In the example below it is dbText (which is a numeric constant). Other field types have different (numeric) constants.

dim fld as field
dim tbl as tabledef

set tbl = currentdb.tabledefs("sometable")
set fld = tbl.createfield("newfieldname",dbtext)
tbl.append fld


-----
sorry. the above example adds new columns to a table

if you are just trying to add new rows to an existing table, then it is different

instead of this

 [FONT=Arial]Set daoDBase = CurrentDb
Set daoRecSet = daoDBase.OpenRecordset(clsImport.Tblname, dbOpenDynaset)
With daoRecSet
.AddNew
For intX = 1 to colSD.Count
![colSD(intX).FieldName] = colSD(intX).DataVal
Next
.Update
.Close[/FONT]

something like this

 set daoRecset = whatever
  
 'sutiable command to open the linked table
  [FONT=Arial]set rst = daoDBase.OpenRecordset(clsImport.Tblname, dbOpenDynaset)[/FONT]

  [FONT=Arial]while not rst.eof[/FONT]
 [FONT=Arial]   With daoRecSet
       .AddNew
       !.FieldName] = rst!DataVal
      .Update[/FONT]
 [FONT=Arial]      .movenext
   end with   [/FONT]
 [FONT=Arial]wend[/FONT]
 [FONT=Arial]'close objects[/FONT]

 

[GaelicFatboy]

Thanks for your help, but I can't get that field referancing to work. Take for example the second field in my example, "Address1", what I'm trying to do is referance this field name without having to type the field name itself. For example setting a variable "FieldName"...

FieldName = "Address1"

But...

![FieldName] = DataVal

doesn't work and throws up an error. I've tried referancing the field using it's index number but either this cannot be done or I've not stumbled on the correct syntax yet.

Any ideas?

Cheers

Denis

P.S. TransferText won't work as I have a single text file but multiple tables and data all in the one file.

 

[gemma-the-husky]

I don't quite understand what you are doing. To address a field in a recordset you can use either of these

rst!myfieldname - where myfieldname is that actual field name, no "" characters

OR

rst.fields("myfieldname")

in this latter case, you CAN use a variable, so you can say

rst.fields(varname)


does that help?

note that you can also use the ordinal position

rst.fields(x) but that probably doesn't help

 

[GaelicFatboy]

Cheers for that, it's all working now.

I was using the ordinal position to step through the fields from start to finish, but the code was running through one iteration and then dropping out with an error, so I didn't think to try using the name's string variable (I put it down to man flu). I've thrown a few extra lines in to referance the name string instead of the incremental index number and it all works.

Thanks it saves an awful lot of coding.

Denis