Can Not Open Any More Databases

[Dave]

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

Thanks,

Dave

[Allen Browne]

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dave" <no***@email.co m> wrote in message
news:Ng******** *********@newsr ead3.news.atl.e arthlink.net...

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

Thanks,

Dave

[Tim Marshall]

Allen Browne wrote:

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.

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 http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

[Martha]

Tim Marshall wrote:

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?

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.

--
Martha Palotay
don't google to email

[Allen Browne]

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).Dat abases.Count

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tim Marshall" <TI****@PurpleP andaChasers.Moe rtherium> wrote in message
news:cv******** **@coranto.ucs. mun.ca...

Allen Browne wrote:

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.

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...

[deko]

> Has anyone encountered the error message "Can not open any more databases"

and what did you do to solve it?

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.OpenRecords et" 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?

[deko]

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

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?

[Allen Browne]

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"deko" <de**@hotmail.c om> wrote in message
news:Ag******** *********@newss vr13.news.prodi gy.com...

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

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?

[deko]

There is no need to use different names in different procedures. They are
already different objects.
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
Unless you write spagghetti code, every procedure has just one entry point, and just one exit point.

10-4

[Allen Browne]

Yes, you can reuse an object like that.

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"deko" <de**@hotmail.c om> wrote in message
news:kI******** *********@newss vr21.news.prodi gy.com...

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

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

Unless you write spagghetti code, every procedure has just one entry

point,

and just one exit point.

10-4