Access Select problem

[Doug]

Here is a seemly simple problem that is stumping me:

I have a select statement that uses Access' DISTINCTROW clause for performance reasons.
The problem is that ASP-db does a SELECT COUNT(*) FROM X WHERE Y to determine
the rowcount to control the buttons. Unfortunately, the COUNT(*) is different then when
you do SELECT DISTINCTROW A,B,C FROM X WHERE Y. Is there anyway I can tell
ASP-db to use the recordset's RecordCount instead of the default COUNT(*)? Or can
you think of any otherway I can get ASP-db to work properly?

Yes, I can write the SQL statement another way to avoid the DISTINCTROW, but I
was getting unexceptable performance. I will have to switch to SQL Server if this
doesn't work.

Any help would be appeciated,

-Doug

[Frank]

Doug,
The following fell thru the crack in the doc. Please try it and see whether it solve your problem.

* New property dbCountSQL. Use this property if your dbSQL is complicated and you do not want ASPDB to estimate and construct the SQL for counting records. If this property is not blank it'll be used to obtain accurate record count without any SQL construction. If "ORDER BY" exists in dbSQL, high speed [TOP] will be used. Otherwise, low speed counting will be used.

Example -> X.dbCountSQL="Select count(*) FROM Carspecs.


Frank




On 1/17/99 3:55:59 PM, Doug wrote:
> Here is a seemly simple problem that is stumping me:

I have a select
> statement that uses Access' DISTINCTROW clause for performance
> reasons.
The problem is that ASP-db does a SELECT COUNT(*) FROM X WHERE Y
> to determine
the rowcount to control the buttons. Unfortunately, the
> COUNT(*) is different then when
you do SELECT DISTINCTROW A,B,C FROM X
> WHERE Y. Is there anyway I can tell
ASP-db to use the recordset's
> RecordCount instead of the default COUNT(*)? Or can
you think of any
> otherway I can get ASP-db to work properly?

Yes, I can write the SQL
> statement another way to avoid the DISTINCTROW, but I
was getting
> unexceptable performance. I will have to switch to SQL Server if
> this
doesn't work.

Any help would be appeciated,

-Doug

[John]

Hi Doug,

The dbCountSQL property that Frank refers to is also in the manual. You can
always find the latest version of the manual and FAQ online by clicking on
Online Documentation on the main page (www.aspdb.com).

Thanks,
John

On 1/17/99 3:55:59 PM, Doug wrote:
> Here is a seemly simple problem that is stumping me:

I have a select
> statement that uses Access' DISTINCTROW clause for performance
> reasons.
The problem is that ASP-db does a SELECT COUNT(*) FROM X WHERE Y
> to determine
the rowcount to control the buttons. Unfortunately, the
> COUNT(*) is different then when
you do SELECT DISTINCTROW A,B,C FROM X
> WHERE Y. Is there anyway I can tell
ASP-db to use the recordset's
> RecordCount instead of the default COUNT(*)? Or can
you think of any
> otherway I can get ASP-db to work properly?

Yes, I can write the SQL
> statement another way to avoid the DISTINCTROW, but I
was getting
> unexceptable performance. I will have to switch to SQL Server if
> this
doesn't work.

Any help would be appeciated,

-Doug