Solved

Help in troubleshooting "Cannot open any more databases" (Error 3048)

Posted on 2010-11-19
22
3,507 Views
Last Modified: 2012-05-10
Hello,

I have Access 2003 app which uses multilevel tab controls.  I am running into the Jet db engine limit of 2048 handles to open tables.  I am trying to optimize and reduce the number of active connections and would like to determine the current number of "handles" being used.  Can the current index of the TableID or count be determined and reported at runtime?

Thanks,
Craig Harrington
0
Comment
Question by:cnharrington
  • 7
  • 6
  • 4
  • +2
22 Comments
 
LVL 75
ID: 34177524
Active connections?
Handles?
Index of TableID ?

Not quite following ?

mx
0
 

Author Comment

by:cnharrington
ID: 34177584
I am trying to find a way to monitor the conditions that cause Access to generate Error #3048 "Too many databsaes open".  The explanation thatfollows was copied from http://database.itags.org/ms-access-database/3406/
Thanks,
Craig

Error #3048 is usually caused by your database application trying to handle
too many tables at the same time. There are 2048 "table handles," or
TableID's, available for Jet 3.51 and 4.0 (1024 TableID's for Jet 3.0 and
3.5) that Access requires when dealing with each table or linked table.

TableID's are used for the following:
- Tables
- Queries
- Bound forms and reports
- Modules for forms and reports
- Combo boxes and list boxes whose row source is a table or query
- Data Access Objects (DAO) Recordsets in code
- Domain functions, such as DLookup( ), DCount( ), et cetera.

If this error is caused by the application using too many TableID's, then
ensure that every Recordset used in code is explicitly closed and the
Recordset variable set to Nothing before the variable goes out of scope. If
this is occurring only after the report is opened multiple times, then
you'll likely fix the problem by explicitly closing and setting objects to
Nothing in at least one code module

0
 
LVL 75
ID: 34177636
I see.
Just to be sure, try these two things first.

1) Compact and Repair the db.

If no luck, have you rebooted your system since this started (seriously).

I don't see this error to often, however ... it is fairly specific.  Unfortunately, it's not that easy to troubleshoot.  You have to look at every facet of the app and see here 'tables' are being opened. This includes recordsets, loops, combo/list boxes, aggregate expressions in controls, and many other scenarios listed in what you posted.

"multilevel tab controls. '
Can you be more specific ?

mx
0
 
LVL 75
ID: 34177674
Also, although in theory ... this should not matter once a Procedure loses scope, be sure that you Close all recordsets you open, and set the Object Variables to Nothing.

Function mFx()

Dim rst as DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SomeTableOrQueryName", dbOpenDynaset)
  ' code
  ' more code

rst.Close                  ' ****
Set rst = Nothing     ' *****

End Function
0
 

Author Comment

by:cnharrington
ID: 34177774
I agree on this being hard to troubleshoot.  This is why I was looking for a way to monitor the usage of "table handles," at runtime.  To answer your questions, yes to both.  The error is very repeatable and I am currently removing specific code segments in an attempt to isolate the more "handle" consuming components.  The effect seems fairly well dispersed which is depressing as it suggests I will have to revise my basic architecture.  In brief I have the following basic form/query layout.

Primary Form containing a couple text fields and a TabControl (TC1)
TC1 with 3 tabs
TC1-Tab1 contains 8 text fields and a subform (sf) (SF1).  SF1 has a 16 text fields and a list sf (SF2)
TC1-Tab2 contains a list sf (SF3)
TC1-Tab3 contans 6 fields and a sf containing another TabControl (TC2)

TC2 with 5 tabs
TC2-Tab1 has a sf (SF4).  SF4 has ~70 text,combox, checkbox controls
TC2-Tab2 contains a list sf (SF5)
TC2-Tab3 contains a sf (SF6) having 20 text,combox, checkbox controls and a sf (SF7).  SF7 has 10 text fields
TC2-Tab4 contains a sf (SF8) having 15 text controls, a Tab Control (TC3) and a list sf (SF9).  TC3 has 3 tabs with ~40 text,combox, checkbox controls
TC2-Tab5 has a sf (SF10).  SF25 has ~70 text fields.
Most of the form code modules make use of local recordsets or call public modules using local recordsets.

It would really be nice to see the current use of "handle" s during runtime.

Thanks,
Craig
0
 

Author Comment

by:cnharrington
ID: 34177787
I have spent the entire day looking for and cleaning up open recordsets.  I found a few but it did not cure the problem.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 167 total points
ID: 34178404
To me it just looks like your form has too many Objects on it. (is too complex)

Remember that objects like nested subfoms, and tab controls "Weigh" a lot.
From here: http://www22.brinkster.com/accessory/wip.shtml

