Problem updating recordset with ADO, difficulty adding to table with columns
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:
Code:
'''''''''''''''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:
Code:
'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.
