[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Access 2010 Hanging when Generating Report

Posted on 2012-09-21
3
Medium Priority
?
816 Views
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.
0
Comment
Question by:lromero55
  • 2
3 Comments
 
LVL 58
ID: 38423010
<<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
 

Author Comment

by:lromero55
ID: 38436577
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
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 38437648
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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
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…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

873 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