Unhandled exceptions during report outputs to snapshot files

[Peter B]

I have been trying to diagnose this problem for weeks now. I have a
FE/BE database system for which I want to automatically run reports
each night. The reports are written to snapshot files. There is a
series of reports that I generate, and in total, over 3000 snapshots
are generated. I have created a small VB script that opens MS Access
and runs a macro that runs code in VBA to gen the reports. I have
created a Scheduled Task to run the VB script every nite.

My problem is that Access ends with an unhandled exception, usually in
ntdll.dll, before all the reports are generated. This is not
repeatable. It happens on different reports every time. When I go
into my database to preview the report corresponding to the one that
Access bombed on, it works correctly. The other day, I tried to
manually launch the process about 6 times, and the abend occurred at 6
different places along the way.

I have managed to isolate the problem to just one of the reports, which
is a "combination" report that is composed of sub-reports. The best I
have managed to figure out is that there is a problem with one of the
subreports in the combination report. That is, if I disable the
combination report, then my processes and exports work fine.

Access does not offer me a debug opportunity or anything, making this
difficult to trace. I have searched the Internet looking for a
solution, and so far, have tried the following:

1) Completely rebuild the "combination" report from scratch
2) Did a decompile on the FE database, then a C&R, then a recompile,
and a C&R.
3) Followed Allen Browne's suggestions for fixing corruption, including
re-importing the entire FE database into a clean, new database, with
the Name AutoCorrect disabled.
4) I have created a VB script process that copies the FE/BE to a local
harddrive, and uses a printer connected to LPT1 to avoid the use of any
network resources, in case this was in issue with traffic.
5) I have changed printer drivers

So far, none of this has helped, and I cannot find any solution on the
internet. I have several suspicions, but no way to check them out:

1) My "combination" report is too complicated, and this creates a
problem;
2) I am suffering from "DLL Hell", and there is something about some
service pack, update, etc. etc. etc. on my PC that is conflicting with
Access;
3) For whatever reason, my database is VERY sensitive to corruption,
which may be the root cause of this problem.

I do not have a lot of confidence that sending the error reports to MS
will result in some quick turnaround solution from them, and I have no
way to decipher the error report to get to the root of the problem.

I am using a Win XP Pro SP2 box, with Access 2002 SP3. The database
itself is an Access 2000 format.

Does anyone have any thoughts on what might be going on?

 

[Ron Weiner]

My initial impression is 3000 Snapshots is a LOT!

I am suspecting some kind of a Memory leak somewhere either in the code you
wrote, or in Access. First thing I'd try, would be to break the single
batch down into two, three or more smaller ones, that are scheduled to run
about an hour apart. I'd hope that if it was a memory problem, when access
quits each batch all of the memory that was allocated during that run would
be freed allowing the next batch to have a "Clean slate".

Just my $.02

 

[Peter B]

Ron,

Good suggestions. For generating the reports, I have selected a
printer assigned directly to the LPT1 port, to avoid using the
spoolsv.exe (there was an issue at one time with this).

But, so you know, since I had isolated the problem to one of the
reports, I reworked the code to only export the problematic report
(reducing the number of snapshot files exported to around 1300). Even
under this situation, I get the error.

Also, I was monitoring memory usage with the TaskManager, and didn't
see MSACCESS.EXE ballooning, or anything else out of the ordinary.

 

[Ron Weiner]

If it were me I rework the logic to reduce the number to something really
low like 20. If that worked I would try 40, and so on. If you discover
that your batch can process 500 reports I'd go with it. You have not shown
us any of the logic or code you are using to deterring what to print when.
Are you creating any objects or resources that you are not releasing?

Also what does the Print spool have to do with creating a snapshot file? Am
I missing something there?

 

[Peter B]

Ron,

Let's say there are 1000 entities, and for each entity, I generate 4
reports. During the report generation/export process, I have a list of
the entities that I compile, and then enumerate thru that list to
generate the report "set" for each entity, as a series of snapshot
files. Each snapshot has a file name prefix associated with the entity
to make it easy to identify.

Regarding your first comment about number of reports:
On occasion, I can get all the way thru the entire process - no
problem. Other times, this thing blows up during the export for the
20th entity, and sometimes for the 669th entity. Completely
unpredictable.

So, I know it can get thru it all. My problem is the inability to
trigger the unhandled exception predictably, which would enable me to
debug it. I have inserted procedures in every event of every part of
the reports, and when an event triggers, I write an entry into a
tracking table. This has been only mildly helpful, as I cannot get the
error to repeat itself in the same place.

Regarding your second comment about object creation:
Yes - I create all kinds of objects, recordsets, etc. I am pretty
familiar with the object model in VBA and Access, and use it
extensively to build these reports. I understand that I should
"release" all objects once I am done with them in every procedure (Set
Object= Nothing). Recordsets are closed and then Set RecordSet=
Nothing. I have build a small procedure that does just that, and that
I call globally to close any recordset I open programatically.

Regarding print spooler:
To generate a snapshot file, I must still use a printer attached to the
report. If I use a networked printer, then Windows uses the print
spooler. So, to work around this, I have a printer driver installed on
the computer, and attached to LPT1. By doing this, I don't use Print
Spooler or any network related services (again, trying to eliminate the
network as the cause of this problem). Even if I use Print Preview, I
have to have a printer associated with the report just to preview it.
All my reports use the "Default Printer" rather than a specific one,
and this works fairly well. I can set the default printer external to
my application, without changing any code in the Access DB.

 

[strive4peace]

Hi Peter,

if any of your reports have images, here is a link that may
help you by Stephan Leban:

http://www.lebans.com/printfailures.htm

Stephan also says, "A couple of observations regarding this
issue:

"If you are using Image controls, set the Picture property
in the Print event NOT the Format event. Alos ensure you
have turned off the Loading Image dialog via the Registry.

"Do not use Lines or Borders that have a dot or dash style
setting. In many cases, each dot or dash gets converted to
an individual BltBit call."


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[strive4peace]

Hi Peter,

you wrote, "3) For whatever reason, my database is VERY
sensitive to corruption, which may be the root cause of this
problem."

I consulted another MVP, Tom Wickerath, regarding your
troubles and this is what he said:

"Perhaps Peter has a defective component in his PC (memory,
NIC card, etc.)." Tom goes on to talk about a previous
experience someone else had when their "Windows 2000 based
PC started experiencing frequent BSOD (Blue screen of death)
episodes, about once every 20 ~ 30 minutes. Each time
required a Ctrl - ALT - Del to restart."

Tom tracked the problem down to a defective floppy diskette
drive, "With the case open, you could see the floppy drive
spinning very inconsistently, ie. fast ---> slow and
struggling ---> fast ---> slow and struggling, etc. I
purchased a new floppy drive, installed it, and no more BSOD!"

Peter:

1. can the problem can be replicated on a different PC
without involving a network?

2. what brings you to the conclusion that your database is
corrupting frequently? Is the FE or BE trashed after a failure?

3. Have you established relationships between the tables in
his database?

again, quoting Tom...
"When one establishes a relationship, the foreign key field
is automatically indexed, although it will not be displayed
in the Indexes window in table design view. Indexes help
prevent table scans (ie. reading all records in a table to
solve a query). Given that the problem involves a report
with four subreports, I wonder if the possible lack of
indexes on the link child field(s) might be taxing his
system a bit too much.

4. What kind of free space do you have on your hard drive?
As an absolute minimum, you should have at least 10% and
more is better.

5. Does your environment variable point to a valid Temp.
folder on your system? Have you emptied out the temp folder
recently?

6. Have you run chkdsk /F and defragged your hard drive
recently?

"He has a very I/O intensive process to be reading all that
data from the hard drive. It would be nice to know that his
hard drive is not overheating due to an excessively
fragmented drive. Can he monitor the system temperature?
And, are all the dust bunnies cleaned out from this PC,
including vents?"

I will add something to the importance of cooling fans... I
lost 2 hard drives within a short period of time and my son
suggested that the temperatures were getting too hot. We
got a case with a bunch of fans in it and I haven't had any
hard drive failures since!

We did have some pretty powerful storms lately that had an
effect on my computer. For a couple days, my computer was
crashing right and left. I did a bit of housekeeping, ran
some utilities, and also ran Scandisk with complete surface
scan (took about 2o hours) and so far, no more problems!

As for cleaning: dust interferes with electrical connections
(as well as providing insulation to trap heat) so it is
important to keep your system as dust-free as possible. We
have a kit for your vacuum cleaner with small attachments
that we use to vacuum PC's. We also use canned air (don't
breathe it and keep it out of reach of children). To clean
the fans, alcohol on a Q-tip works good.

another thing to try is decompiling the database (back it up
first):

make an icon with this as its target:

"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE"
"C:\path\filename.mdb" /decompile

if your Access program is not located in the directory
specified, make the appropriate substitution

after you decompile, compile it if you have any code and
then do compact/repair

If you STILL have problems, please post the code you are
using -- another set of eyes might see something that you
have missed.

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[strive4peace]

ps

I want to add another point regarding indexes... how long is
the field you are using to relate your tables? If you are
using Autonumber/Long Integers, then that is efficient as
Long Integers take 4 bytes to store.

Recently, I troubleshooted a query someone was having
problems with. It has worked fine when they built is but
now that the database had lots of records, the query was
failing -- the problem turned out to be caused by linking
using a long text field. The link was changed to a short
field and the query ran lickity split with no problems.

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[Tom Wickerath]

Hi Crystal,

Some quick comments...

We did have some pretty powerful storms lately that had an
effect on my computer.

A good UPS (Uninterruptable Power Supply) that protects against voltage
spikes and sags (brownouts) can be very helpful in preventing corruption to
JET databases. I use a Smart-UPS manufactured by APC: http://www.apc.com
There are other reputable manufacturers as well. You can get a decent UPS for
a home office, for about $80 US (650 VA). I have a 1000 VA model, just to
give a bit more time available, after a power failure. Any component that
plugs into your PC should be protected by a spike protector, as a minimum. An
example is a printer. You don't need to provide protection from loss of
power, or temporary brownouts, for printers but you should definately provide
spike protection. Again, I use an APC product for this as well.

http://www.apc.com/products/family/index.cfm?id=21
and
http://www.apc.com/products/category.cfm?id=12&subid=65

We have a kit for your vacuum cleaner with small attachments
that we use to vacuum PC's.

make an icon with this as its target:

"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE"
"C:\path\filename.mdb" /decompile

You can make a generic shortcut that will work for the next database that
you open, by not including the path to the .mdb file, ie.:

"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" /decompile

A generic shortcut will work as long as you have not implemented Access
Security, assuming you are joined to the plain vanilla system.mdw file.



Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Tom Wickerath]

Peter,

If there is anyway that you can send me a copy of your database, complete
with data so that I can test it as you are, then I'll see if I can contribute
to helping you find a resolution. I will run an analysis of your database
using Total Access Analyzer as a start:
http://www.fmsinc.com/products/analyzer/index.html

If you are interested, send me a private e-mail message with a valid
reply-to address. Compact the database first, and then zip it. My e-mail
address is available at the bottom of the contributor's page indicated below.

Please do not post your e-mail address (or mine) to a newsgroup reply. Doing
so will only attract the unwanted attention of spammers.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[strive4peace]

Hi Tom,

It must have been environmental magnetic interferance as we
always unplug computers as well as the Internet connection
during storms. We also unplug applicances since last
summer, we lost our refigerator due to a storm. In that
storm, we also lost a switch and a NIC because we didn't
unplug the Internet when we unplugged power -- we were very
lucky it didn't go further into the computer!

Coincidentally, a very powereful coat hook magnet that I
have had for 15 years lost a great deal of its power last
week as well...

thanks for the info on the UPS

