Another Query Question

  • Thread starter Thread starter Joe Williams
  • Start date Start date
J

Joe Williams

I have an access app linked to a SQL 2000 server for the back end.

One of the tables is a transaction file that has a large number of records
(200,000+)

I have a front end query that asks the user to enter a data range and this
limits the records returned. This takes FOREVER due to the large number of
records involved.

What is the best way to handle this situation?
 
Hi,
Does the SQL 2000 server table has any indexes?
If the yes, is there an index on the data range that you are using to limit
down the records returned.
 
Hi
There is an index, but it is NOT the date field that I am using as the
criteria. For this table the index is an incremental transactionnumber.
Where can I go from here to speed up this query?

THanks
Joe
 
Hi,
2 things.
1) Create an index on the date field that you are quering on the SQL Server
side. See if that will speed things up.

2) If create an index is out of question, then try to query on the
incremental transactionnumber instead of the date field.
 
Ok, I set up a pass through query and that returns records quickly, but how
do I pass parameters to the pass through query? (In this example, a date
range)

_ joe
 
Use DAO code to update the pass-through query's SQL property:
CurrentDb.QueryDefs("qsptYourPT").SQL = "SELECT ....WHERE DateField <= '" &
Forms!frmDate!txtEndDate & "'"
 
Back
Top