Cannot open any more tables!

[bowldog]

In my report I have several subreports.When previewing report it looks fine,but when I try to email it about half way thru I get an error message "Cannot open any more tables". Has anyone encountered this before, if so is there a workaround with code maybe?

 

[nonturbo]

I'm sure I'm about as confused as anyone else reading this. I've never encountered this problem, nor heard of it, but I'm not saying it doesn't exist. You say you can preview your report fine. Can you print it? Does the print out look fine? How are you emailing it exactly? Are you using the VB SendObject command, or are you doing something weird? Are you sending through Access itself using Outlook's extensions? What version of Access? Are you attempting to email some report with a million subreports to a million different people? Where exactly is it crashing, will it send 5 of the 10 emails, or halfway through the first email it crashs. There are so many variables that its like calling your Car dealer and saying "My new car won't start, what's wrong with it?" and expecting a reliable answer.

 

[bowldog]

Thanks for taking time to help me with my problem.Here are the details: I'm using Access 2000/Windows 2000;Novell Groupwise for Email;I have a total of 10 subreports in the main report; I'm using SendObject command for Email to one person; when I printed the report the information stopped after the 8th subreport but the labels continued to print without any details under them.When I tried to Print; the "Cannot open any more tables" message popped up while it was running the queries, I pressed Ok and it resumed printing but only 8 out of the 10 subreports actually printed. After 5 subreports I did send the email to myself and everything worked fine. Maybe I need to break it into 2 reports!

 

[nonturbo]

How new are you to Access? The reason I ask is that you may be able to create the report without the use of so many sub-reports. I don't know the specifics of your database, but I frequently see newbies having problems with reports that are fundamentally poorly-designed, evaluate for yourself if this is truly the best way of creating your report. Maybe you can accomplish this by creating a few queries and some VBA, and not have **ANY** subreports at all, or reduce them greatly.. Many newbies get amazed once they realize what can be done with a little VBA.

Also, how much memory is in the machine? Are you running low? Bring up task-manager and watch the memory and processor usage with everything closed, then open access.. then open your report. If your report is killing the system, you'll see the memory spike up higher then the amount of RAM you physically have in the computer. Maybe buying more RAM will fix the problem.

Have you tried to open up the database on a different PC and print the report? Maybe it's a bad install of Access, or something else screwy with the machine.

..Also, I've never used Novell, so I can't help out there. Although that shouldn't matter at all at this point.
HTH!

 

[bowldog]

Thanks for the reply. It seems to maybe be a memory problem afterall. I have 256k of memory that goes down to 23-25k when trying to print the report then it stops with the "Cannot open any more tables" msgbox, is this normal?I'm using the task manager for the memory numbers. I'm going to try and use the NoData event to try and close the subreports that don't have any data.Is that a workable solution?Thanks in advance for help.

 

[nonturbo]

I think you mean 256 Meg of RAM, rather then K (Kilobytes), and if so, you should be fine. Although upgrading RAM might be an option to solve your problem, the prices on memory have been so low lately, its worth looking into.

Also, just out of curiosity, what are the main report and subreports' recordsources? Are they based on Tables, Queries, or Nothing (probably SQL queries set through VBA). I've got a feeling they're all based on Tables. Go ahead and double-check that.