-
Jan 18th, 2008, 12:46 PM
#1
Thread Starter
Lively Member
[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
-
Jan 18th, 2008, 01:46 PM
#2
Thread Starter
Lively Member
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
-
Jan 18th, 2008, 02:08 PM
#3
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
-
Jan 18th, 2008, 09:04 PM
#4
Thread Starter
Lively Member
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
-
Jan 19th, 2008, 12:56 AM
#5
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
-
Jan 19th, 2008, 03:20 AM
#6
Thread Starter
Lively Member
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
-
Jan 20th, 2008, 06:12 AM
#7
Thread Starter
Lively Member
-
Jan 20th, 2008, 08:10 AM
#8
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").
-
Jan 20th, 2008, 08:36 AM
#9
Thread Starter
Lively Member
Re: ADO Select Sum of a recordset
I guess my inexperience shows!
-
Jan 21st, 2008, 07:25 AM
#10
Thread Starter
Lively Member
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
-
Jan 21st, 2008, 07:52 AM
#11
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.
-
Jan 22nd, 2008, 06:25 PM
#12
Thread Starter
Lively Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|