....computer vacuum kit ... I did say that wrong, didn't I ?
Well, I suppose our kit WOULD work with Peter's vacuum
cleaner too

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[Peter B]

Gents,

All of your responses are much appreciated. In some cases, what was
suggested had already been tried or checked. Here is where I am at
with this problem:
1) I have copied the database to two other computers (laptop and
desktop) and ran the procedure - both cases failed with the same
unhandled exception.
2) Suspecting a virus scanner issue, I disabled the virus scanner on
one of the systems. This has not helped.
3) I stumbled on some information about SubDataSheet property needing
to be [None] and the issue about NameAutoCorrect. I have addressed
this across the board, but it has not solved the problem.
3) I have created a small, 308 KB database, trying to replicate the
problem. This database has two reports - one report is a subreport of
the other report. The subreport has 4 MS Graph OLE objects embedded in
the report design. The database has 1000 entities, and each entity has
a set of information (involving 1000 datapoints) that is graphed on
each of the 4 graphs in the subreport.

Well, interestingly, I can get the error to occur (intermittently, and
randomly) on the small, test database. At the moment, I am
experimenting with two properties of the embedded OLE objects - the OLE
Type Allowed, and the Class. In OLE Type Allowed, I changed the
setting from Either to Embedded. In the Class, I have an issue - it
seems that no matter what version of Access I have, the Class for a
graph is always MSGraph.Chart.8. Curiously, I can edit this to read
MSGraph.Chart without any complaints from Access, but I don't know what
this does. So far, I do know that when I make these two changes, I
have successfully run through the report export to a snapshot without
the error. But, I was also able to do that with the default settings
for these properties.

Since this error is intermittent and unpredicatable, it's a little
tough to pin it down to a particular thing. But, at least I now have a
small DB that I can experiment with in short order.

 

[Peter B]

Also, here's some new information. I watched what was happening in the
TEMP folder when I dump out this snapshot. Here is the shocking
news...

1) During the export, my 1000-page snapshot is exported to a temp file
that comes perilously close to the 2 GB file size limitation (it was
about 1.86 GB).
2) At the end of the export, Access continues to do some processing
(obvious from the CP monitor), and generates another temporary file
that is about 65 MB in size, presumably from the contents of the first,
1.86 GB file.
3) Once complete, the 65 MB file is moved to the dir where my database
is located, and all the temp files are cleaned up/deleted.

I can't imagine why the export gens a 2 GB file, when the finished
product is only 65 MB. Pretty ugly, if you ask me. Unless, the issue
is the printer driver, and the 2 GB file has a bunch of print commands
that are then "translated" by Access into the SNAPSHOT format.

Is there a more compact printer driver I could use? I was using an HP
PCL 5 driver.

Gents,

All of your responses are much appreciated. In some cases, what was
suggested had already been tried or checked. Here is where I am at
with this problem:
1) I have copied the database to two other computers (laptop and
desktop) and ran the procedure - both cases failed with the same
unhandled exception.
2) Suspecting a virus scanner issue, I disabled the virus scanner on
one of the systems. This has not helped.
3) I stumbled on some information about SubDataSheet property needing
to be [None] and the issue about NameAutoCorrect. I have addressed
this across the board, but it has not solved the problem.
3) I have created a small, 308 KB database, trying to replicate the
problem. This database has two reports - one report is a subreport of
the other report. The subreport has 4 MS Graph OLE objects embedded in
the report design. The database has 1000 entities, and each entity has
a set of information (involving 1000 datapoints) that is graphed on
each of the 4 graphs in the subreport.

Well, interestingly, I can get the error to occur (intermittently, and
randomly) on the small, test database. At the moment, I am
experimenting with two properties of the embedded OLE objects - the OLE
Type Allowed, and the Class. In OLE Type Allowed, I changed the
setting from Either to Embedded. In the Class, I have an issue - it
seems that no matter what version of Access I have, the Class for a
graph is always MSGraph.Chart.8. Curiously, I can edit this to read
MSGraph.Chart without any complaints from Access, but I don't know what
this does. So far, I do know that when I make these two changes, I
have successfully run through the report export to a snapshot without
the error. But, I was also able to do that with the default settings
for these properties.

