1

I have a table in MS-Access that needs to contain an array of 4 (type number) values. At the moment they are in named fields, VAL1, VAL2, VAL3, VAL4. However, that’s cumbersome (the number of values may be larger) because I want to use a loop in the associated VBA code, which generates a binary data file from the database tables.

Is it possible for VBA to access table fields using raw index values? As an example, if I have a table with the following fields:

Name VAL1 VAL2 VAL3 VAL4

Can I reference fields using generic names and indices, so that my 4 VAL fields can be accessed in a loop using a structure such as TableName.field(i)?

BruceV
  • 117
  • 2
  • 10
  • 1
    No, not really. In cases like this go down not across, i.e. have another table with `name_id | value_id | value` - so 4 rows per name relating to each value. – Alex K. Apr 11 '17 at 14:22
  • You can loop the fields of a recordset, see e.g. here: http://stackoverflow.com/q/11548697/3820271 (use `rs.Fields(n).Value` to get the value). But as Alex wrote, you should probably change your table design. And use Crosstab query / Pivot to get a tabular view. – Andre Apr 11 '17 at 14:28
  • Note that index begins with 0. Also, for similarly named fields, can dynamically construct the field name and reference that: `rs.Fields("Val" & n)`. However, since you probably need to reference all fields, not just the similar named ones, use the index reference. – June7 Apr 11 '17 at 17:45
  • @BruceV - Are you saying you want (n) number of values to be contained in one column / field, rather than multiple columns, and you want to be able to iterate the values? – Absinthe Apr 11 '17 at 17:52
  • as alex k mentioned you can use secondary table 1:N relationship to have all your sub values. or if your application can handle, use JSON and save the array as string. Any loop operation must extract the json array first before accessing the content! – Krish Apr 12 '17 at 13:20
  • Does this answer your question? [Looping through an array and posting array to table](https://stackoverflow.com/questions/15637850/looping-through-an-array-and-posting-array-to-table) – June7 Apr 21 '21 at 18:10

1 Answers1

-1

I think it will be a lot better for you now, and especially in the long run, if you name your fields relevant to what they actually represent. That's my .02.

ASH
  • 20,759
  • 19
  • 87
  • 200