The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.
|
 |
|
Dev Shed Forums
> Programming Languages - More
> Visual Basic Programming
|
Excel/ADO/RecordSet problem
Discuss Excel/ADO/RecordSet problem in the Visual Basic Programming forum on Dev Shed. Excel/ADO/RecordSet problem Visual Basic Programming forum discussing VB specific programming information. Quickly prototype and build applications with this robust and simple language.
|
|
 |
|
|
|
|

Dev Shed Forums Sponsor:
|
|
|

January 26th, 2012, 03:21 AM
|
|
|
|
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?
|

February 11th, 2012, 09:29 PM
|
|
|
|
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.
|

February 11th, 2012, 10:08 PM
|
 |
Still alive
|
|
Join Date: Mar 2007
Location: Washington, USA
|
|
|
VBA? Excel 2007 and probably earlier can pull data directly from a database, even with custom SQL queries.
Would that be easier?
|

February 11th, 2012, 11:38 PM
|
|
|
|
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.
|
Developer Shed Advertisers and Affiliates
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
Rate This Thread |
Linear Mode
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|