Alternative to domain aggregate functions

[Dean]

Has anyone toiled with creating/using alternate domain aggregate
functions? I have been messing with that a little. The one
recordsource I have been working indicates I get 20 to 40% savings in
processing time. I have not tried this with different recordsets yet.
I am curious what others might think.

What might be interesting if you could create temporary indexes. I did
try sorting the recordset on find first. It ran slower. I have not
tried to filter the recordset as I did on the dsum and dcount function.
Also, I am unsure if opening the recordset as a dbOpenSnapshot the best
solution either.

Option Compare Database
Option Explicit

Public Function mFirst(Field As String, domain As String, criteria As
String) As String
'Replacement for dfirst
Dim MyRS As Recordset
Set MyRS = CurrentDb.OpenRecordset(domain, dbOpenSnapshot)
Let MyRS.Sort = Field
MyRS.FindFirst (criteria)
mFirst = MyRS.Fields(Field)
If MyRS.NoMatch = True Then mFirst = "No Match"
End Function
Public Function mCount(Field As String, domain As String, criteria As
String) As Variant
'Replacement for dcount
Dim MyRS As Recordset, rsFiltered As Recordset
Set MyRS = CurrentDb.OpenRecordset(domain, dbOpenSnapshot)
MyRS.filter = criteria
Set rsFiltered = MyRS.OpenRecordset
rsFiltered.MoveLast
mCount = rsFiltered.RecordCount
End Function
Public Function mSum(Field As String, domain As String, criteria As
String) As Variant
'Replacement for dsum
Dim MyRS As Recordset, rsFiltered As Recordset
Set MyRS = CurrentDb.OpenRecordset(domain, dbOpenSnapshot)
MyRS.filter = criteria
Set rsFiltered = MyRS.OpenRecordset
rsFiltered.MoveFirst
While rsFiltered.EOF = False
'DoCmd.Echo True, rsFiltered.PercentPosition
mSum = mSum + rsFiltered.Fields(Field).Value
rsFiltered.MoveNext
Wend
End Function

[Arno R]

Look at what Trevor Best has made long time ago.
Download from http://easyweb.easynet.co.uk/~trevor/AccFAQ/
==downloads ==domain function replacement

Arno R

"Dean" <no*****@coveyaccounting.comschreef in bericht news:11*********************@i42g2000cwa.googlegro ups.com...

Has anyone toiled with creating/using alternate domain aggregate
functions? I have been messing with that a little. The one
recordsource I have been working indicates I get 20 to 40% savings in
processing time. I have not tried this with different recordsets yet.
I am curious what others might think.

What might be interesting if you could create temporary indexes. I did
try sorting the recordset on find first. It ran slower. I have not
tried to filter the recordset as I did on the dsum and dcount function.
Also, I am unsure if opening the recordset as a dbOpenSnapshot the best
solution either.

Option Compare Database
Option Explicit

Public Function mFirst(Field As String, domain As String, criteria As
String) As String
'Replacement for dfirst
Dim MyRS As Recordset
Set MyRS = CurrentDb.OpenRecordset(domain, dbOpenSnapshot)
Let MyRS.Sort = Field
MyRS.FindFirst (criteria)
mFirst = MyRS.Fields(Field)
If MyRS.NoMatch = True Then mFirst = "No Match"
End Function
Public Function mCount(Field As String, domain As String, criteria As
String) As Variant
'Replacement for dcount
Dim MyRS As Recordset, rsFiltered As Recordset
Set MyRS = CurrentDb.OpenRecordset(domain, dbOpenSnapshot)
MyRS.filter = criteria
Set rsFiltered = MyRS.OpenRecordset
rsFiltered.MoveLast
mCount = rsFiltered.RecordCount
End Function
Public Function mSum(Field As String, domain As String, criteria As
String) As Variant
'Replacement for dsum
Dim MyRS As Recordset, rsFiltered As Recordset
Set MyRS = CurrentDb.OpenRecordset(domain, dbOpenSnapshot)
MyRS.filter = criteria
Set rsFiltered = MyRS.OpenRecordset
rsFiltered.MoveFirst
While rsFiltered.EOF = False
'DoCmd.Echo True, rsFiltered.PercentPosition
mSum = mSum + rsFiltered.Fields(Field).Value
rsFiltered.MoveNext
Wend
End Function

[pietlinden]

Arno R wrote:

Look at what Trevor Best has made long time ago.
Download from http://easyweb.easynet.co.uk/~trevor/AccFAQ/
==downloads ==domain function replacement

Arno R

Sssshhhhh!!! Don't _tell_ him he's reinventing the wheel...

[Dean]

pi********@hotmail.com wrote:

Sssshhhhh!!! Don't _tell_ him he's reinventing the wheel...

Actually, Trevor took a different approach: He creates a query def
each time. I did find several post where the tcount was slower than
dcount. Also found in the comments that t functions is a replacement
for d functions on attached tables, not intended as replacement all
together. Based on these comments, I am assuming the built-in
functions run faster on native Access record sets rather than linked
tables.

I have I also found http://allenbrowne.com/ser-42.html which is more
similar to what I did which was utilize the existing record set. I am
going to some testing with this code.

[Rick Brandt]

Dean wrote:

pi********@hotmail.com wrote:

Sssshhhhh!!! Don't _tell_ him he's reinventing the wheel...

Actually, Trevor took a different approach: He creates a query def
each time. I did find several post where the tcount was slower than
dcount. Also found in the comments that t functions is a replacement
for d functions on attached tables, not intended as replacement all
together. Based on these comments, I am assuming the built-in
functions run faster on native Access record sets rather than linked
tables.

I have I also found http://allenbrowne.com/ser-42.html which is more
similar to what I did which was utilize the existing record set. I am
going to some testing with this code.

I disagree with your approach though. If you want an efficient way to "look up"
a record open a Recordset that contains only that record rather than opening the
whole domain and then do a FindFirst on the Recordset. If the criteria will
return more than one row and you want t6he first match then use a TOP 1 clause
with an appropriate ORDER BY.

If you want to do a count of a Recordset then use a SQL statement that uses the
Count() function rather that opening the entire domain and then moving to the
end and then using the RecordSet.Count.

It is in creating the most efficient query that you will reap the most benefits.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[David W. Fenton]

"Dean" <no*****@coveyaccounting.comwrote in
news:11*********************@m73g2000cwd.googlegro ups.com:

Actually, Trevor took a different approach: He creates a query
def each time.

Huh? As far as I can tell his t functions do nothing of the sort.

I did find several post where the tcount was slower than
dcount. Also found in the comments that t functions is a
replacement for d functions on attached tables, not intended as
replacement all together. Based on these comments, I am assuming
the built-in functions run faster on native Access record sets
rather than linked tables.

If I'm not mistaken, the t functions were indeed created to be
faster than the builtin domain aggregate functions on linked tables.
But I believe that problem was fixed in A2K or later so that the
domain aggregate functions were no longer slower on linked tables,
so Trevo'rs functions no longer had an advantage over them.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/