Dcount -> SQL

[Sheldon Mopes]

I posted this about a week ago, and I got some interesting responses,
but none of them acually answered what I was asking, so I'll try
again..

I have read a few articles that state that a multi-user app over a
network will run faster if DSum & DCount functions are replaced with
SQL statements replicating the functions.What would be a SQL statement
that would be functionally equal to DCount ? As I am a novice to SQL,
anysuggestions would be hugely appreciated. Thanks.

[Lyle Fairfield]

CurrentProject.Connection.Execute("SELECT SUM(Graft) FROM Politicians
WHERE Dishonest AND YEAR([Date]) = 2006").Collect(0)

CurrentProject.Connection.Execute("SELECT COUNT(*) FROM Foxes WHERE
Attitude LIKE 'Loves%'").Collect(0)

[PBsoft]

> I have read a few articles that state that a multi-user app over a

network will run faster if DSum & DCount functions are replaced with
SQL statements replicating the functions.What would be a SQL statement
that would be functionally equal to DCount ? As I am a novice to SQL,
anysuggestions would be hugely appreciated. Thanks.

In SQL you can use SUM and COUNT operators.

SELECT SUM(field1) AS field1Sum, COUNT(field2) AS field2Count
FROM myTable
GROUP BY fieldX, fieldY,.......

--
PBsoft di Gabriele Bertolucci
www.pbsoft.it
skype: pbsoftsolution

[Mark Reed]

Lyle, could you explain what .Collect(0) is doing? I've had a look through
Access help with no joy

TIA,

Mark

"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11**********************@e56g2000cwe.googlegr oups.com...

CurrentProject.Connection.Execute("SELECT SUM(Graft) FROM Politicians
WHERE Dishonest AND YEAR([Date]) = 2006").Collect(0)

CurrentProject.Connection.Execute("SELECT COUNT(*) FROM Foxes WHERE
Attitude LIKE 'Loves%'").Collect(0)

[Lyle Fairfield]

It's an undocumented function (both ADO and DAO) for
..Fields(index).Value.
In testing some years ago I found it is faster than anything except
declaring a specific reference to a field.
You can find many undocumented methods and properties in Access by
opening any Module, going to the Object Browser (<F2>), right clicking
and selecting Show Hidden Members. Some of these are useful. When you
do this the hidden members Hidden Members are not guranteed to work in
future versions of Access. I was warned about this ten years ago. But
they all still work.
The Object Browser is often much more helpful than Help, which shows
everything that should be available, while the Object Browser shows you
what is available.

[Mark Reed]

Cheers for the info Lyle, very helpful!!!!

Mark

"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11**********************@i40g2000cwc.googlegr oups.com...

It's an undocumented function (both ADO and DAO) for
.Fields(index).Value.
In testing some years ago I found it is faster than anything except
declaring a specific reference to a field.
You can find many undocumented methods and properties in Access by
opening any Module, going to the Object Browser (<F2>), right clicking
and selecting Show Hidden Members. Some of these are useful. When you
do this the hidden members Hidden Members are not guranteed to work in
future versions of Access. I was warned about this ten years ago. But
they all still work.
The Object Browser is often much more helpful than Help, which shows
everything that should be available, while the Object Browser shows you
what is available.