Slow "DoCmd" code ???

[martinpratt]

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.

Ok Thanks,

code for query is quite simple [I think]

SELECT DISTINCT wbom.pstk, wbom.issue, wbom.sstk, cst.u1, cst.u2, cst.desc2, cst.desc1, cst.desc3, cst.source, wbom.[no], wbom.desc
FROM wbom INNER JOIN cst ON wbom.sstk = cst.pstk
ORDER BY wbom.issue, wbom.sstk;

The Report has two sub reports embedded, but I removed both of these and it made no difference.

Thanks

Martin

 

[bob fitz]

If wbom is also a query please post the SQL

 

[martinpratt]

If wbom is also a query please post the SQL

'wbom' and 'cst' are both linked tables

 

[boblarson]

The use of Distinct could be the problem. Why must you use that?

 

[martinpratt]

The use of Distinct could be the problem. Why must you use that?

OMG....

I was using DISTINCT as the query was returning duplicate results and 'Group by' would not work, but DISTINCT did.

I removed distinct but the query failed to return the correct results. So I wondered about the 'unique identifier' that I picked when importing the table.... so I removed and re-linked the table 'wbom' but did not choose any identifier this time. The query now works again, and the report completes in around 6 secs....much better.

I do not claim to know why or how this works, but it does, so I am happy for now.

Thanks for all your help, I'm sure that I'll have some more questions soon

Martin

 

[RainLover]

Bob Fitz

I would agree with Bob. Limit the query and sub queries as much as possible.

I wonder what the Back End is. Jet, SQL or what. In any case the searched fields can be indexed in the Back End instead of the front end.

I would also leave any formating to the report not the queries.

Without the actual Database that is about all I can suggest.

 

[RainLover]

Too slow with my post.