Report Restarts Itself And Never Completes

[VariableZ]

System and Version:
Windows 7 Professional 64bit
MS Office Professional 2010 version 14.0.5128.5000 (32-bit)
File format Access 2007
4Gb RAM

Background:
I have a report that is based off of a totals query that is based off of a query that consolidates a few queries that pull information from various tables while performing calculations on them. There are DCount, DMax, DLookup, IIF, Max, Right, and Left functions involved. The report Sorts on a field, Groups on another field, Sorts on another field, then Sorts on one more field. The table that most of the number crunching is done on is a linked table from an SQL db and currently has about 30,300 records with 70-80 records added every day. The report takes a great deal of time to run, which I'm ok with.

Problem:
About half the time, the report runs fine. The other half of the time it doesn't finish running, but begins to loop after a few lines, starting over and running until about the same point (not exactly the same point, though) and starting over again. Every time it does this, the text at the very top of the Access window reads "Not Responding" for a split second before the report restarts. Sometimes, if I leave it long enough, it will finally finish running, but usually it will cycle indefinitely. If I close Access and reopen it, the report usually runs fine until I run it a couple of times, then it starts acting up again.

Request:


Does anyone have any idea what could be causing this problem? I can provide formulas and sample data if needed.

- Zohar S.E.

[aytee111]

A sample database would be great.

[VariableZ]

I think I've attached a sample db. The .zip file is 544kb.

The default form is called frmLotDataConsolidated.

You type the model number into the field at the bottom right (labeled "Model#") and either press [Enter] or click the 'Open Report' button.

[aytee111]

The first thing to do is simplify it, the last qry is way too complex. What is Access doing when you reference the query inside the query itself, do you understand what is happening behind the scenes, how this is going to work? Recordsets, record pointers, algorithms, etc. I personally would not trust Microsoft to do this sort of thing for me, I would far rather do it for myself then I KNOW that it is working correctly.

Remove them all, each one of the DCounts and DLookups and make them separate queries. Then bring those queries in and link them together at the end. See if this solves it.

[VariableZ]

I've looked though everything, and I haven't found that any of the queries reference themselves. The last query references only the query it's based on, which has the same name, but without 'withtotals' at the end. That may have caused some confusion. [Edit] I think I see what you mean by referencing itself: The 'Calc01' and 'Calc02' fields, yes? Is that type of thing frowned upon in a query? [/Edit]

