Problem updating recordset with ADO, difficulty adding to table with columns

[awyeah]

Hi!

Currently I have a database (data.mdb) with 24 tables, and different table names. I am using
sql query to do some preprocessing on it. I create another (svmdata.mdb) file with 1 single table and
empty field (column names).

I wish to preprocess the data using sql query and add each of the results of the sql table
into a column in my new database file. Currently I am having some problems with the recordset
adding, it is not adding to the different columns in the new file, it adds all data into a single
column rather than the 24 columns I created.

Here is the code I use for creating my new db file with a table and 24 empty columns in it:

'''''''''''''''Create 25 column access database for svm training
Dim fso3
Dim svmdbpath       As String
Dim catNewDB3       As New ADOX.Catalog
Dim cn              As New ADODB.Connection
Dim strSQL          As String
Set catNewDB3 = New ADOX.Catalog
    
svmdbpath = App.Path & "\svmdata.mdb"
Set fso3 = CreateObject("Scripting.FileSystemObject")

If fso3.FileExists(svmdbpath) Then
   fso3.DeleteFile svmdbpath
End If

catNewDB3.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
         "Data Source=" & svmdbpath & ";"
Set catNewDB3 = Nothing

'Open db file
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & svmdbpath & ";"

'Create a new table for svm data (25 features and 1 label)
strSQL = "CREATE TABLE Data(F1 Single, F2 Single, F3 Single, F4 Single, " _
 & "F5 Single, F6 Single, F7 Single, F8 Single, F9 Single, " _
 & "F10 Single, F11 Single, F12 Single, F13 Single, F14 Single, " _
 & "F15 Single, F16 Single, F17 Single, F18 Single, F19 Single, " _
 & "F20 Single, F21 Single, F22 Single, F23 Single, F24 Single)"
cn.Execute (strSQL)

Here is the code I am using to add the records to my new file:

'Open newdata.mdb and subtract monthly consumption values
Dim cat             As New ADOX.Catalog
Dim rs              As New ADODB.Recordset
Dim rsNew           As New ADODB.Recordset
Set cn = New ADODB.Connection

Dim newdbpath       As String
Dim sql             As String
Dim month(0 To 2)   As String
Dim lngPosition     As Long

Dim featcols        As Variant
Dim subcols         As Variant
featcols = Array("F1", "F2", "F3", "F4", "F5", "F6", "F7", "F8", "F9", "F10", "F11", "F12", "F13", "F14", "F15", "F16", "F17", "F18", "F19", "F20", "F21", "F22", "F23", "F24")
subcols = Array("01", "02", "03", "04", "05", "06", "07", "08", "09", "010", "011", "012", "013", "014", "015", "016", "017", "018", "019", "020", "021", "022", "023", "024", "025")

newdbpath = App.Path & "\newdata.mdb"

'Open Customer ID file
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & newdbpath & ";"

'INNER JOIN records
For lngPosition = LBound(month) To UBound(month)

sql = "SELECT ABS([" & subcols(lngPosition + 1) & "].Consumption - " _
 & "[" & subcols(lngPosition) & "].Consumption) From " _
 & "[" & subcols(lngPosition + 1) & "], [" & subcols(lngPosition) & "]"
MsgBox (sql)
Set rs = cn.Execute(sql)
   
'Opening Catalog Connection
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                          "Data Source=" & svmdbpath & ";"

'Open table
rsNew.Open "Select " & featcols(lngPosition) & " from Data", cat.ActiveConnection, adOpenKeyset, adLockOptimistic

'Initialising Counter
Do Until rs.EOF

With rsNew
  For Each fld In rs.Fields
  .AddNew
  .Fields(lngPosition).Value = fld
   Next
   .Update
End With

rs.MoveNext
Loop
rsNew.Close
Next lngPosition

Set rs = Nothing
cn.Close

I am having problems for adding my recordset into different columns.
Everytime I do a sql query I wish to add that to a different column of my new recordset (svmdata file).
Currently it add all data into the first column 'F1'.

It should be like, every time I query, that query should be added to 'F1' and then for the second
query add to 'F2' and so on, untill the 24 columns. All help is appreciated. I do not think this is
difficult, just am not able to grasp the method of adding and updating the records.

[Hack]

I went through your code and all I saw was a SELECT query and a CREATE TABLE query, neither of which is going to add records.

To add records, I would write an INSERT INTO query. Are you familiar with those? Do you need an example?

[jggtz]

Probably something like

INSERT INTO NewTable
SELECT * FROM Table1 UNION ALL
SELECT * FROM Table2 UNION ALL
SELECT * FROM Table3 UNION ALL
SELECT * FROM Table4 UNION ALL
SELECT * FROM Table5 UNION ALL
etc...