Trying to salvage a report (1 Viewer)

[Micron]

If the report crashes on another pc I think you can rule out disk failure.

 

[Isaac]

My turn! "Have You Tried": Completely commenting out the VBA functions on which any portion of the report depends? To prompt some potential "other" type of response from the Report?
That's the thing about Reports...They promise all kinds of VBA accessible functionality, but I find it to be a delicate grasp on any defensible success rate. : ( Much less robust at handling "bad situations" it encounters, than queries are.

I actually have to hand it to Access. By enabling Queries that immediately accept VBA functions, it's like accomplishing the power of CLR's in SQL Server - but just a tad bit easier.

 

[demax182]

Ok, this is going to be long...

So with the SQL from the report's record source, I made a select query. Then I put a bunch of breakpoints in the "QueryFieldSeparatedAsString" and ran the query. What I found out was that it was going through the function endlessly, which meant that it was probably going through all the records. I figure this is what bogged down the report.

I also need to mention that the union query (quni_combined_assessments) calls on select queries and in those select queries, the "QueryFieldSeparatedAsString" function is used for one of their columns.

Next, I went into those particular select queries and input a criteria to narrow down their results. I found that after I did that, the report became a bit more stable, but still caused crashing. But at least it got me far enough to get into design view. However, if I switched between design view and print preview 2 or 3 times, it would crash Access.

From there, I went into the record source and deleted the union query out of it. Then I deleted all of the fields in the report that used to come from that union query. There's also a subreport I had that was based off the same union query, I deleted that as well. Lastly, there's a header in my report that had its visibility set according to one of the union query fields (which I deleted). I went back and forth through the report between Print Preview and Design View about 5 times and no crashing. It would have crashed by now. Good news is I kind of found out what was causing the crashing. The bad news is I need to figure out how to design this report without the union query...

 

[Isaac]

Ok, this is going to be long...

So with the SQL from the report's record source, I made a select query. Then I put a bunch of breakpoints in the "QueryFieldSeparatedAsString" and ran the query. What I found out was that it was going through the function endlessly, which meant that it was probably going through all the records. I figure this is what bogged down the report.

I also need to mention that the union query (quni_combined_assessments) calls on select queries and in those select queries, the "QueryFieldSeparatedAsString" function is used for one of their columns.

Next, I went into those particular select queries and input a criteria to narrow down their results. I found that after I did that, the report became a bit more stable, but still caused crashing. But at least it got me far enough to get into design view. However, if I switched between design view and print preview 2 or 3 times, it would crash Access.

From there, I went into the record source and deleted the union query out of it. Then I deleted all of the fields in the report that used to come from that union query. There's also a subreport I had that was based off the same union query, I deleted that as well. Lastly, there's a header in my report that had its visibility set according to one of the union query fields (which I deleted). I went back and forth through the report between Print Preview and Design View about 5 times and no crashing. It would have crashed by now. Good news is I kind of found out what was causing the crashing. The bad news is I need to figure out how to design this report without the union query...

Whew - that's a MAJOR breakthrough. I'm glad you got that progress! I am facing a thorny challenge in Excel right now and not making headway, (may post on AWF), so I'm glad somebody is making progress tonight. It had only occurred to me recently that the bottleneck may be in just about any underlying VBA function. I'm sure the union queries don't help (thankfully I've had very few instances needing to use them in Access), but good job on doing the breakpoints and evaluating the overall iterations that thing was going through!!

 

[Galaxiom]

The bad news is I need to figure out how to design this report without the union query...

Are you using UNION or UNION ALL ?
Without the ALL keyword the query will check for duplicates in the two sets of results. UNION ALL just loads them all.

I don't know about the situation in ACE but the query optimiser in SQL Server can struggle to get the right strategy with UNION queries. Sometimes putting the two subqueries around the other way can make a difference.

Do you have all the relevant indexes on the table to make the query run well?

Do your Select criteria depend on the results of the function? That is a always a recipe for terrible performance.

 

[Isaac]

Are you using UNION or UNION ALL ?
Without the ALL keyword the query will check for duplicates in the two sets of results. UNION ALL just loads them all.

I don't know about the situation in ACE but the query optimiser in SQL Server can struggle to get the right strategy with UNION queries. Sometimes putting the two subqueries around the other way can make a difference.

Do you have all the relevant indexes on the table to make the query run well?

Do your Select criteria depend on the results of the function? That is a always a recipe for terrible performance.

Certainly one simple 'test solution', coming from my very tired and simple mind right now, is to dump the results of both queries in a table and then switch that particular recordsource to the table. Would I intuitively think that's any faster than rendering one Union query? I'm not sure, but it might be simple to test .... and given the "Report" factor (having to page through records and render and format them), my gut says is worth a full test.
Excellent point about the Union/Union all ...... I recently suggested that as a faster method elsewhere and the OP reported it made no difference, which greatly surprised me.....

 

[demax182]

Yes, Isaac, good suggestion, which would have been mine as well. This has to be done incrementally to find which element does it. But just to grasp at straws (and we are talking desperate grasps),...

Can you at some point go to your explorer page, right-click on the drive where this app is kept, and tell it to run a CHKDSK? You will have to reboot the machine and wait for it to run, so maybe this is someone you can do overnight and just get the results in the morning. OR do some paperwork.

NOTE: You didn't say whether this was a traditional HDD or an SSD. If you are on a solid-state disk, this suggestion would be pointless, but on a traditional rotating platter style of disk, CHKDSK might find a cluster of bad blocks. Also, if you have not done one lately, do a disk optimization and don't let it tell you that it doesn't need one. Sorry to throw time-consuming issues at you but we are eliminating options and what is left is heading towards the bottom of the barrel.

Sorry, haven't responded to this. The laptop I'm running this on has SSD.

 

[demax182]

Are you using UNION or UNION ALL ?
Without the ALL keyword the query will check for duplicates in the two sets of results. UNION ALL just loads them all.

I don't know about the situation in ACE but the query optimiser in SQL Server can struggle to get the right strategy with UNION queries. Sometimes putting the two subqueries around the other way can make a difference.

Do you have all the relevant indexes on the table to make the query run well?

Do your Select criteria depend on the results of the function? That is a always a recipe for terrible performance.

I'm but a novice, but I'll try to answer your questions to the best of my ability. I am using UNION ALL because one of the columns is Long Text. If I did UNION, it would truncate the field if it's longer than a certain number of characters.

I probably don't have the relevant indexes on the table... To be honest, I'm not quite familiar with indexing. I guess now is the time for me to learn more about it. The only fields that are indexed are those used to create relationships between tables.

My select criteria do not depend on the results of the function. Rather, the results of the function is dependent on the criteria.

I'm going to look into indexing the appropriate fields in the tables under the query.

 

[Isaac]

My select criteria do not depend on the results of the function. Rather, the results of the function is dependent on the criteria.

Ok, what about this more specific: Do you have any references to a function in the Where clause, such that you are passing a column to the function--in the Where clause. Like: Where CustomFunction(ColumnName)="something"

 

[demax182]

Ok, what about this more specific: Do you have any references to a function in the Where clause, such that you are passing a column to the function--in the Where clause. Like: Where CustomFunction(ColumnName)="something"

Oh, for sure no.

 

[Isaac]

Ok, thanks for the reply. Galaxiom's suggestion to add appropriate indexes is very likely to improve things. It's really just a few basic concepts and a few moments in the table design....Perhaps a few more while applying the change & validating constraints, if you have a lot of data.