Choose Control Types Wisely
Different control types have different performance "weights" associated with them. When choosing controls for a form, keep the following relative weights in mind:

Cost, Type
1      Rectangle
1      Line
1      Page Break
3      Label
5      Command Button
5      Option Button
5      Toggle Button
5      Checkbox
5      Tab Page
10      Textbox
10      Option Group
20      Image
25      Tab Control
40      Listbox
40      Combobox
45      Custom Control (OCX)
50      SubForm
50      Object Frame
50      Bound Object Frame

A quick count of your controls is about 240 objects...
For reference, a popular Access utility (Total Access Analyzer) sets it's "Form complexity" control limit to 50 controls per form/report by default...
http://www.fmsinc.com/microsoftaccess/BestPractices.html

It is also not clear how complex (or large) the recordset(s) are, ...Recordset code, calculated controls, Aggregate functions, inefficient variable datatypes, Un-optimized indexes, ...etc

All of these factors, coupled with the total amount of controls may just be enough to make Access cry uncle because it is low on system resources...

So consider simplifying the form, or breaking it up into separate forms...


JeffCoachman

0
 
LVL 75
ID: 34178408
" I found a few but it did not cure the problem."
Well, wasn't hopeful on that.

"This is why I was looking for a way to monitor the usage of "table handles," at runtime."
I am personally not aware of any way to do that.

You have an **extremely* complex form/situation.  My suggestion is ... you are going to need to scale back and rethink this design.  

mx
0
 
LVL 84
ID: 34179315
AFAIK this is managed internally by Access, and Access doesn't report on these things for you. Besides, if you can get at the information, what would you do with it?

I haven't seen your database, but as Jeff and mx have said, this is almost always caused by forms/reports which are too complex. This (the complexity) can often be caused by poor table structures, but it could just simply be a design that needs to be reworked.
0
 

Author Comment

by:cnharrington
ID: 34180691
While it appears that the use of Tab Controls and Subforms may be a large part of my problem I would like to confirm this before I rip it all apart.  re: "AFAIK this is managed internally by Access, what would you do with it".  Being able to track the resource overhead required by GUI components I currently have would help in determining a solution.  My only options at this point are to remove GUI objects out one by one and see if the problem goes away.  This ultimately doesn't help since I have no idea if my use of these limited handles is just under the 2048 limit or not.  So far I have to eliminate a couple subforms (it doesn't matter which ones) to eliminate the 3048 error code.  Again doing this may eliminate the error but I may still be just under the limit.  I am running Access 2003 and it is my understading that in Access 2007 and beyond the handle limit is half (1024) of the current making my problem much worse when Access is upgraded.

Sorry for the griping but this problem would seem simple to understand and deal with if the resource usage information (AFAIK) was just made public.  My opinion of the "form structue" is, yes it is complex but functional.  My use of subforms is primarily to allow reuse in various GUI contexts (save time and effort managing multiple identical data layouts) and embedded Tab Controls provide the ability to "drill down" into data relationships.

I am confused about where this resource limit resides.  Is it an MSAccess Object limit or is it a MS DAO Object limit.  If my problem is based primarily on my use of DAO recordset references in code then I might be motivated to migrate to an SQL Server be.  If my problem is truly coming from the fe architecture then I have to make various decisions about “reworking” the fe with the possibility of migrating to a more current Access/Office version or taking the big hit and moving out of VBA all together.

I have attached my be table structure and a few screenshots of the GUI elements I outlined above (CH_DB_GUI.zip).  While I understand it is impossible to comment on the underlying query/code structure simply from looking at my screen shots I would appreciate a general reaction to the “complexity” of the form layout and usage of Tab Controls and Subforms.  If what I am doing appears overly complex then I guess I have some “reworking” to do, if not then I’ll start digging elsewhere to solve this problem.  I just don’t want to waste my time chasing gremlins.

Thanks for all the help,

CH
 CH-DB-GUI.zip
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34183947
If you want a brute force solution to test, ...try increasing the amout of available memory.

Beyond that and, yes, it will be difficult to nail down all of the factors contributing to this.
It may be one thing, it may be a combination of a few things.

It issue with any fix will be that if the complexity increases again, you may have the same problem all over again.

My question would be at what point in the evolution of this db did this issue arrise?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:cnharrington
ID: 34184075
A significant change was made 5-6 mos ago which restructured the way contacts were being handled.  This is clearly at the root of my problem.  The 4 Contact related tables are used to provide a list of people (up to 2M of data) in a ComboBox control which is a field displayed in datasheet view. This datasheet view is embedded in a subform.  This basic structure is reused on 3 of the tab sheets in the App.  I can see from your "cost" info above that this will be costly.

