Using variables to write data to an Access file

External Since: Feb 18, 2007 Posts: 2

Hi all,

I have an MS Access database with just one table. In that table are
several dozen columns, each one called Variable1, Variable2,
Variable3, etc.

Is there any way to use a For/Next loop to write information to this
table? For example, if I wanted to write data to just one variable, I
could use this: MyDatabase!Variable1 = [whatever value].

What I'm trying to do is write all of the variables at once, using
something like this:

For b = 1 to 100
MyDatabase!Variable"(b)" = [whatever value].
Next b

Obviously this code doesn't work, but I'm wondering whether someone
knows how I can accomplish this.

Thanks,
Rob

External Since: Jun 27, 2004 Posts: 24

wrote in message

> Hi all,
>
> I have an MS Access database with just one table. In that table are
> several dozen columns, each one called Variable1, Variable2,
> Variable3, etc.
>
> Is there any way to use a For/Next loop to write information to this
> table? For example, if I wanted to write data to just one variable, I
> could use this: MyDatabase!Variable1 = [whatever value].
>
> What I'm trying to do is write all of the variables at once, using
> something like this:
>
> For b = 1 to 100
> MyDatabase!Variable"(b)" = [whatever value].
> Next b
>
> Obviously this code doesn't work, but I'm wondering whether someone
> knows how I can accomplish this.
>
> Thanks,
> Rob

This is a DAO group, so assuming DAO

Say your query asks for any 3 fields, something like
set RS=DB.OpenRecordSet("Select Variable1,Variable12,Variable35 From
YourTable")

Then the returned recordset will have 3 fields as follows
RS.Field(0) ' Variable1
RS.Field(1) ' Variable12
RS.Field(2) ' Variable35 'note the order of the fields in the query

Similarly, if your query gets all the fields
set RS=DB.OpenRecordSet("Select * From YourTable") then you
can loop thru with something like
For i = 0 to RS.Fields.Count - 1
RS.Edit
RS.Fields(i).Value = [whatever value] 'likely an array of data
RS.Update
Next 'note: This is updating only 1 row, but all the fields

This will allow you to give your Fields more descriptive names
which will preserve your sanity.