Report is very slow when run using VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Running Access 2003.

We have a query linked to an SQL Server database and a report using this
query. The query runs in only a few seconds. The report, when run directly
from the database window, also takes just a few seconds. But when the report
is run from VBA, because we usually want data for a specific period of time
and for a specific client, the report may take as long as two hours.

The report is run by the following command:
DoCmd.OpenReport stDocName, acPreview, , "clientname ='" & Me.cmbClientName
& "' AND cdate(TransferDateF) between cdate('" & Me.txtFra & "') AND cdate('"
& Me.txtTil & "') "

I guess the WhereCondition is the problem here. But the query is sorted on
both ClientName and TransferDateF, so I see no reason why it should take that
long to run.

Why is this taking so long? And what can we do to make it run faster?
 
Assuming that TransferDateF is a Date/Time type field, the CDate() is part
of the problem here.

Format the date as mm/dd/yyyy, and delimit with #.
Try this as the WhereCondition:

"([clientname] = """ & Me.cmbClientName & _
""") AND ([TransferDateF] between " & _
Format(Me.txtFra, "\#mm/dd/yyyy\#") & " And "& _
Format(Me.txtTil, "\#mm/dd/yyyy\#") & ")"

Is it possible to use the ClientID value as the bound column of the
(unbound) combo, instead of the name?


If that is still slow, open your table in design view, and index the fields
ClientName and TransferDateF, i.e. in the lower pane set the Indexed
property to "Yes (Dupes ok)".

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...
 
I tried eliminating the CDate() as you suggested, but did not achieve any
significant improvement.

But a fundamental question arises: Is (1) the query run first, the result
stored in a temporary table, and then the report run against these data, or
is (2) the query run with the WhereCondition attached to the query-SQL?

If (1) is the case, I don't understand why the WhereCondition makes the
report run THAT much slower than without it. If (2) is the case, missing
indexes on the TransferDate-field is probably the reason why this runs so
slowly.

Allen Browne said:
Assuming that TransferDateF is a Date/Time type field, the CDate() is part
of the problem here.

Format the date as mm/dd/yyyy, and delimit with #.
Try this as the WhereCondition:

"([clientname] = """ & Me.cmbClientName & _
""") AND ([TransferDateF] between " & _
Format(Me.txtFra, "\#mm/dd/yyyy\#") & " And "& _
Format(Me.txtTil, "\#mm/dd/yyyy\#") & ")"

Is it possible to use the ClientID value as the bound column of the
(unbound) combo, instead of the name?


If that is still slow, open your table in design view, and index the fields
ClientName and TransferDateF, i.e. in the lower pane set the Indexed
property to "Yes (Dupes ok)".

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
Running Access 2003.

We have a query linked to an SQL Server database and a report using this
query. The query runs in only a few seconds. The report, when run directly
from the database window, also takes just a few seconds. But when the
report
is run from VBA, because we usually want data for a specific period of
time
and for a specific client, the report may take as long as two hours.

The report is run by the following command:
DoCmd.OpenReport stDocName, acPreview, , "clientname ='" &
Me.cmbClientName
& "' AND cdate(TransferDateF) between cdate('" & Me.txtFra & "') AND
cdate('"
& Me.txtTil & "') "

I guess the WhereCondition is the problem here. But the query is sorted on
both ClientName and TransferDateF, so I see no reason why it should take
that
long to run.

Why is this taking so long? And what can we do to make it run faster?
Click to expand...
Click to expand...
 
To answer your question, Access actually runs it as a query into a query.

If the report's source query is named Query1, then the WhereCondition is
effectively the same as creating another query containing:
SELECT * FROM Query1 WHERE ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
I tried eliminating the CDate() as you suggested, but did not achieve any
significant improvement.

But a fundamental question arises: Is (1) the query run first, the result
stored in a temporary table, and then the report run against these data,
or
is (2) the query run with the WhereCondition attached to the query-SQL?

If (1) is the case, I don't understand why the WhereCondition makes the
report run THAT much slower than without it. If (2) is the case, missing
indexes on the TransferDate-field is probably the reason why this runs so
slowly.

Allen Browne said:
Assuming that TransferDateF is a Date/Time type field, the CDate() is
part
of the problem here.

Format the date as mm/dd/yyyy, and delimit with #.
Try this as the WhereCondition:

"([clientname] = """ & Me.cmbClientName & _
""") AND ([TransferDateF] between " & _
Format(Me.txtFra, "\#mm/dd/yyyy\#") & " And "& _
Format(Me.txtTil, "\#mm/dd/yyyy\#") & ")"

Is it possible to use the ClientID value as the bound column of the
(unbound) combo, instead of the name?


If that is still slow, open your table in design view, and index the
fields
ClientName and TransferDateF, i.e. in the lower pane set the Indexed
property to "Yes (Dupes ok)".


message
Running Access 2003.

We have a query linked to an SQL Server database and a report using
this
query. The query runs in only a few seconds. The report, when run
directly
from the database window, also takes just a few seconds. But when the
report
is run from VBA, because we usually want data for a specific period of
time
and for a specific client, the report may take as long as two hours.

The report is run by the following command:
DoCmd.OpenReport stDocName, acPreview, , "clientname ='" &
Me.cmbClientName
& "' AND cdate(TransferDateF) between cdate('" & Me.txtFra & "') AND
cdate('"
& Me.txtTil & "') "

I guess the WhereCondition is the problem here. But the query is sorted
on
both ClientName and TransferDateF, so I see no reason why it should
take
that
long to run.

Why is this taking so long? And what can we do to make it run faster?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top