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.
Chris O'C via AccessMonster.com said:
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.