[RESOLVED] ADO Recordset sum

[VBAhack]

I'm such a neophyte when it comes to ADO I can't even tell if what I'm asking is essentially the same as other recent posts.

I've created a recordset from Excel cell data, no connection at all, just creating recordset from scratch.

I use recordset.Filter to get the records I want. Now I want to sum the values for a particular field (i.e. column).

Do I need to loop through every record and sum or is there an ADO command to do it?

[si_the_geek]

It depends on how you are filling the recordset.. if you are adding items 'manually' then you probably can't do it easily (I think you would need to loop), but if you are filling it with an SQL statement you simply modify that to include a Sum.

[VBAhack]

Thanks Si. Yep, I'm doing it manually. OK, I'll explore the SQL angle.

[desmondno1]

Hi VBAHack

Im going through the same issues as you but this is what i did

Option Explicit


objRecordset.Open "SELECT SUM(INVVALUE)AS VatTotal FROM [Log$] WHERE PROJECTNUMBER = '" & JobNo & "'", objConnection, adOpenStatic, adLockOptimistic, adCmdText


Range("b2").CopyFromRecordset objRecordset



End Sub

Theres a lot more I need to find out.. read my post

Hope this helps

[VBAhack]

I managed to use SQL by first writing the recordset out to a text file, then creating a new recordset via SQL SELECT against the text file. Seems like a rather convoluted method to sum the values in a column, so I'll likely just loop through the records.

Question - is it possible to use an open recordset as the source for an SQL SELECT? If so, what would the syntax be? I've done a little playing around and don't think it is possible.

[si_the_geek]

I've never heard of a way to do it, so I don't think it is possible.


How is the data arranged in Excel? if it is in a standard 'table' layout, you could either use an ADO connection, or use the Excel object model to read the range directly into an array & then loop that (which presumably would be faster than building and looping a recordset).

[VBAhack]

The data is arranged in the spreadsheet in a fashion that is easy for entry/maintenance, but it isn't well suited to be read directly via ADO connection, so I just read the range into an array and created the recordset directly.

Actually, I tried using a connection to read the data directly from Excel, but ran into two problems:

- It only worked when the spreadsheet was closed. I'm guessing that's a requirement - the Excel spreadsheet to be read with a connection can't be open.

- Most of the values (numerical) weren't being picked up. After thinking about it for a while, I realized what must be the problem - many of the cells are blank. I guess this would be considered a mixed format. Is there a workaround for this short of making sure all blank cells contain zero?

[si_the_geek]

Ah, that doesn't sound like it will work.. but rather than put the data into a Recordset, you could put it into a different, more specific, array (perhaps of a User Defined Type).

I'm not sure, but I think that would be faster overall (as long as you don't ReDim too often when making it - and there are tricks for that).

[VBAhack]

si, thanks for your comments. I think I understand the options. It's just a matter of playing around and deciding which is best. I'm actually favoring the ADO / SQL approach because I like the filtering, sorting, etc. capabilities. It's cool. Thanks again.