#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2004
    Posts
    194
    Rep Power
    329

    Excel/ADO/RecordSet problem


    I use ADO connection, read data to recordset and then try to transfer the data to a worksheet:
    Code:
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Set cnn = New ADODB.Connection
    Set rst = New ADODB.Recordset
    Dim rowCounter As Long
    rowCounter = 0
    cnn.Open "solidDB", "dba", "dba"
    rst.ActiveConnection = cnn
    rst.CursorLocation = adUseServer
    rst.Source = "SELECT SNAME, SFIG, SITEM, SMONTH, SYEAR FROM YRYLESTABLE WHERE SYEAR = '2011'"
    rst.Open
    Do While Not rst.EOF
    rowCounter = rowCounter + 1
    If rowCounter = 1 Then
    Worksheets("Sheet4").Cells(rowCounter, "A") = rst.Fields(0).Name
    Worksheets("Sheet4").Cells(rowCounter, "B") = rst.Fields(1).Name ' shows column header OK...
    Worksheets("Sheet4").Cells(rowCounter, "C") = rst.Fields(2).Name
    Worksheets("Sheet4").Cells(rowCounter, "D") = rst.Fields(3).Name
    Worksheets("Sheet4").Cells(rowCounter, "E") = rst.Fields(4).Name
    rst.MoveNext
    End If
    If rowCounter >= 2 Then
    Worksheets("Sheet4").Cells(rowCounter, "A").Value = rst.Fields(0).Value
    Worksheets("Sheet4").Cells(rowCounter, "B").Value = rst.Fields(1).Value '<<< results an error...
    Worksheets("Sheet4").Cells(rowCounter, "C").Value = rst.Fields(2).Value
    Worksheets("Sheet4").Cells(rowCounter, "D").Value = rst.Fields(3).Value
    Worksheets("Sheet4").Cells(rowCounter, "E").Value = rst.Fields(4).Value
    rst.MoveNext
    End If
    Loop
    rst.Close
    Set rst = Nothing
    Set cnn = Nothing
    This works partially, but rst.Fields(1) value is not correct. In database it is a floating point number, from -100 to 150000 roughly, but in the worksheet it will be something astronomical, like 2361183283216270000. I have tried various different ways, but no luck. Without "On Error Resume Next" just before the line, I got "error -2147217887 (80040e21) Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done." There are some empty cells, but not sure if those result that error. All the other fields are OK. I have no idea what kind of data types original database uses. When using QueryTables, all the fields come out correctly. Any idea what might be the problem?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2004
    Posts
    194
    Rep Power
    329
    Well, this particular code wasn't working as hoped and could have used e.g. For/Next to run through those recordset fields. Anyway, I changed the connection to DAO and modified the code a bit, then the values came out OK. Soon after I noticed another problem, when using recordset to hold tens or even hundreds of thousands of records and search/fetch data from there is really slow process. I tried to split data to smaller recordsets, this was already a bit faster, but not enough. Once I changed the concept to split DB fields to individual arrays, I managed to cut down the search time to 1/10ᵗʰ - 1/20ᵗʰ from the original search time.
  4. #3
  5. Jealous Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,302
    Rep Power
    9400
    VBA? Excel 2007 and probably earlier can pull data directly from a database, even with custom SQL queries.
    Would that be easier?
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2004
    Posts
    194
    Rep Power
    329
    The actual code is way more complex, this was just a test to see if the data comes out OK. I know Excel can read data from DB, but in this particular case I couldn't utilize it, I had to use VBA. I actually tried first to read data from DB to a temp worksheet and then do all the comparisons, calculations, etc. by using that temp worksheet data, but it wasn't fast enough, but it did work though.

IMN logo majestic logo threadwatch logo seochat tools logo