[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:)

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

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

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:)

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

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
:)

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:thumb: but thats it:confused:

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

---

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").

I guess my inexperience shows!

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:blush:

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.

Thank for your advice. :wave: