If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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?

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.

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.

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

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

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

Re: ADO Select Sum of a recordset

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?

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:

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.