create new table in a loop (ADO)?

[Meeko]

hi all,

The following is a for loop that will read the feature name, I would like to add them as the field names of a new table of a mdb. How could I do this?

Dim dbs As Database

For n = 0 To FieldCount - 1
aSTR = FieldName(n)
dbs.Execute "CREATE TABLE new(????);"
Next n

in the same case, I would like to fill the table with the values of each field in a loop,

For j=1 to recordcount
For n = 1 To FieldCount - 1
aSTR = CStr(RecordValue(j, fieldName(n))
Next n
Next j


'RecordValue(j, fieldName(n)) is the value of the j record at n'th field

Thanks very much!!

[Bruce Fox]

Create your Table and use INSERT like:

dbs.Execute "INSERT INTO table_name(fieldName1, fieldName2) VALUES (value1, value2);"

This will place 2 values into the Table.

[Meeko]

thanks for reply,
however my problem is that the value1, value2 is from a loop,
value1 is from the first loop
and value2 is from the next loop....

btw, how to replace fieldname1,fieldname2 with the name of the first column, and the name of the second column,
I mean not knowing the fieldnames but get them by index...
thanks

[Bruce Fox]

Originally posted by Meeko
thanks for reply,
however my problem is that the value1, value2 is from a loop,
value1 is from the first loop
and value2 is from the next loop....

btw, how to replace fieldname1,fieldname2 with the name of the first column, and the name of the second column,
I mean not knowing the fieldnames but get them by index...
thanks

A. Add ONLY one value during each loop. ie. Execute the SQL to load only
on of the values like:

dbs.Execute "NSERT INTO table_name(fieldName1) VALUES (value1);"

And in the other loop do:

dbs.Execute "NSERT INTO table_name(fieldName1) VALUES (value2);"

B. You can specify the colum index (from memory) like:

dbs.Execute "NSERT INTO table_name(1, 2) VALUES (value1, value2);"

Bruce.

[Bruce Fox]

However......

If the values need to be added concurently...... thats another issue.

You could set a counter from the first loop, then reposition the
record set to the 'first' loop 1 data entry, and add loop 2 data
from that spot.???

[Meeko]

You could set a counter from the first loop, then reposition the
record set to the 'first' loop 1 data entry, and add loop 2 data
from that spot.???

What do you mean? Would you please have more explanation?


BTW, when I use the fieldindex, error occurs saying it is an unknown field name 1...

INSERT into dbf (1) values('test')

[Meeko]

I've try to use adox to append the fields to the table,
however, how could I insert records with values to each field using adox?


Greatly appreciate for any reply!!!!