Since this error is intermittent and unpredicatable, it's a little
tough to pin it down to a particular thing. But, at least I now have a
small DB that I can experiment with in short order.

Hi Tom,

It must have been environmental magnetic interferance as we
always unplug computers as well as the Internet connection
during storms. We also unplug applicances since last
summer, we lost our refigerator due to a storm. In that
storm, we also lost a switch and a NIC because we didn't
unplug the Internet when we unplugged power -- we were very
lucky it didn't go further into the computer!

Coincidentally, a very powereful coat hook magnet that I
have had for 15 years lost a great deal of its power last
week as well...

thanks for the info on the UPS

...computer vacuum kit ... I did say that wrong, didn't I ?
Well, I suppose our kit WOULD work with Peter's vacuum
cleaner too

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[Peter B]

Update - I added some VBA code to my test database to export the
snapshots one file at a time. This is also part of my automated
process that is bombing on me. This also gets away from any TEMP space
issues associates with huge snapshot exports.

I started the export process before I left work yesterday. If
successful, I would have 1000 snapshot files parked on my c: drive in
the morning. Well, got here this morning, and after only 34 exports, I
have the same Access unhandled exception on my screen. I shut down,
relaunched Access and tried to export again, and after 70 exports,
Access bombed out on me.

I am going to try to repeat this a few times, and if I can, I can then
make changes to see what fixes it.

 

[Tom Wickerath]

Hi Peter,

Are you going to be able to send any type of a cut down sample DB to me,
which replicates the problem on your PC?


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Tom Wickerath]

Hi Peter,

I have tested the sample database that you sent to me, using two different
PC's. In both cases, all 1000 snapshot files were created without any
problem. At this time, I am unable to reproduce the failures that you are
experiencing.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[strive4peace]

Hi Peter,

Tom sent me your database and I tested it too -- ran fine in about 20
minutes with the points and number of graphs specified. Then I
increased the points to 100 and the number of graphs to 1000 -- took
about 3-1/2 hours, but it never failed

It is hard to suggest a solution for a problem that doesn't happen for
me, but I will look at the code and see if I see anything that you might
want to change...

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

 

[strive4peace]

Hi Peter,

I looked at your code. I see you have a sub to DelayApp that does
DoEvents (prob only need to do this once instead of a bunch of times)
....and I also see you commented it out. You might try just one DoEvents
statement

another thing you might try is creating the filename as a variable in
LoopReport instead of constructing it in your Access.DoCmd.OutputTo
statement

ie:

dim mFilename as string

mFilename = "c:\AccessBug\A" & VBA.Format(i, "0000") & ".snp"

it might also be good to test if the file is there
DIR(mFilename)
and KILL it first -- if is does not disappear, then use another filename


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

 

[strive4peace]

Hi Peter,

another note about DoEvents... you may want to put a counter in your
loop to generate the reports and only execute DoEvents once every 50 or
so times... throwing that in every now and then may help -- I have seen
systems where that was necessary...


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

 

[Peter B]

Crystal,

Thanks very much for all your comments. Let me offer a brief
explanation of what the DelayApp was for.

When I run this report, I notice in TaskMgr that there are as many as 4
instances of GRAPH.EXE (one for each chart object in the report). As
the reports generate, you can see the GRAPH.EXE instances come and go.
Between file exports, sometimes one or more of the GRAPH.EXE instances
stays in the TaskMgr list, as if the reports were being generated so
quickly that the GRAPH.EXEs couldn't shut down before another report
was being generated.

I SPECULATED that maybe this was causing the problem, so I did the
DELAYAPP function to slow things down between reports and allow all the
instances of GRAPH.EXE to shutdown gracefully before the next report
got generated.

I will try your other suggestions. As you and Tom can see, this is a
vexing problem for me. I can't reproduce the error "on command", and I
don't have any witnesses, either (in spite of your efforts - thanks for
taking the interest).

I will keep you both posted.