Are you recommending that I move the D-functions to a previous query or that I make queries that act as DCount, DLookup, etc? I can move the D-functions to previous queries (though I'm not sure I understand how that would help) but I don't know how to make a query that doesn't use them and yet provides the same functionality. If you have any examples of how to do that, I would be ecstatic.

[aytee111]

Check these out:

DMax("FormattedDate","qryLotDataConglomerate","[UniqueID] LIKE """ & [UniqueID] & """") AS MaxDate,
DCount("VINCode","qryLotDataConglomerate","[UniqueID] LIKE '" & [UniqueID] & "'" & "And [FormattedDate] <= #" & [FormattedDate] & "#") AS TotalVINToDate,
DCount("VINCode","qryLotDataConglomerate","[UniqueID] LIKE """ & [UniqueID] & """") AS TotalVINRun,
DLookUp("VINCode","qryLotDataConglomerate","Right([VINCode],7) = " & Min(Right([VINCode],7))) AS FirstOfVINCode,
DLookUp("VINCode","qryLotDataConglomerate","Right([VINCode],7) = " & Max(Right([VINCode],7))) AS LastOfVINCode,

I couldn't even get qryLotDataConglomerate to run successfully, I had to change it to having tables as input instead of queries for it even to run to completion (may be my computer).
For each of the above examples, how many queries are running? For each of the 30,000 records there are a minimum of three queries running 5 times (=15 times). Every time you do so much as a date format the query will slow down a lot. Then you add left joins which go over the recordsets more than once. It is overwhelming! I hate to even think what is going on behind the scenes.

By changing the first 2 queries to "make tables" I stopped all the continual calculations being done there. Improved performance immediately. Next I would do the same with the next query (qryLotDataConglomerate) - another saving on continually having to do calculations. Then I would remove all Dlookup's etc and put them in their own queries - using Totals - maybe even more make tables. Now everything would be simple, all calculations will already be done prior to coming in to the last query.

[VariableZ]

I've made a few changes based on your suggestions and my performance increase is astounding, but I wanted to run through a couple of things to make sure I'm not going to cause more problems in the future:

1) I got rid of one query by changing the table tblReceivedVINSheets to use the calculated field UniqueLotID calculated as Right("000000" & [LotNumber],6) & "_" & Left([FirstVINOfLot],8) & Mid([FirstVINOfLot],10,2) so that I wouldn't have to run a query to do the calculations. Is that an 'accepted practice'?

2) I put the code below into a JOIN statement to rid myself of another query. Access's 'Design View' doesn't like it one bit, but it works. I suppose my question is: Although I can do it, is there any good reason why I should not do it?

FROM (dbo_lotdata LEFT JOIN tblSkippedVINSheets ON dbo_lotdata.vincode = tblSkippedVINSheets.SkippedVIN) LEFT JOIN tblReceivedVINSheets ON RIGHT("000000" & dbo_lotdata.lotnum, 6) & "_" & LEFT(dbo_lotdata.vincode, 8) & Mid(dbo_lotdata.vincode, 10, 2) = tblReceivedVINSheets.UniqueLotID

3) Unfortunately, I haven't yet figured out how to move the D-functions away from the last query and still get the results I need. Erf.

[aytee111]

1) I don't know the answer to that one. Do some research, look up at what point in time those calculations are done. If they are done every time a record is accessed then it is a lot of overhead. If they are done when the record is inserted then that is definitely the way to go. Brilliant n fact.

2) Just remember - every time you call a function there is a lot of overhead. The "Right", "Left", "Mid", "Format", etc. are all functions.

3) I guess I just don't understand what you are doing here:
DCount("VINCode","qryLotDataConglomerate","[UniqueID] LIKE """ & [UniqueID] & """")
a) why LIKE, why not equals? Like compares character by character, equals does it all at once
b) If you make a copy of qryLotDataConglomerate and give it groupings, you can do the counts there, by UniqueID or Date or whatever you need. You may be able to combine them, but regardless, they should be out on their own
c) By changing qryLotDataConglomerate into a make table your next query should run a lot faster, even leaving everything else the same. It won't have to keep rerunning the query every time you reference it, it can just go to the table and get the data. Again, for every record it has to run the query for every DCount, First, Dlookup, etc.

[aytee111]

Are you winning?

[VariableZ]

Winning? Perhaps a bit. I still don't know the actual cause of the initial problem, but it seems to have gone away, at least for now. I've been able to increase the performance of the report far beyond what it was thanks to the use of delete/append queries and calculation modifications (I don't know why I used LIKE instead of EQUALS). I've run into a few odd problems as a result of all these changes, but I think I've finally gotten everything cleared up and usable. I appreciate all of your help. For future reference, the problems I ran into are as follows:

1) Database growth issues as a result of delete/append queries acting on the table. The delete query doesn't actually clear any space. It seems that it just marks the data as deleted (it no longer shows up in the table, which is functionally acceptable), but doesn't bother to go through the process of physically deleting it. The append query then adds more data, and soon the database is several times its original size. Compacting relieves the enlarged database issue.

2) If I write any VBA, the append query - which normally takes about 3 seconds to run - starts taking over a minute to run. When I compact the database, it goes back to working fine, though I can't figure out why.

3) After applying all the changes to the real database that worked perfectly in the test database, many calculated report fields - including those in some unrelated reports - kicked back "#name?" errors for no reason. After wrestling with that for a while and getting nowhere, on a hunch I copied the VBA from an affected report to Notepad and set the 'Has Module' setting on the report to 'No' so that Access cleared the VBA from the report. The "#name?" errors all went away. I copied the VBA back, and I haven't had a problem since. Access is so weird.

[aytee111]

1) That's right, Access doesn't clean up after itself, you have to do it.

2) Functions, remember? Any time you refer to a function - of any kind, whether one written by Access or one written by you - it adds a lot of overhead.

3) Can't agree more!