The Access controls themselves are capable of handling the datasets and the overall App performance is acceptable.  My first indication of a problem was from a user who routinely left multiple form views active (rather than closing one view before opening another from the switchboard) as well as having multiple Apps running in addition to the Access DB.  I initially assumed the problem was not enough OS memory on the users PC since I could not recreate the problem on other PCs with less memory.  The final straw came with some word merging features that were added recently that relied on temporary 5 recordsets.  Performing the word merge would routinely push the "handle" count passed the limit and bark.  I can now dependably cause the problem and it is clear that the data I am trying to deliver as described above is the cause.

I have found some ways to trim data queries to optimize the data sourcing the ComboBox fields and the 5 temporary merging recordsets are now being constructed used and destructed one at a time.  My concern is all of these measures will undoubtedly only keep me just within the 2048 handle limit and with the specter of this limit being cut in half by moving up to Office 2007 or 2010 I would like to have a better alternative available for the long term.

I sm still interested to know..
I am confused about where this resource limit resides.  Is it an MSAccess Object limit or is it a MS DAO Object limit.  If my problem is based primarily on my use of DAO recordset references in code then I might be motivated to migrate to an SQL Server be.  If my problem is truly coming from the fe architecture then I have to make various decisions about “reworking” the fe with the possibility of migrating to a more current Access/Office version or taking the big hit and moving out of VBA all together.

Thanks Again,
CH
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34184992
"I am confused about where this resource limit resides."
There really is no real "Resource Limit" in MS Access.
There are limits, but none are really etched in stone, because, again, ...many things may contribute to an issue.

All the Limits of Access 2003 can be found in the Help Files:
Search for:  Specifications

FWIW, a while ago, this error was popular, but I doubt it is applicable here, but who knows:
http://support.microsoft.com/kb/815281

Perhaps LSM and MX can go into more depth on this topic...

JeffCoachman
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 167 total points
ID: 34185043
In looking at the jpgs, there is certainly some complexity.

Again, the error you are getting is pretty specific, ie,  it pretty much means what it says, unlike other errors in Access which are quite vague, and a given error as stated can be quite misleading.

One not so obvious limitation is this:

"Number of controls and sections you can add over the lifetime of the form or report 754 "
I'm not sure how that error renders itself, not likely the cause of the error you are getting.

Besides the 'physical' open recordsets that you can count ... tables, queries, DAO based, combo and list boxes, Access/Jet opens several hidden recordsets/tables as required for various circumstances.

Here is a list of known limits that are spelled out, for whatever it's worth:

http://office.microsoft.com/en-gb/access-help/access-2007-specifications-HA010030739.aspx

The only times I've run into this error is 3-4 times in the past when I've inherited some complex app.  Troubleshooting was never fun ... just start taking stuff out until it quits breaking, then try to revamp as required.

Sorry I can't be of more help ...

mx

0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 166 total points
ID: 34186587
This line speaks volumes to me:

"So far I have to eliminate a couple subforms (it doesn't matter which ones) to eliminate the 3048 error code"

It's complexity, pure and simple. Reducing the complexity could certainly make a difference. It's a natural design paradigm to make a "single interface" - that is, a form that includes everything needed. Rarely is this a good idea. It's better to use the concept of "chunks" of work. For example, if a user is working with a Company, and they need to see Contact, then open a separate Contact form, then close that form when done. It's more work, to be sure, but in most cases it's a better choice.

One thing you might do is use a SINGLE Subform control, and set the Source Object as needed. For example, on the Plans tab you have 4 different subforms. Instead of actually using those 4 Subforms, instead add a single subform, set your Tab control to be "short" (just large enough to actually see the tab, and then use the Change event of that Tab control to set the Source Object:

Select Case Me.YourTabControl
  Case 0 '/the first tab
    Me.MySingleSubform.SourceObject = "sfrmPlanInfo"
  Case 1
    Me.MySingleSubform.SourceObject = "sfrmPlanContacts"
  etc etc
End Select

This concept can be used throughout the application where "groups" of Subforms seem to cluster.

Doman functions like DLookups also open a connection, and can open more than one, depending on exactly what you're doing. If, for example, you use DLookup against a query, then you open a connection for the DLookup, for the query, and for each table included in that query.

Also make sure that the RowSources for queries are optimized, and ONLY use the tables needed.

If you can base a listbox/combo directly on a table, you'll save a few of those "connections". You might also try NOT setting the .RowSource of those items until the user moves into the control. This might make sense if a combo/list is not used very often.

I've not read where newer versions have a limit on the number of connections. Do you have a source for this?
0
 

Author Comment

by:cnharrington
ID: 34191186
Thanks LSM,MX,JC
I am, reluctantly, concluding that the "complexity" of the form structure I am using combined with the relational structure being used to manage "Contacts" is summing up to TOO MUCH.
re: suggestions

LSM1)  I have implemented a solution similar to LSM suggestion re: Single subform which only attaches a source object to the major tab subforms when the tab is active.  This change alone seams to solve the 3048 err.  The downside is slower response while refreshing the tab.  Users have gotten used to no delay and I expect some negative feedback.  They use these screens for reference during client phone conversations so I can understand their displeasure (the hem and haw with clients while waiting for data).

