By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
460,044 Members | 1,172 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 460,044 IT Pros & Developers. It's quick & easy.

Can Not Open Any More Databases

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

Thanks,

Dave
Nov 13 '05 #1
Share this Question
Share on Google+
15 Replies


P: n/a
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.com> wrote in message
news:Ng*****************@newsread3.news.atl.earthl ink.net...
Has anyone encountered the error message "Can not open any more databases"
and what did you do to solve it?

Thanks,

Dave

Nov 13 '05 #2

P: n/a
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
Nov 13 '05 #3

P: n/a

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

Nov 13 '05 #4

P: n/a
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

--
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****@PurplePandaChasers.Moertherium> 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...

Nov 13 '05 #5

P: n/a
> 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.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?
Nov 13 '05 #6

P: n/a
> 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?
Nov 13 '05 #7

P: n/a
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.com> wrote in message
news:Ag*****************@newssvr13.news.prodigy.co m...
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?

Nov 13 '05 #8

P: n/a
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
Nov 13 '05 #9

P: n/a
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.com> wrote in message
news:kI*****************@newssvr21.news.prodigy.co m...
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

Nov 13 '05 #10

P: n/a
> Yes, you can reuse an object like that.

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


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?
Nov 13 '05 #11

P: n/a
Allen Browne wrote:
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


Ahh, got ya, thanks (and thanks to Martha as well).

Goodness, I've never come close to that, I'm pretty sure...

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Nov 13 '05 #12

P: n/a
A recursive function that sets public variables???

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

--
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.com> wrote in message
news:Bm***************@newssvr14.news.prodigy.com. ..
Yes, you can reuse an object like that.

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

being
pedantic.


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?

Nov 13 '05 #13

P: n/a
> A recursive function that sets public variables???

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


and that's just the splash screen.... :)
Nov 13 '05 #14

P: n/a
Tim Marshall <TI****@PurplePandaChasers.Moertherium> wrote in
news:cv**********@coranto.ucs.mun.ca:
Allen Browne wrote:
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


Ahh, got ya, thanks (and thanks to Martha as well).

Goodness, I've never come close to that, I'm pretty sure...


The error message is wrong. It actually refers to "table handles,"
not "databases." So, dbEngine(0).Databases.Count won't actually tell
you anything useful for solving the problem.

The way table handles are used is rather more profligate than one
might expect. If you've got a form that has as a recordsource a
query with 2 tables and another query in it, that's 4 table handles
plus the number of table handles use by the query.

Each source table/query uses a table handle, and each aggregate uses
a table handle. So, if you have 2 tables and a query with 2 tables
in a query, that's 5 table handles plus one for the query that
incluees those, for a total of 6 table handles.

Replication also ups the number of table handles used, too, since
the replication tables have to be constantly updated while data is
being edited.

If you have lots of forms open with lots of combo boxes and list
boxes and subforms, then you can use up your table handles very
quickly.

The solution is to reduce the number of recordsource/rowsources
active. The usual solution for this:

1. don't open and hide forms (which is often done for performance
reasons when you have heavy-weight forms that are very slow to
open), except where it's absolutely necessary.

2. don't assign recordsources to subforms until they are actually
display. This is most relevant to forms with multipage tabs where
each tab displays a different subform.

3. don't assign rowsources to combo/listboxes until they are
visible. You may even want also choose to not assign a rowsource to
a combo box until after 1 or 2 characters have been typed (filtering
on the character typed, which also has the benefit of reducing the
number of rows returned).

4. don't maintain recordsets open in code any longer than necessary.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #15

P: n/a
"Dave" <no***@email.com> wrote:
Has anyone encountered the error message "Can not open any more databases"
and what did you do to solve it?


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
--
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
Nov 13 '05 #16

This discussion thread is closed

Replies have been disabled for this discussion.