Cannot open any more databases (error 3048) - Where to from here?

[Guest]

In Access 2003 with a split database...I am running into this limitation in
virtually every new project and have about given up on Access. I am using
some quite complex queries nested several levels deep, but am trying to
intentionally limit the number of subforms/subreports in order to avoid this
problem. Am I the only one having this problem? Maybe it's just bad design
on my part?

I'm thinking that Access (or Jet, I suppose) is just not "robust" enough (as
they say) to handle what I am asking it to do. I read on a post here that
there is a limit (256 open, as I recall) and that does not seem like a lot to
me for a complex app. I'd hate to have to learn another language and then
find out it has a similar limitation. Any suggestions (other than obvious
one of combining things into one database)would be appreciated.

 

[Allen Browne]

Perkon, this is not a limit I hit very often. I'm not sure if your question
is voicing a frustration, or seeking information about how to circuit the
issue.

Assuming the later, avoiding these kinds of situations may help:

1. Domain aggregate functions in your queries. Since Access has to execute
them for every record, this uses them up very quickly.

2. Many combos/list boxes. Since each one has its own RowSource, you can hit
the limit in (e.g.) a rostering application where you have lots of the same
combo (staff) on the same form (lots of fields, not just lots of records).
It may be possible to avoid the problem by using a call-back function
function to load the combos.

3. Many open forms/reports, with subforms/subreports, using many queries, or
using code referring to their RecordsetClone.

4. Failing to close recordsets you open, and set the objects to Nothing.

 

[Tony Toews]

In Access 2003 with a split database...I am running into this limitation in
virtually every new project and have about given up on Access. I am using
some quite complex queries nested several levels deep,

I have occasionally had this problem but I was unioning 10 or 12
different queries based on the same table. So I created a temporary
table to hold interim results and based the report on that table.
Made the report simpler too.

See the TempTables.MDB page at my website which illustrates how to use
a temporary MDB in your app.
http://www.granite.ab.ca/access/temptables.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Guest]

Certainly some of both...possibly leaning towards the frustration side.
Sorry to sound "whiny".

1. I do stay away from domain aggregate functions when possible, and there
were none in play here.

2. The problem occurs even when just opening the report directly. I do
have some situations where I use forms with a lot of combo boxes though. I
am not familiar with the call-back function, but I will investigate.

3. There are three subreports. One of which uses a union query nested
about four levels deep on top of other union queries. I also use some of the
same sub-queries for the main report. I think this is what's putting me over
the top. There is some minimal code inolved, but nothing to do with
RecordsetClone.

4. I do try to make sure other recordsets are closed...but I am not taking
any particular actions to set the objects to nothing. I'll have to
investigate how to do that and try it.

Tony Toewes had also responded to my initial post and suggested using some
temporary tables. I have used that technique in the past (although not to
solve this kind of problem) and I could see it helping in this case. It's
something a little easier for me to get my head around, too. For now, since
I'm on a deadline, I've just had to "un-split" the database just to get the
app to work. I'll try your advice and maybe I can split it up again after I
get the initial install done.

Let me re-echo the praise and thanks I see on this site. It is without a
doubt my main resource for Access help, and I can't tell you how great it is
to hear from real people when there are problems. I don't know how you guys
keep up. I hope someday I will be able to help someone else out here.

 

[Guest]

That's about my situation with the number of union queries...plus I'm using
the same queries over again for the main report and one of the three
subreports. I've used the temporary table technique you suggested (although
not to solve this particular problem) in the past and it strikes me as this
is the best way to go.

I really intentionally avoided doing so in this app just to be "neat"...but
I think I outsmarted myself. I'm on a deadline so I've had to "un-split" the
database for now just to have a useable app.

I'd had a response from Allen Browne too and I expressed my general thanks
to all of you on this site. I am a frequent user and virtually always find
some help.

 

[Allen Browne]

Sounds like Tony correctly guessed the cause of your issue. He's always good
value, and his suggestion makes good sense.

The call-back function won't help this case, but if you want an example of
how to create one of these cryptic things, there's one here:
http://allenbrowne.com/ser-19.html

It's generally good programming to close everything you open (but only what
you open), and set your objects to Nothing before exit. There was a case in
Access 97 where failing to do so would leave resources unreleased, and
Access could not be closed. Ever since then, many of us have been
super-cautious about ensuring that we close our recordsets (if you opened
them, e.g. not RecordseClone) and set them to nothing:
Dim rs As DAO.Recordset
Set rs = dbEngine(0)(0).OpenRecordset("Query1")
'meaningful stuff here
rs.Close
Set rs = Nothing

Anyway, back to creating those temp tables.

 

[Guest]

I think so too...now I've got some rewrite to do...but I guess this is how we
learn.

Re: callback function. "Cryptic" is the word all right. I think I'll save
trying to figure this one out for later.

I had found the rs.Close and Set rs = Nothing code lines from earlier posts

 

[Moshe Alon]

All this good advices in many forums will not always solve the problem.

I have a very "loaded" application and use a lot of forms and combo-boxes

I solve the problem by using this very simple procedure to empty the
combo/list box on deactivate the form and refill them on activate the form
again.

At the top of the form declare:
--------------------------------
Dim MIs_AttchFlds As Boolean


At the "Form Open" add:
-----------------------
MIs_AttchFlds = True


At the "Form Activate" add:
-----------------------------
If Not MIs_AttchFlds Then Call AtchFlds("Form_Name", True)
MIs_AttchFlds = True


At the "Form DeActivate" add:
-------------------------------
Call AtchFlds("Form_Name ", False)
MIs_AttchFlds = False

The Procedure:
--------------------
Sub AtchFlds(MFormName As String, MIsAttch As Boolean)
On Error GoTo Err_AtchFlds
Dim Mfrm As Form, MCtrl As Control
Set Mfrm = Forms(MFormName)
With Mfrm
For Each MCtrl In .Controls
With MCtrl
Select Case .ControlType
Case acListBox, acComboBox, acObjectFrame
If MIsAttch Then
.RowSource = .Tag
.Tag = ""
Else
.Tag = .RowSource
.RowSource = ""
End If
End Select
End With
Next
End With
Exit_AtchFlds:
Exit Sub
Err_AtchFlds:
MsgBox ("AtchFlds Err." & Err.Number & ", " & Err.Description)
Resume Exit_AtchFlds
End Sub

url:http://www.ureader.com/msg/10439835.aspx