LSM2)  (DLookup, optimzation, list/combo based ontable) The suspect combobox is delivering available "Contacts".  Unfortunately, a Contact is a multi-table relationship. There are multiple locations throughout the GUI where a contact can be chosen from a combobox on a datasheet.  I am currently trying to unify this source for all combobox's by making it a public recordset.  No DLookups used but I will look at more indexing of key fields to regain some speed.

LSM3) My ref for comments regarding the FieldID specs are from the ref in my 2nd post above.
 http://database.itags.org/ms-access-database/3406/
"Error #3048 is usually caused by your database application trying to handle
too many tables at the same time. There are 2048 "table handles," or
TableID's, available for Jet 3.51 and 4.0 (1024 TableID's for Jet 3.0 and
3.5) that Access requires when dealing with each table or linked table."

I have no idea if these comments (ref: Gunny) are true or not since I cannot find an explicit Jet specification showing a "TableID".  The best I can find regarding this is an outdated MS KB article discussing MS ACCESS 7.0 AND 97.
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q165272
excerpt below
MORE INFORMATION
The Microsoft Jet database engine versions 3.0 and 3.5 allow a maximum of 1024 open TableIDs at one time. Microsoft Jet version 3.51 allows a maximum of 2048 open TableIDs at one time. A TableID is a recordset pointer that the database engine uses, and which is not exposed to the user. The Jet database engine may open multiple TableIDs during the execution of a single query; however, there is no accurate method to estimate the number of TableIDs that the Jet database engine will use for any given operation.

Above discusses increasing the TableID limtis in Jet 3.51.  It makes no sense to lower it in Jet 4.0.

I am currently debugging a version that quiets the 3048 err and will get some feedback on the form refresh delay from users.  My next effort may be to determine whether moving away from the Jet DB engine and into a SQL Server be would be worth the effort.  I am assuming this would remove the TableID issue altogether.

CH
0
 
LVL 84
ID: 34191322
Seems to me you're reading that backwards:

Jet 3.0 and 3.5 allowed 1024 handles (Access 97 and below)

Jet 3.51 and 4.0 allow 2048 (Access 2000 - 2003)

Newer versions (the ACE engine, Access 2007 and 2010) have the same as 3.51 and 4.0.

================

< I am assuming this would remove the TableID issue altogether.>

I'm not sure it would. This is more of a UI consideration than anything else. Before moving further along that path, I'd certainly do some more research to determine this.



0
 

Author Comment

by:cnharrington
ID: 34192253
Sorry, didn't mean to refer to Jet 4.0 regarding TableID limit returning to the lower 1024 limit. I meant to refer to Access 2007/2010.  Somewhere along the way I read a thread saying that was the case for Access 2007/2010.  Maybe I was mistaken thinking I saw it.  This was the primary reason for my looking for a way to eliminate the whole issue but if I can migrate to a newer version of Access without worring about having to squeeze 50% out of the TableID count I have now then I will live with it.

Thanks for the help All
Craig Harrington
0
 
LVL 84
ID: 34192557
AFAIK the limit hasn't been reduced with the move to the new ACE format. If you keep your databse in the .mdb format, you'll notice no difference in those regards.

However, I'd make every effort to reduce the complexity of your app purely from a performance and stability standpoint.
0
 

Expert Comment

by:Neliduff
ID: 34362281
Hi There
I am hoping someone can help me.    I have a database which acts as a diary seven days (from the current date) at any one time time.   For each day there are several sub forms pulling data relating to that date.  The underlying queries have the criteria Now(), Now()+1 etc to produce the display day by day.     There is a second copy of the diary where the user can enter a date and click, and the system opens a second form which reads the next seven days from the date entered.    It works perfectly (in  Access 2003) up to the point where I split it into a back end / front end system.    I then got the error message "Cannot open anymore databases".

There is a second issue - if I leave the file as one, while is works fine in 2003 - it I open it in 2007 it will open the first form, but when clicking to open the second it give the message "There isn't enouh memory to perform the operation"

Thanks
0
 
LVL 75
ID: 34362357
@Neliduff:
Please post a new question instead of tagging onto this one.  You can reference this Q if it applies to your Q.

mx
0
 

Expert Comment

by:Neliduff
ID: 34362392
Sorry - I am new and didn't realise it shouldn't go here
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

746 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

12 Experts available now in Live!

Get 1:1 Help Now