Slow "DoCmd" code ???

[martinpratt]

Hello all,

I am a complete noobie with access, but I have managed to get some working reports which run from queries, whose info comes from linked tables.

The problem I have is with the code running very very slow....

To give some details, I have a query which I have added two parameters. When I run this directly it take aroud 2secs to complete the query.

I have a report on this query which takes around 4secs to complete.

But, if I remove the parameters and use a form to send the required parameters, the report now takes more than 60secs to run!

The form is a simple two box, two button form with the following code....


Option Compare Database
Option Explicit
Private Sub cancel_Click()
DoCmd.Close acForm, "Show BoM", acSaveNo
End Sub
Private Sub show_bom_Click()
If Me![assy_no] <> "" Then
DoCmd.OpenReport "BoM", acViewReport, , "pstk = '" & Me!assy_no & "'" & "And issue = '" & Me!issue_no & "'"
DoCmd.Close acForm, "Show BoM", acSaveNo
Else
DoCmd.Close acForm, "Show BoM", acSaveNo
End If
End Sub


It seems to take around 56secs to before the form closes and the status bar says "running query" and then around 4secs later the report appears.

It seems to me to be a problem with the DoCmd.OpenReport code.

Any ideas to speed this up will be gratfully received.

Thanks you

by the way I am using MS Access 2010

Martin

 

[bob fitz]

Not sure this is correct but adding an index to the fields "pstk" and "issue" may help.

 

[martinpratt]

Not sure this is correct but adding an index to the fields "pstk" and "issue" may help.

Thanks, but not sure I understand.....

 

[bob fitz]

You have a table which has the fields "pstk" and "issue".
Open the table in design view.
Click the field that you want to create an index for.
Under Field Properties, click the General tab, click in the Indexed property box, click the arrow, and then click Yes (Duplicates OK)

 

[martinpratt]

You have a table which has the fields "pstk" and "issue".
Open the table in design view.
Click the field that you want to create an index for.
Under Field Properties, click the General tab, click in the Indexed property box, click the arrow, and then click Yes (Duplicates OK)

Thanks, but these are linked tables that I cannot change.

However, pstk is indexed 'Yes (No Duplicates)' and issue is indexed 'Yes (Duplicate OK)'

Thanks

 

[bob fitz]

As the form is so simple, I would create a new form and new controls and try again.

 

[martinpratt]

As the form is so simple, I would create a new form and new controls and try again.

Ok, finaly got around to re-creating this form, but with no luck.
I have also tried passing the info using a double click from another report, using the same code, but it still runs slow.

Thanks

Martin

 

[bob fitz]

Can you give us an example of the data in the two text boxes on the form.

 

[martinpratt]

Can you give us an example of the data in the two text boxes on the form.

pstk would be in format = "937-1402B"

and

issue would be in format = "3.2/E2054/04"

Thanks

 

[bob fitz]

Is the speed any different if you use:
DoCmd.OpenReport "BoM", acViewReport, , "pstk = '937-1402B' And issue = '3.2/E2054/04'"

insread of:
DoCmd.OpenReport "BoM", acViewReport, , "pstk = '" & Me!assy_no & "'" & "And issue = '" & Me!issue_no & "'"

 

[boblarson]

It isn't the form being slow. It is the queries running in preparation for the report to open. It is the report that is being slow.

 

[bob fitz]

It isn't the form being slow. It is the queries running in preparation for the report to open. It is the report that is being slow.

Perhaps setting the reports Record Source property in the reports On Open event would help.

 

[boblarson]

Perhaps setting the reports Record Source property in the reports On Open event would help.

That might help the form close quicker but the report will still take the same amount of time to open. What needs to happen is the queries making up the report need to be analyzed and modified to speed things up. For example, if there is a date limit then limit the date in ALL of the queries at their lowest level. Then only the applicable data will be returned. If it has to pull all data and then filter it, it will take much longer.

 

[bob fitz]

That might help the form close quicker but the report will still take the same amount of time to open. What needs to happen is the queries making up the report need to be analyzed and modified to speed things up. For example, if there is a date limit then limit the date in ALL of the queries at their lowest level. Then only the applicable data will be returned. If it has to pull all data and then filter it, it will take much longer.

I have the utmost respect for your knowledge in all aspects of Access. By comparison I'm still a babe in nappies, but in the OP's post #1 he says that the reports underlying query opens in 2 sec. Wouldn't that point to the problem being elsewhere? I'm only asking the question because that's what I thought and I thought you may have missed that bit of info.

 

[martinpratt]

Is the speed any different if you use:
DoCmd.OpenReport "BoM", acViewReport, , "pstk = '937-1402B' And issue = '3.2/E2054/04'"

insread of:
DoCmd.OpenReport "BoM", acViewReport, , "pstk = '" & Me!assy_no & "'" & "And issue = '" & Me!issue_no & "'"

There's an idea, I will need to try this tomorrow.

Cheers

 

[boblarson]

I have the utmost respect for your knowledge in all aspects of Access. By comparison I'm still a babe in nappies, but in the OP's post #1 he says that the reports underlying query opens in 2 sec. Wouldn't that point to the problem being elsewhere?

Actually I had the same exact problem. The queries would open quickly on their own. And the report took 4 minutes to open. So, I went in and looked at the queries and determined that there was stuff happening that wasn't necessary and also stuff that shouild have been there but wasn't. Like it pulling all records from throughout time for a particular item and it went fast by itself. But when coupled with everything else, including a where clause for opening the report - BOOM! it slowed to a crawl.

So, my advice still stands because I experienced this first hand only a few months ago.

 

[bob fitz]

Actually I had the same exact problem. The queries would open quickly on their own. And the report took 4 minutes to open. So, I went in and looked at the queries and determined that there was stuff happening that wasn't necessary and also stuff that shouild have been there but wasn't. Like it pulling all records from throughout time for a particular item and it went fast by itself. But when coupled with everything else, including a where clause for opening the report - BOOM! it slowed to a crawl.

So, my advice still stands because I experienced this first hand only a few months ago.

Thank you for the explanation and reasoning of your advice.

 

[martinpratt]

Is the speed any different if you use:
DoCmd.OpenReport "BoM", acViewReport, , "pstk = '937-1402B' And issue = '3.2/E2054/04'"

insread of:
DoCmd.OpenReport "BoM", acViewReport, , "pstk = '" & Me!assy_no & "'" & "And issue = '" & Me!issue_no & "'"

Ok, so in this configuration, when I click the button it takes around 19 secs for the status bar to say 'running query', and then around 4secs for the report to appear. Total around 23secs.



Martin

 

[martinpratt]

If I put the 2 parameters (937-1402B and 3.2/E2054/04) direct into the query and save it. Then just use

DoCmd.OpenReport "BoM", acViewReport

for the form button click, it only takes around 6 secs to produce the report.

Martin

 

[bob fitz]

Please look at my post #12 and that of boblarson #13.
IMHO boblarson is one of the best at resolving problems. He is very seldom wrong.
My understanding is that he feels the problem is with the queries used for the report. I'm not sure of the best way to go about finding the problem, but posting the SQL statement of the reports query is probably a good place to start.