Has anyone encountered the error message "Can not open any more databases"
and what did you do to solve it?

Thanks,

Dave

The crucial aspect is to identify what is causing the problem.

If you are using Access 97 without the service packs, you are limited to
1024 databases. Applying the service packs doubles this to 2048, so that's
the first step.

Are you using any of the domain aggregate functions such as DLookup(),
DMax(), ... in a query? Each call opens a database, and they don't clean up
after themselves quickly, so you can easily hit the limit as each row of
your query opens a database. Workarounds:
- Leave the domain aggregate function out of the query, and put it on the
form if you only need to display for the current record.
- Use a subquery in place of the domain aggregate function. More info:
    http://support.microsoft.com/?id=209066

Each form, subform, report, subreport, combo, and list box also uses up one
for its RecordSource or RowSource. Do you have lots of forms open, each with
lots of subforms? Or do you have heaps of combos? Can you close some forms,
or redesign with fewer combos?

Next, look at your code. Any code that does an OpenRecordset()? You need to
explicitly Close the recordset at the end of the procedure, and preferable
set the recordset variable to Nothing as well. Access is actully good at
cleaning up after itself, but not perfect. You can also see if you have lots
of forms where you refer to the RecordsetClone as well. It uses only 1
database variable for each form where you use the RecordsetClone, but these
are not released until the form is closed.

Hope that's enough to help you identify the cause of the message.

Show quoteHide quote

Allen Browne wrote:

Allen what do you mean by this?  You can only have 1024/2048 mdbs
developed for one installation?  Does it "wear out" or something?  Is
there an internal mdb counter that runs out?  I don't mean to sound
facetious, I just don't know what you mean...

Tim Marshall wrote:
No, you can only have 1024/2048 databases open at one time. The catch
is that Access interprets "open" differently than you or I. Each time
you set a database variable, that counts as another open database; etc.

Even so, I've never even come close to the limit.

No. You can have lots of database on your drive.

When you run an instance of msaccess, any one workspace within that instance
can only have 2048 databases open at once.

You can see how many you have open at present by opening the Immediate
window (Ctrl+G) and entering:
    ? dbEngine(0).Databases.Count

Show quoteHide quote

What about best practices for using Database and other objects?  Let's say I
need to open multiple Recordsets or have multiple db.Execute statements in
the same function - or have a function that calls other functions - that
calls other functions - and each function creates similar objects.  For
example, the first function has Set db = DAO.Database (and Set db.QueryDefs
= db.QueryDefs, etc.),  and calls another function that does the same thing.
I understand that variables are scoped to the function in which they are
created, but what about these DAO objects?  Is it okay to one function 'Set
db = DAO.Database' and then call another function that does another 'Set db
= DAO.Database', and so on?

Should each object be named differently:

Dim dbA As DAO.Database
Dim dbB As DAO.Database
Dim rstA As DAO.Recordset
Dim rstB As DAO.Recordset

When is it necessary to do this?  Can I simply 'Set rst = something else'?
Or do I need to 'Set rst = Nothing' first?

Best practice is to declare the object in the procedure that needs it,
rather than declaring a public object and having everything use it. Too much
scope for interference there.

There is no need to use different names in different procedures. They are
already different objects.

If you are in the middle of one routine, and you have a child procedure that
also needs to operate on the Database or Recordset, you can pass the object
to the child proc. Example:
    Function MyMain()
        Dim db As DAO.Database
        'do something with the database
        Call MyChild(db)
    End Function
    Function MyChild(db As DAO.Database)
        'do something else with the same Database object.
    End Function
That approach can help avoid extra database variables, but it can also do
things like letting you see the RecordsAffected in the main proc after the
child proc runs.

Unless you write spagghetti code, every procedure has just one entry point,
and just one exit point. Even after an error, it exits from the same point,
and in that point you include any clean up code such as setting your objects
to Nothing. That's all you need to do.

Show quoteHide quote

I see. So I assume I can also reSet the object in the same function as
needed:

Set rst = this
Set rst = that
Set rst = otherthing
and only when I'm done:
Set rst = Nohting

10-4

Yes, you can reuse an object like that.

Personally I would set to Nothing before reusing it, but that may just being
pedantic.

Show quoteHide quote

thanks for the tip.

I have one recursive function in mind when asking about this.  I set form,
control and database objects in a form module procedure, then pass
parameters from that procedure to a public function which sets more form,
control and database objects using the same names (frm, ctl, db), then
passes those to private function that sets another db object (to delete some
tables with db.TablesDefs.Delete), and then calls itself (based parameters
received by the calling procedure) thus redimming and resetting the objects
again.  Perhaps I should set the objects to Nothing in the recursive
function just before it calls itself?

A recursive function that sets public variables???

Sounds really dangerous. Multiple instances of a function running and
interferring/interacting with each other? Ouch.

Show quoteHide quote

and that's just the splash screen.... :)

I ran into that error yesterday.  I was looping through the TableDefs
collection and doing a db.Execute to put all the table names in a table.  I
was calling another function with a "db.OpenRecordset" and forgot the
MoveNext.  So even though I had a db = Nothing in the original sub, the loop
still opened enough databases to barf up the error.

What's interesting in Allen Browne's comments is that DLookup, DMax,
RecordSources and RowSources also use a database objects.  So How are we
supposed to clean up these?  cboList = Nothing?

I had this with some complex queries which where in turn in a union query. I could
have eight queries in the union but nine it didn't care for.

Another case was when a report had a lot of subreports and 500 pages or so.

Tony

Views in SQL; whats in Access?
On Screen Keyboard
"Time and Billing" database question -- access
Copy expressions with one change in each of 36 boxes
Can't find Master Link and Child Link Attributes