Cannot open any more databases

[bellamy_luke]

Hi - I get the old "Cannot open any more database" error with Access
2002. I know the culprit is a big union I'm doing because of a bad
design decided that was made years ago.

I was reading something along the lines of, this error occurs because
the query space is not big enough. Maybe someone can confirm?

Is there any chance upgrading JET/DAO/MDAC and/or moving to Access
2007 would fix this?

Yes I can fix the underlying problem, but the above move is also on
the road map so curious to know more about this issue.

 

[Tom van Stiphout]

On Thu, 2 Oct 2008 19:03:40 -0700 (PDT), (e-mail address removed)
wrote:

There is a free download of the A2007 runtime, so you can check for
yourself.
Nothing like fixing the bad design though.

-Tom.
Microsoft Access MVP

 

[Paul Shapiro]

I wouldn't expect much from Access 2007. I have a client whose application
was running fine in Access 2003, but started getting this error when they
upgraded to Access 2007. The application has a very complex form, with many
subforms, which might not be ideal UI design but it's what they wanted. When
that form is open, they can't open other forms due to this error.

Other suggestions I saw when searching this error:
Allen Browne said the problem might be related to the number of domain
aggegate calls you are making, e.g. if the report's query or controls call
DLookup(), DMax(), etc. Use a replacement for DLookup().

It's also important to dereference your object variables in your code. For
example, if you have code that uses CurrentDb and OpenRecordset, ensure you
close the recordset and set your object to nothing. This kind of thing:
rs.Close
Set rs = Nothing
Set db = Nothing[/QUOTE]

According to the discussion in http://dbforums.com/arch/42/2003/6/804659
using a recordset variable for a recordsetclone opens implicit
database references. So, this:
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst [whatever]
If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark
Set rs = Nothing

will (in A2K) cause an implicit database reference to be created that is not
released at the end.

The solution is to not use a recordset variable, but to use a With block:
With Me.RecordsetClone
.FindFirst [whatever]
If Not .NoMatch Then Me.Bookmark = .Bookmark
End
That does not have the same problem.

Don't waste your time upgrading to Access 2007 in hopes of fixing this.
There are only 2048 handles available to open databases, tables, queries,
forms, modules, combo boxes and list boxes and you've used them all up.

As Tom says fix the design.

Chris
Microsoft MVP

There is a free download of the A2007 runtime, so you can check for
yourself.
Nothing like fixing the bad design though.

-Tom.
Microsoft Access MVP

Hi - I get the old "Cannot open any more database" error with Access
2002. I know the culprit is a big union I'm doing because of a bad

[quoted text clipped - 8 lines]

Yes I can fix the underlying problem, but the above move is also on
the road map so curious to know more about this issue.

 

[Paul Shapiro]

Sorry, the URL is from an old note. Here's what I copied from that
discussion.

Using a recordset variable for a recordsetclone opens implicit database
references. So, this:
Dim rs As DAO.Recordset

Set rs = Me.RecordsetClone
rs.FindFirst [whatever]
If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark
Set rs = Nothing

will (in A2K) cause an implicit database reference to be created
that is not released at the end.

The solution is to not use a recordset variable, but to use a With block:
With Me.RecordsetClone
.FindFirst [whatever]
If Not .NoMatch Then Me.Bookmark = .Bookmark
End

That does not have the same problem.

 

[Tony Toews [MVP]]

There is a free download of the A2007 runtime, so you can check for
yourself.
Nothing like fixing the bad design though.

But you'll also need the full version of A2007 to run it.

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
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

Hi - I get the old "Cannot open any more database" error with Access
2002. I know the culprit is a big union I'm doing because of a bad
design decided that was made years ago.

I was reading something along the lines of, this error occurs because
the query space is not big enough. Maybe someone can confirm?

Is there any chance upgrading JET/DAO/MDAC and/or moving to Access
2007 would fix this?

I wouldn't count on it.

Yes I can fix the underlying problem, but the above move is also on
the road map so curious to know more about this issue.

One fix would be to use a temporary file to hold intermediate results.
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
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[carasen]

Sorry, the URL is from an old note. Here's what I copied from that
discussion.

Using a recordset variable for a recordsetclone opens implicit database
references. So, this:
Dim rs As DAO.Recordset

Set rs = Me.RecordsetClone
rs.FindFirst [whatever]
If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark
Set rs = Nothing

will (in A2K) cause an implicit database reference to be created
that is not released at the end.

The solution is to not use a recordset variable, but to use a With block:
With Me.RecordsetClone
.FindFirst [whatever]
If Not .NoMatch Then Me.Bookmark = .Bookmark
End

That does not have the same problem.

 

[carasen]

Sorry, the URL is from an old note. Here's what I copied from that
discussion.

Using a recordset variable for a recordsetclone opens implicit database
references. So, this:
Dim rs As DAO.Recordset

Set rs = Me.RecordsetClone
rs.FindFirst [whatever]
If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark
Set rs = Nothing

will (in A2K) cause an implicit database reference to be created
that is not released at the end.

The solution is to not use a recordset variable, but to use a With block:
With Me.RecordsetClone
.FindFirst [whatever]
If Not .NoMatch Then Me.Bookmark = .Bookmark
End

That does not have the same problem.

 

[carasen]

Sorry, the URL is from an old note. Here's what I copied from that
discussion.

Using a recordset variable for a recordsetclone opens implicit database
references. So, this:
Dim rs As DAO.Recordset

Set rs = Me.RecordsetClone
rs.FindFirst [whatever]
If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark
Set rs = Nothing

will (in A2K) cause an implicit database reference to be created
that is not released at the end.

The solution is to not use a recordset variable, but to use a With block:
With Me.RecordsetClone
.FindFirst [whatever]
If Not .NoMatch Then Me.Bookmark = .Bookmark
End

That does not have the same problem.

 

[carasen]

Sorry, the URL is from an old note. Here's what I copied from that
discussion.

Using a recordset variable for a recordsetclone opens implicit database
references. So, this:
Dim rs As DAO.Recordset

Set rs = Me.RecordsetClone
rs.FindFirst [whatever]
If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark
Set rs = Nothing

will (in A2K) cause an implicit database reference to be created
that is not released at the end.

The solution is to not use a recordset variable, but to use a With block:
With Me.RecordsetClone
.FindFirst [whatever]
If Not .NoMatch Then Me.Bookmark = .Bookmark
End

That does not have the same problem.