Results 1 to 12 of 12

Thread: [RESOLVED] ADO Select Sum of a recordset

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Feb 2006
    Location
    London
    Posts
    90

    Resolved [RESOLVED] ADO Select Sum of a recordset

    Hi Folks,

    Can anybody help here, all seems good but am unfamiliar wuth summing a value on a recordset and then turfing it out - see last few lines of code!

    Thanks

    Code:
    Option Explicit
    Private Sub Workbook_Open()
    Dim Finalrow As Variant
    Dim i As Integer
    Dim JobNo As Variant
    
    Dim objConnection As Object
    Dim objRecordset As Recordset
    
    Finalrow = Range("A65536").End(xlUp).Address
        Finalrow = Right(Finalrow, 2)
        
        For i = 1 To Finalrow
     JobNo = Cells(i, 1).Value
     
    On Error Resume Next
    
    Const adOpenStatic = 3
    Const adLockOptimistic = 3
    Const adCmdText = &H1
    
    Set objConnection = CreateObject("ADODB.Connection")
    Set objRecordset = CreateObject("ADODB.Recordset")
    
    objConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=U:\Radii\LOGS\Purchase Order Log.xls;" & _
            "Extended Properties=""Excel 8.0;HDR=Yes;"";"
    
    objConnection.Open
    objRecordset.Open "SELECT Sum POVALUEexclVAT, FROM [Log$] WHERE PROJECTNUMBER = '" & JobNo & "'", objConnection, adOpenStatic, adLockOptimistic, adCmdText
    
    'Cells(i, 2).value = 'value of the summed recordset
    
    Next i
    
    End Sub
    Thanks

  2. #2

    Thread Starter
    Lively Member
    Join Date
    Feb 2006
    Location
    London
    Posts
    90

    Re: ADO Select Sum of a recordset

    yeah I know about that but thats okay I can get around it, I was merely getting a draft down before elaorating and finessing :-)

    I'm sure ive read that you can return the sum of all recordset values somewhere? In this case the jobNum will match Purchase Orders of which there could be hundreds and return a total value or total cost of a project?

    Does this make sense

  3. #3
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: ADO Select Sum of a recordset

    1) Please, please, please, PLEASE don't open and close the connection in the loop. That's only going to cause problems and slow it down.
    2) Is the data you are trying to select from in the same document that the code is running? Or rather, I see referenced to Cell and Range, are those from the same file that you are getting the data from?
    3) The SUM use is wrong, it should be "SELECT SUM(POVALUEexclVAT) AS VatTotal FROM ...."
    4) Why do it line by line? Just do a SELECT SUM on all of the rows, then you won't need to do a loop or anything.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Feb 2006
    Location
    London
    Posts
    90

    Re: ADO Select Sum of a recordset

    Hi Tg



    1) Please, please, please, PLEASE don't open and close the connection in the loop. That's only going to cause problems and slow it down.

    Am I? thought i was opening it and then performining the loop!

    2) Is the data you are trying to select from in the same document that the code is running? Or rather, I see referenced to Cell and Range, are those from the same file that you are getting the data from?

    ADO connection is to an excel document elsewhere on the network.

    3) The SUM use is wrong, it should be "SELECT SUM(POVALUEexclVAT) AS VatTotal FROM ...."
    I'm not sure where the As VatTotal comes from, can you elaborate

    4) Why do it line by line? Just do a SELECT SUM on all of the rows, then you won't need to do a loop or anything.
    Sorry sorry dont know what you are saying, Im looping because each occurence will be a different job number and therefore a new cost. It stands that there will be as many loops as there are job numbers hope this is understandable

    Many thanks

    D

  5. #5
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: ADO Select Sum of a recordset

    1) Look again.... your objConnection.Open is inside the For i loop....

    2) I got that, what I want to know is if it's a _different_ excel file from the one the code is in.

    3) "AS VatTotal" is an alias.... by doing a select SUM... we in effect created a new column... the as VatTotal gives that column a name. Allow it to be referenced later.

    4) Ok. I get it. So how many rows are we talking about? 5, 10, 50, 150, 1500?

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6

    Thread Starter
    Lively Member
    Join Date
    Feb 2006
    Location
    London
    Posts
    90

    Re: ADO Select Sum of a recordset

    Thanks for the reply

    1. Ok
    2. No, code is in the excel file that I will write the data to
    3. Ok
    4. about a 100 on avarage

    I'll spend some time digging around today

    Cheers

  7. #7

    Thread Starter
    Lively Member
    Join Date
    Feb 2006
    Location
    London
    Posts
    90

    Thumbs up Re: ADO Select Sum of a recordset

    Sorry if I'm being a bit thick but.... Maybe some one can put me out of my misery

    I've made a few changes but am stuck, maybe someone can point me in the right direction. I get the Value of the first JobNo but thats it

    1. I would like to write the result to a cell next to the job Number see line of code "Range("b2").CopyFromRecordset objRecordset" I've tried using the counting integer "cells(i, 2).CopyFromRecordset objRecordset" but that does not diplay anything at all??

    2. By rights this code should display the value of the last jobNo loop however it seems to only work once and does not overwrite the first value in cell B2 for the latter JobNos

    3. This is a work in progress and in actual fact I want to record the value to a Dim for further manipulation. Can any one shed light on this

    This what i'm looping through:

    JobNo Value
    R0517 cellB2
    R0518 cellB3
    R0519 cellB4
    Etc

    Code:
    Option Explicit
    Private Sub Workbook_Open()
    Dim Finalrow As Variant
    Dim i As Integer
    Dim JobNo As Variant
    Dim ProjectCost As Variant
    
    Dim objConnection As Object
    Dim objRecordset As Recordset
    
    Const adOpenStatic = 3
    Const adLockOptimistic = 3
    Const adCmdText = &H1
    
    Set objConnection = CreateObject("ADODB.Connection")
    Set objRecordset = CreateObject("ADODB.Recordset")
    
    objConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=U:\Radii\LOGS\Purchase Order Log.xls;" & _
            "Extended Properties=""Excel 8.0;HDR=Yes;"";"
    
    objConnection.Open
    
    Finalrow = Range("A65536").End(xlUp).Address
        Finalrow = Right(Finalrow, 2)
     Cells(1, 2).Value = "VatTotal"
        
        For i = 2 To Finalrow
     
     JobNo = Cells(i, 1).Value
     
    On Error Resume Next
    
    objRecordset.Open "SELECT SUM(INVVALUE)AS VatTotal FROM [Log$] WHERE PROJECTNUMBER = '" & JobNo & "'", objConnection, adOpenStatic, adLockOptimistic, adCmdText
    
    
    Range("b2").CopyFromRecordset objRecordset
    
    Next i
    
    objConnection.Close
    
    End Sub

  8. #8
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: ADO Select Sum of a recordset

    There is a major problem with your code, and VB wants to tell you what it is.. but you have decided to put your fingers in your ears and shout "I'm not listening!", which is a rather odd (but surprisingly common) thing to do. I'd recommending reading the article What is wrong with using "On Error Resume Next"? from our Classic VB FAQs (in the FAQ forum, which is shown near the top of our home page)


    Once you have removed that, you will find that the second time through the loop (when i=3), on the objRecordset.Open line you will get an error like "Operation is not allowed when the object is open.", because you haven't bothered to close it since you last opened it.

    Even without re-opening it, you should still be closing it when you are finished with it - otherwise you may find that you damage your data files. For the same reason, you should also Set it (and the connection) to Nothing after closing. The last couple of lines should be changed to this:
    Code:
    Next i
    
    objConnection.Close
    Set objConnection = Nothing
    
    End Sub
    You should close/Set the recordset in the same way, and this should be done after using it (so after CopyFromRecordset), and before you re-open it or close the connection (so before the "Next i").

  9. #9

    Thread Starter
    Lively Member
    Join Date
    Feb 2006
    Location
    London
    Posts
    90

    Re: ADO Select Sum of a recordset

    I guess my inexperience shows!

  10. #10

    Thread Starter
    Lively Member
    Join Date
    Feb 2006
    Location
    London
    Posts
    90

    Re: ADO Select Sum of a recordset

    Thanks Si, great advice as usual. I'm very keen to work with best practice at all times. I have found it difficult to get good info for learning more about ADO and in particular addressing some of my issues stated on the thread, response No.8

    I've followed your advice which works fine now but earlier Techgnome said
    HTML Code:
    1) Please, please, please, PLEASE don't open and close the connection in the loop. That's only going to cause problems and slow it down.
    This is precisely what I have done so maybe i'm up for an ear bashing!! it works fine albeit really slow as expected. Maybe theres a far more efficient way of gathering the info I need from the Excel Database?

    If anybody knows of some good learning resources please let me know.

    I suppose I'm keen to find alternatives to this line:
    Code:
    Range("b2").CopyFromRecordset objRecordset
    as I want to Dim it and then manipulate it.

    Thanks

  11. #11
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: ADO Select Sum of a recordset

    Now you have moved the objConnection.Open line to before the loop, it should be faster than it was (and also be less prone to causing errors). That issue isn't about ADO as such, it is just general programming - if you put code inside a loop, it will run multiple times (so don't put it there unless you actually want to run it repeatedly!).


    Regarding the CopyFromRecordset, I'm not sure what you mean by "I want to Dim it and then manipulate it.". However, as you are only returning a single value (the Sum which is aliased as VatTotal), there is no need to copy the entire recordset, which will take extra work.. instead simply put that single value into your sheet, eg:
    Code:
    Range("b2").Value = objRecordset.Fields("VatTotal").Value
    'or:
    Cells(i ,2).Value = objRecordset.Fields("VatTotal").Value

    Due to the amount of work involved (adding totals by PROJECTNUMBER), I don't think you could make this much more efficient by using non-database code, but I guess reading the sheet into an array would be an improvement.

    To make it faster, you could change the source data to an actual database, rather than an Excel file. You are using database related functions to read it, but an Excel file is still just a file, which is not as efficient as a real database.

    To make it much faster still, you could use a single SQL statement (instead of a loop) to do all the work... but to do that you would need to put the data you have in column A into the database too, which almost certainly isn't worth it if you do it each time (ie: that data would need to be kept in the database rather than in Excel).



    In terms of places to learn about things like ADO and SQL, see our Database FAQs (link in my signature), which contains various articles and links that could help - but they wont cover all situations, as things are often specific. Note that it also does not contain general coding info (like not putting extra code in a loop), as that is not specifically related to database work.

  12. #12

    Thread Starter
    Lively Member
    Join Date
    Feb 2006
    Location
    London
    Posts
    90

    Re: ADO Select Sum of a recordset

    Thank for your advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width