Replacing DCount With SQL Statement

  • Thread starter Thread starter croy
  • Start date Start date
C

croy

In an old message (6 Nov 2003) in
[...] We replaced the DCOUNT call with an equivalent
SQL statement that counted the records as desired.
And wala ... the form is filling fast again.
Click to expand...

I thought DCount *was* an SQL statement.

Could someone please help me understand this?

I've got some places where this might speed things up.
 
croy said:
In an old message (6 Nov 2003) in
[...] We replaced the DCOUNT call with an equivalent
SQL statement that counted the records as desired.
And wala ... the form is filling fast again.
Click to expand...

I thought DCount *was* an SQL statement.

Could someone please help me understand this?

I've got some places where this might speed things up.
Click to expand...

It probably gets translated into one, at least it should, but it is a VB
statement to start with.
Switching to straight SQL is a lot faster but you are not likely to notice
it on forms. In a report it can make a big difference.
 
croy said:
In an old message (6 Nov 2003) in
[...] We replaced the DCOUNT call with an equivalent
SQL statement that counted the records as desired.
And wala ... the form is filling fast again.
Click to expand...

I thought DCount *was* an SQL statement.

Could someone please help me understand this?

I've got some places where this might speed things up.
Click to expand...

It probably gets translated into one, at least it should, but it is a VB
statement to start with.
Switching to straight SQL is a lot faster but you are not likely to notice
it on forms. In a report it can make a big difference.
 
croy said:
In an old message (6 Nov 2003) in
[...] We replaced the DCOUNT call with an equivalent
SQL statement that counted the records as desired.
And wala ... the form is filling fast again.
Click to expand...

I thought DCount *was* an SQL statement.

Could someone please help me understand this?

I've got some places where this might speed things up.
Click to expand...

It probably gets translated into one, at least it should, but it is a VB
statement to start with.
Switching to straight SQL is a lot faster but you are not likely to notice
it on forms. In a report it can make a big difference.
 
croy said:
In an old message (6 Nov 2003) in
[...] We replaced the DCOUNT call with an equivalent
SQL statement that counted the records as desired.
And wala ... the form is filling fast again.
Click to expand...

I thought DCount *was* an SQL statement.

Could someone please help me understand this?

I've got some places where this might speed things up.
Click to expand...

It probably gets translated into one, at least it should, but it is a VB
statement to start with.
Switching to straight SQL is a lot faster but you are not likely to notice
it on forms. In a report it can make a big difference.
 
In an old message (6 Nov 2003) in
[...] We replaced the DCOUNT call with an equivalent
SQL statement that counted the records as desired.
And wala ... the form is filling fast again.
Click to expand...

I thought DCount *was* an SQL statement.

Could someone please help me understand this?

I've got some places where this might speed things up.
Click to expand...

DCount() is a builtin VBA function, which can be called from a query if you
wish, and if the query is running in Microsoft Access; e.g. a query SQL
statement referencing DCount() will not be portable to SQL/Server or MySQL or
any other platform.

In contrast, the Count operator is standard, portable SQL, that works in
Access, SQL/Server, MySQL, DB2, or any other implementation of the SQL
standard.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
Thanks Mike.

croy said:
In an old message (6 Nov 2003) in
[...] We replaced the DCOUNT call with an equivalent
SQL statement that counted the records as desired.
And wala ... the form is filling fast again.
Click to expand...

I thought DCount *was* an SQL statement.

Could someone please help me understand this?

I've got some places where this might speed things up.
Click to expand...

It probably gets translated into one, at least it should, but it is a VB
statement to start with.
Switching to straight SQL is a lot faster but you are not likely to notice
it on forms. In a report it can make a big difference.
Click to expand...

So I've got this form... with some controls that use
DLookup as the Control Source.

How do I switch that to "straight SQL"?
 
croy said:
So I've got this form... with some controls that use
DLookup as the Control Source.

How do I switch that to "straight SQL"?
Click to expand...

You really do not need to.

The domain functions DCount(), DLookup(), etc., incur some processing
overhead each time they are used. This overhead is completely unrelated to
how efficiently they retrieve their data which is every bit as efficient as
using any other method (like "straight SQL").

The problem is when you use them in a query or in a looping operation
because that processing overhead is incurred for every single row of the
query and every single iteration of the loop. THAT is when domain functions
suck and when their use is not recommended.

Using them a handful of times on a form is not going to incur that much of a
penalty and replacing them with something else will make very little
difference. In fact most replacements will not make any difference at all
if you do not take care to not invoke the same overhead that the domain
functions do.

Now, if you're talking about a continuous or datasheet form then as stated
above you should try to avoid them. In those cases one would normally try
to get the base query of the form to use a sub-query or other "straight SQL"
solution.

If you're talking about a standard one-record-at-a-time form then don't
bother to change anything.
 
You really do not need to.

The domain functions DCount(), DLookup(), etc., incur some processing
overhead each time they are used. This overhead is completely unrelated to
how efficiently they retrieve their data which is every bit as efficient as
using any other method (like "straight SQL").

The problem is when you use them in a query or in a looping operation
because that processing overhead is incurred for every single row of the
query and every single iteration of the loop. THAT is when domain functions
suck and when their use is not recommended.

Using them a handful of times on a form is not going to incur that much of a
penalty and replacing them with something else will make very little
difference. In fact most replacements will not make any difference at all
if you do not take care to not invoke the same overhead that the domain
functions do.

Now, if you're talking about a continuous or datasheet form then as stated
above you should try to avoid them. In those cases one would normally try
to get the base query of the form to use a sub-query or other "straight SQL"
solution.

If you're talking about a standard one-record-at-a-time form then don't
bother to change anything.
Click to expand...

Thanks Rick--good info!

My forms are single-record forms, so it looks like they're
OK as is.
 
Frequently, DLookup()s can be eliminated by changing the form's RecordSource to a query that joins to the table containing the data you want to look up. The ONLY time I use DLookup() or other domain function to populate a field on a form is if joining to the table/query will make my form not updateable. For example, if you want to show a customer's outstanding balance on each order form, you would create a query that sums any unpaid invoices. If you joined to that query, the resulting query would be not updateable because some part of the query was not updateable (the totals query). So, in this case, I would use a DSum() to show the outstanding balance. Another case is if the data sources come from different databases. I have had applications that contained primarially Jet/ACE tables but used tables in a different RDBMS for lookup. Rather than join an ODBC linked table to a linked Jet/ACE table to get customer information to display on an order form, I would either use DLookup() or DAO/ADO code behind the form to populate the lookup fields.
In an old message (6 Nov 2003) in
microsoft.public.access.tablesdbdesign, Susan wrote:


I thought DCount *was* an SQL statement.

Could someone please help me understand this?

I have got some places where this might speed things up.

--
Thanks,
croy
Here is the link I meant to add...

<https://groups.google.com/group/mic...sign/browse_frm/thread/ece95f239774df5a?hl=en>
Click to expand...
Click to expand...
 
Back
Top