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.
Peter B wrote:
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.


strive4peace wrote:
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

*

Tom Wickerath wrote:
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.


You have a kit for Peter's vacuum cleaner? G, D & R


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/ex...tributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

"strive4peace" "strive4peace2006 at yaho" wrote:


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

*

Peter B wrote:

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.

Ron Weiner wrote:


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?

--
Ron W
www.WorksRite.com
"Peter B" wrote in message
legroups.com...


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 wrote:


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

--
Ron W
www.WorksRite.com
"Peter B" wrote in message
oglegroups.com...


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?