Docmd.OpenReport fails after opening 13,902nd report? (error 3011)

[toeknee84]

Hi all,

We have a complex SQL Server based system in which reports are
automatically opened, then exported to PDF based on 'where' criteria.

I have pinpointed the problem as the following segment of code:

docmd.openreport strReport, acNormal, , strWhere

Basically after a report is opened (any report) for the 13,902nd time,
a 3011 error occurs.

The printer is WIN2PDF and automatically outputs the respect report
into different directories. I have ruled this out being the source of
the problem tho, by changing acNormal to acPreview. strWhere usually
involves a simple where clause like "CommissionID = 92 and ConsultantID
= 182".

There are SQL recordsets behind the reports, however changing these to
queries makes no difference, nor does basing them from dynamic to
snapshot.

Memory is not an issue as Access seems to use about 30,000K.

There are functions within the reports, though taking these out makes
no difference either.

It just seems that after recieving the error, no other database objects
can be opened AT ALL - basically Access becomes a dud. No error
messages - nothing comes up when you double click. When typing
docmd.opentable "Consultant" for example, it says "No Current Record"
(although there are 4000 oddwhat records).

The MDB file is in 2000 format, though I'm currently running through a
test after converting it to 2002/2003 to see if it makes a difference.

I must stress, the recordsources behind the reports are complex, but
surely this can't be the problem? Plus, after opening each reports,
there is checking to see if the report is open, and if so, it closes
it.

If there's no data in the recordsource behind the report, the On_NoData
will 'Cancel'.

I've mixed up the order in which reports are printed, yet it still
bombs out at opening a report for the 13,903rd time.

(only solution so far is to print reports in categories - say upto
10,000, then restart the database and continue)

Quite strange. A fully automated system would be nice...


Any advice would be helpful!

 

[Jeff Boyce]

It almost sounds as if Access/JET/?Windows fills up and can't handle any
more objects.

If you are opening a database object and/or a recordset object, and/or a
querydef object, and/or ..., are you also closing them and, where
appropriate, setting them to Nothing?

 

[Guest]

Access has a number of limits like this, which most of
us never see: this may be another.

I think that trying it in 2002/2003 is a good idea, but
if there is a limit, it will probably just be at a different
number, not gone entirely.

Another thing to consider would be to set the Has
Module property to "No" where possible, and the
use of "End" (to clear VBA) after opening the report

DoCmd.Open acreport, "rptname"
End

-- but I don't actually expect that to have any effect.
(david)