Max Records

  • Thread starter Thread starter david epsom dot com dot au
  • Start date Start date
D

david epsom dot com dot au

I have a query with MaxRecords set to 200. This is an ODBC setting. It works
well. It is connected to an ODBC linked table.

I have a subform bound to dynamic SQL based on that query. It does not limit
to 200 records.

A select query based on a query does not inherit the subquery record limit.
If the selection is not made carefully, it may take hours to return.

What is the best way to setup my subform so that it only ever waits for 200
records maximum from the ODBC connection?

(david)
 
David

Not sure if I get the question correctly but have you try the TOP predicate
in the dynamic SQL?
 
but have you try the TOP predicate

Ok tried that.

Extra information: the query has an Order By clause.
If I use TOP 200 the recordset is sorted before the records
are selected (slow). If I use MaxRecords the recordset is
sorted after the records are selected (fast).

I'm now pushing the SQL into a predeclared qdf to get the
MaxRecords property, but I'm still open for suggestions about
the 'best' way to handle this subform. There are 1-2 million
records involved, the select query takes 0-10 seconds, the
sort clause takes 0-60 minutes.

(david)
 
Are you using the LinkMasterFields / LinkChildFields for the Subform?

IIRC, the most efficient method for Subform as per Microsoft is to use an
Access Query / JET SQL *AND* the LinkMasterFields / LinkChildFields rather
than Pass-Through Queries.

I am sure you are trying to use Indices as much as you can in selecting /
sorting ... I wouldn't want to sort 2 million Records without using Indices.
 
Back
Top