Solved

Printing Problem

Posted on 2016-10-14
13
31 Views
Last Modified: 2016-10-14
I suddenly have started getting this message when printing. I only have one database open.

Cannot open any more databases. (Error 3048)

You have reached the limit on the number of databases that can be opened at one time. Close one or more databases and try the operation again.
0
Comment
Question by:DatabaseDek
13 Comments
 
LVL 22

Assisted Solution

by:Ferruccio Accalai
Ferruccio Accalai earned 125 total points
ID: 41843260
Guessing that you open some queries in VBA code you must take care about the number of simultaneous connections to the same DB.
Do you close any recordset after executing the query? Do you close the reports correctly?
Seeing your code could give more input to help
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 125 total points
ID: 41843384
The error is not referring to connections to multiple external databases, it is referring to internal connections.

A "Database" can be many things in Access. for example, a ComboBox that is tied to a Query, or uses an SQL statement for a Rowsource, will open a "database". Subforms/Subreports use multiple "databases". Listboxes, etc etc - all of them will open a "database connection". Queries that use inline Domain functions, like DCount or DLookup, will use many database connections (essentially one per line).

Can you show us the query that drives your report, and also a screenshot of the report in Design mode?

Are you doing anything else in the database when you're opening this report? As Ferruccio suggested, we'd need to see the code that opens the report, as well as any code that runs in the report Events.
0
 
LVL 57
ID: 41843408
The error is somewhat of a catch all for a number of things that can go wrong when Access goes to work with a DB.

Besides the obvious error of actually trying to work with too many DB's, I've seen this error in three other cases:

1. DB is exceeding the 2GB limit in performing a process.

2. The internal table ID limit is being exceeded.

3. Number of locks is exceeds the lock limit.

    For #2, the internal table limit is approx 2048 (it floats a bit).    Every object, form, query, field, etc if referencing a table in some way, chews up a table ID.

 For example, a query has a reference for each table it's working with, and each field your working with carriers a table reference as well.   This is another reason why you simply don't output all fields in a query.

 So first, make sure your not anywhere near the 2GB limit on the DB (do a compact and repair).  Then for this report, make sure you don't reference anything you don't need.

 Last, increase the MaxLocksPerFile setting using SetOption to something like 200,000 to make sure your not running out locks.

Jim.
0
 

Author Comment

by:DatabaseDek
ID: 41843433
Do you think this might be the problem then. Tis a little complex. The code simply opens the report.  The query behind it has 210 fields.

I do have  4 Dlookups but deleting them did not help.
I am not using any Locks.
I don't know how I would find out if I am using more than 2 G in memory. Would Task manager help?
Capture.PNG
Capture1.PNG
0
 
LVL 57
ID: 41843447
<<Do you think this might be the problem then. Tis a little complex. The code simply opens the report.  The query behind it has 210 fields.>>

 Again, every field reference also carries a table reference.  Just looking at your report, I would think your bumping into the table ID limit.

 Quick test:
1. Open your report
2. Do a "save as" and save it under a new name.
3. Close the report.
4. Open the *new* report and delete all but a few controls.
5. Save
6. Now execute the new report.

 If you don't get the error, you have your answer.

<<I do have  4 Dlookups but deleting them did not help.>>

 That might not have been enough.

<<I am not using any Locks.>>

 Yes you are....you are always using locks in a DB.   In this case, read locks are getting placed when the report runs.

<<I don't know how I would find out if I am using more than 2 G in memory. Would Task manager help?>>

  Not memory....database size.   Access will often use some internal space to execute things....if that pushes the DB size over the 2GB limit, it triggers an error.
 
 Do a compact and repair on the DB.

Jim.
0
 

Author Comment

by:DatabaseDek
ID: 41843461
Yes I guess it was obvious really. Your suggestions are helpful and I will get onto it.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:DatabaseDek
ID: 41843485
Is it necessary to have all of these fields in the report. Most are not seen but if I want to show Frame height I have to have that field or control on the report (albeit hidden) even if the text on the report uses the Me.Print FrameHeight any way around this? If so I could remove most of the controls on the report.
0
 
LVL 57
ID: 41843506
<< Me.Print FrameHeight>>

 Not sure what your talking about here and even if the controls are hidden, there still would be a table reference.

 Your only option at this point would be to print some of the fields on one report, then print more info on a second.

 There is no workaround or change you can make for the internal table ID limit.

Jim.
0
 

Author Comment

by:DatabaseDek
ID: 41843643
This Jim

See I don't show any of the encircled controls (Fields) on the drawing. But they are referred to by the  report's code to create the drawing
Capture2.PNG
0
 

Author Comment

by:DatabaseDek
ID: 41843653
If I created a query based on the query that has 210 fields but only used one field from it, does access still count the entire 210 fields from the original query

Would I have the same problem working with Access 13 instead of 2002
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
ID: 41843667
So your actually rendering that drawing?  yikes, that must have taken some work.   But the bottom line is whether your showing the fields or not, they are consuming resources.

 I think if it were me, I would split off that piece of it.

 Do the left side as "page 1" in one report, then the drawing as a "page 2" in another.

 Not going to look as nice, but it shouldn't take too much work to split it up that way.

 I'd say do the drawing as a subreport, but I'm not sure that's going to get around the problem.  From what I know of reports, it works with everything at once.

 If you do want to keep the same format, then one possible workaround would to be generate the drawing before hand, save it, then place it in an image control when you run the report.   I'm not sure exactly what form that would take (maybe a PDF?), but that is something to at least think about.

Jim.
0
 
LVL 57
ID: 41843671
<<If I created a query based on the query that has 210 fields but only used one field from it, does access still count the entire 210 fields from the original query>>

  Yes, that uses 211 table ID's then.  Keep in mind that it's not what you are working with, but what Access is working with to do what you want.

<<Would I have the same problem working with Access 13 instead of 2002>>

 Yup.  The internal table ID was changed (forget when off-hand - believe it was with A2000) from 1024 to 2048, but that's been the only change in this area.

Jim.
0
 

Author Closing Comment

by:DatabaseDek
ID: 41843734
Thank you all

Very helpful

I do hope point share is fair

Derek
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now