By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,490 Members | 896 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,490 IT Pros & Developers. It's quick & easy.

Dcount -> SQL

P: n/a
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.

May 3 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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)

May 3 '06 #2

P: n/a
> 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
May 3 '06 #3

P: n/a
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)

May 3 '06 #4

P: n/a
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.

May 3 '06 #5

P: n/a
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.

May 6 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.