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


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

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

Hybrid View

  1. #1
    Join Date
    Apr 2008
    Posts
    13

    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.

  2. #2
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    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?
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  3. #3
    Join Date
    Aug 2005
    Location
    Hermosillo, Sonora, Mexico
    Posts
    103
    Probably something like
    Code:
    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...

Similar Threads

  1. Replies: 1
    Last Post: 05-07-2003, 07:01 PM
  2. ADO Data Control Recordset problem
    By praveen in forum VB Classic
    Replies: 1
    Last Post: 05-22-2002, 01:34 PM
  3. VB doesn't wait until SQL-query has finished
    By Daniel Teufert in forum VB Classic
    Replies: 2
    Last Post: 04-24-2002, 03:34 PM
  4. Replies: 1
    Last Post: 07-03-2001, 10:45 AM
  5. Re: Updating a hierarchical recordset
    By Todd in forum VB Classic
    Replies: 0
    Last Post: 12-21-2000, 03:41 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center