Visual Basic Programming
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me

The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.

Go Back   Dev Shed ForumsProgramming Languages - MoreVisual Basic Programming

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old January 26th, 2012, 03:21 AM
anziga anziga is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Jul 2004
Posts: 189 anziga User rank is Major (30000 - 40000 Reputation Level)anziga User rank is Major (30000 - 40000 Reputation Level)anziga User rank is Major (30000 - 40000 Reputation Level)anziga User rank is Major (30000 - 40000 Reputation Level)anziga User rank is Major (30000 - 40000 Reputation Level)anziga User rank is Major (30000 - 40000 Reputation Level)anziga User rank is Major (30000 - 40000 Reputation Level)anziga User rank is Major (30000 - 40000 Reputation Level)anziga User rank is Major (30000 - 40000 Reputation Level)anziga User rank is Major (30000 - 40000 Reputation Level) 
Time spent in forums: 1 Week 1 Day 14 h 32 m 7 sec
Reputation Power: 327
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?

Reply With Quote
  #2  
Old February 11th, 2012, 09:29 PM
anziga anziga is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Jul 2004
Posts: 189 anziga User rank is Major (30000 - 40000 Reputation Level)anziga User rank is Major (30000 - 40000 Reputation Level)anziga User rank is Major (30000 - 40000 Reputation Level)anziga User rank is Major (30000 - 40000 Reputation Level)anziga User rank is Major (30000 - 40000 Reputation Level)anziga User rank is Major (30000 - 40000 Reputation Level)anziga User rank is Major (30000 - 40000 Reputation Level)anziga User rank is Major (30000 - 40000 Reputation Level)anziga User rank is Major (30000 - 40000 Reputation Level)anziga User rank is Major (30000 - 40000 Reputation Level) 
Time spent in forums: 1 Week 1 Day 14 h 32 m 7 sec
Reputation Power: 327
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.

Reply With Quote
  #3  
Old February 11th, 2012, 10:08 PM
requinix's Avatar
requinix requinix is offline
Still alive
Dev Shed God 16th Plane (12500 - 12999 posts)
 
Join Date: Mar 2007
Location: Washington, USA
Posts: 12,869 requinix User rank is General 120th Grade (Above 100000 Reputation Level)requinix User rank is General 120th Grade (Above 100000 Reputation Level)requinix User rank is General 120th Grade (Above 100000 Reputation Level)requinix User rank is General 120th Grade (Above 100000 Reputation Level)requinix User rank is General 120th Grade (Above 100000 Reputation Level)requinix User rank is General 120th Grade (Above 100000 Reputation Level)requinix User rank is General 120th Grade (Above 100000 Reputation Level)requinix User rank is General 120th Grade (Above 100000 Reputation Level)requinix User rank is General 120th Grade (Above 100000 Reputation Level)requinix User rank is General 120th Grade (Above 100000 Reputation Level)requinix User rank is General 120th Grade (Above 100000 Reputation Level)requinix User rank is General 120th Grade (Above 100000 Reputation Level)requinix User rank is General 120th Grade (Above 100000 Reputation Level)requinix User rank is General 120th Grade (Above 100000 Reputation Level)requinix User rank is General 120th Grade (Above 100000 Reputation Level)requinix User rank is General 120th Grade (Above 100000 Reputation Level)  Folding Points: 417516 Folding Title: Super Ultimate Folder - Level 1Folding Points: 417516 Folding Title: Super Ultimate Folder - Level 1Folding Points: 417516 Folding Title: Super Ultimate Folder - Level 1Folding Points: 417516 Folding Title: Super Ultimate Folder - Level 1Folding Points: 417516 Folding Title: Super Ultimate Folder - Level 1Folding Points: 417516 Folding Title: Super Ultimate Folder - Level 1
Time spent in forums: 5 Months 1 Week 5 Days 6 h 19 m 22 sec
Reputation Power: 8977
Send a message via AIM to requinix Send a message via MSN to requinix Send a message via Yahoo to requinix Send a message via Google Talk to requinix
VBA? Excel 2007 and probably earlier can pull data directly from a database, even with custom SQL queries.
Would that be easier?

Reply With Quote
  #4  
Old February 11th, 2012, 11:38 PM
anziga anziga is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Jul 2004
Posts: 189 anziga User rank is Major (30000 - 40000 Reputation Level)anziga User rank is Major (30000 - 40000 Reputation Level)anziga User rank is Major (30000 - 40000 Reputation Level)anziga User rank is Major (30000 - 40000 Reputation Level)anziga User rank is Major (30000 - 40000 Reputation Level)anziga User rank is Major (30000 - 40000 Reputation Level)anziga User rank is Major (30000 - 40000 Reputation Level)anziga User rank is Major (30000 - 40000 Reputation Level)anziga User rank is Major (30000 - 40000 Reputation Level)anziga User rank is Major (30000 - 40000 Reputation Level) 
Time spent in forums: 1 Week 1 Day 14 h 32 m 7 sec
Reputation Power: 327
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.

Reply With Quote
Reply

Viewing: Dev Shed ForumsProgramming Languages - MoreVisual Basic Programming > Excel/ADO/RecordSet problem

Developer Shed Advertisers and Affiliates



Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.

© 2003-2013 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap