• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 822
  • Last Modified:

Access 2010 Hanging when Generating Report

Good afternoon all,

I've been trying to resolve this issue but have not been able to. I've been using Microsoft Access 2010 to generate a report. I use 4 queries that pretty much compile the report. I formatted the report differently so that it uses the three susbequent queries of the driving main query to generate subreports.

The issue I'm having, is that it works fine when generating maybe 20 records, but I've been using a sample parameter that generates about 350 records. At that point, it tells me Access is not responding when formatting the report. However, if I let it run for about 5-7 minutes, it will generally execute and display the report (which in print preview comes to about 56 pages).

I'm currently linking to four tables in our SQL Server 2000 database. When running the queries/reports individually, everything displays just fine. But when I run the main report that contains the three subreports... That's when it all breaks.

I've gone through my queries and deleted any erroneous data I had there for testing. I've been researching this all day and have not found any solutions to my problem. If you need to see my SQL, just let me know and I'll gladly post it.

Thank you in advance for your help in finding any reason or resolution to this.
0
lromero55
Asked:
lromero55
  • 2
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<That's when it all breaks.>>

 Breaks meaning it's just slow?   Note that with remote SQL data, it's often better to pull the data that you need in and work with it locally.

 If your queries are joining local JET tables with the SQL tables it will really slow things down.

 And yeah, we'll need to see the queries.

Jim.
0
 
lromero55Author Commented:
Thank you, Jim. I talked about it with a couple other developers and they all agreed that it would just take a long time in processing since we are indeed linking it to SQL server tables. Thank you for your assistance in this matter.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
If you are running the report on remote data, the critical thing to remember is to steer clear of using any JET specific expressions in the SQL.

 For example, if you use IIF() in your query, then this automatically forces JET to handle each row of the query and in some situations, can result in JET sending a query request to the server for every row.  If you have any expressions in your query outside of straight SQL (Sum(), Count(), etc), move them to the report.

 Also joining local JET tables with linked tables forces JET to handle the query.  If you need to do this, then it's faster to do make table queries for the SQL data that you need locally, then base your report on all the local tables.

Jim.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now