Access 2010 Hanging when Generating Report

Posted on 2012-09-21
Last Modified: 2012-10-10
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.
Question by:lromero55
    LVL 56

    Expert Comment

    by:Jim Dettman (Microsoft MVP/ EE MVE)
    <<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.


    Author Comment

    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.
    LVL 56

    Accepted Solution

    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.


    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now