Appending results of loop to a table

[Cheese_whiz]

Hi all,

I want to loop through some object collections (objects in the collections,
to be precise) and get system values, and then append the results to a table
in the current db. Right now, the best I know how to do is create a text
file, loop through and append the object/value combinations as a new line in
the text file, then, when the loop ends use the transfertext method to import
the lines into a table and then delete the text file.

It works, but it seems like a round about way of doing things.

Is there a way to just take each of these combinations (object/value), as
I'm going through the loop, and append them to a table?

Thanks,
CW

 

[Michel Walsh]

If you want add one record by pair, you can, in the loop, use:


Dim db As Database : Set db=CurrentDb 'outside the loop

For ..

...
db.Execute "INSERT INTO tableName( field1, field2) VALUES(" &
value1 & ", " & value2 & ")" , dbFailOnError

Next ...


Note that I assumed value1 and value2 to be numerical. If both are strings,
you need a delimiter:


db.Execute "INSERT INTO tableName(field1, field2) VALUES("""& value1
& """,""" & value2 & """)", dbFailOnError


(and that assume neither value1, neither value2 has a " in it, as data,
such as: 5' - 6" 1/8 )


If value1 is a string and value2 a date_time:


db.Execute "INSERT INTO tableName(field1, field2) VALUES("""& value1
& """,#" & Format(value2, "mm/dd/yyyy hh:nn:ss") & "#)", dbFailOnError


Sure, if the values can be picked from a control in an open form, it may be
easier:

DoCmd.RunSQL "INSERT INTO tableName(field1, field2)
VALUES(FORMS!formName!ControlName1, FORMS!formName!ControlName2)"


since then no delimiter will be required.



You may also chose to append the data through an open recordset, but opening
a recordset JUST for that purpose sounds a waste of time.



Vanderghast, Access MVP

 

[Cheese_whiz]

Thanks!

Just what I needed.

CW

If you want add one record by pair, you can, in the loop, use:


Dim db As Database : Set db=CurrentDb 'outside the loop

For ..

...
db.Execute "INSERT INTO tableName( field1, field2) VALUES(" &
value1 & ", " & value2 & ")" , dbFailOnError

Next ...


Note that I assumed value1 and value2 to be numerical. If both are strings,
you need a delimiter:


db.Execute "INSERT INTO tableName(field1, field2) VALUES("""& value1
& """,""" & value2 & """)", dbFailOnError


(and that assume neither value1, neither value2 has a " in it, as data,
such as: 5' - 6" 1/8 )


If value1 is a string and value2 a date_time:


db.Execute "INSERT INTO tableName(field1, field2) VALUES("""& value1
& """,#" & Format(value2, "mm/dd/yyyy hh:nn:ss") & "#)", dbFailOnError


Sure, if the values can be picked from a control in an open form, it may be
easier:

DoCmd.RunSQL "INSERT INTO tableName(field1, field2)
VALUES(FORMS!formName!ControlName1, FORMS!formName!ControlName2)"


since then no delimiter will be required.



You may also chose to append the data through an open recordset, but opening
a recordset JUST for that purpose sounds a waste of time.



Vanderghast, Access MVP