Too Many Databases Error

[max_the_axe]

I've been getting an error with the message 'Too Many Databases' occasionaly and was advised to ensure that all unwanted recordsets are closed by using the following vba code:-

If not recordsetname is nothing then
     recordsetname.close
     Set recordsetname = Nothing
Endif

Seemed to do the trick but I now get occasional errors onthe recordsetname.close line with an error telling me that the recordset doesn't exist. I can't understand how I can test for the existence of the recordset in one line then on the next line it doesn't exist. Very frustrating, would appreciate any suggestions.

Thanks.

[ranman256]

Skip the 'recordsetname.close'. Its not needed. I never use it, never a problem.

[ItsMe]

If the OpenRecordset method returns null the .Close method is not available.

I usually test to see if the Openrecordset actually retrieves data first. Then, if it does not, I will set the object = nothing without the .Close and exit the sub.

[max_the_axe]

Thanks for the replies. Looks like the .Close is superfluous. I shall try taking this out and see if it improves things.

Grateful for the help.

[ssanfu]

Looks like the .Close is superfluous.

No, not really.

From ADezii http://bytes.com/topic/access/answer...etting-nothing

"Rule of thumb... if you open it, close it.
If you assign it... release it."

If you fail to follow: In the best of cases nothing happens, in the worst you get memory leaks, unavailable resources, and corrupted data files.
I've found Access to be very forgiving, often closing and releasing resources implicitly; however, it’s not best practice and really puts a lot of faith in other programmers to cleanup.

From Allen Browne #9 http://allenbrowne.com/ser-29.html

Failing to close a recordset

It is poor programming to open anything without explicitly closing it. This problem is particularly acute in Access 97. Short of pressing Ctrl+Alt+Del, you may find that Access will not quit if recordsets or other objects are not closed and dereferenced.

Another example from Allen

If you open anything, close it.
If you did not open it, do not close it.
Regardless of whether you open it or not, set all object variables to
Nothing.

Example 1: Opening a recordset:
Dim rs As DAO.Recordset
Set rs = dbEngine(0)(0).OpenRecordset("MyTable")
'do something
rs.Close 'Correct: you opened it.
Set rs = Nothing 'Set any object to Nothing

Example 2: Refereing to an already open recordset.
Dim rs As DAO.Recordset
Set rs = Forms("MyForm").RecordsetClone
'do something
rs.Close '<=WRONG! It was already open.
Set rs = Nothing 'Set any object to Nothing

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

From David-W-Fenton (answer #2) http://stackoverflow.com/questions/2...in-a-recordset

Setting the variable to Nothing clears the pointer to that memory structure.

Theoretically, clearing the pointer should release the memory the pointer was referring to, because VBA uses reference counting for determining when it can release memory. Unfortunately, various things can go wrong and the reference count can end up out of whack, and memory won't be released even when it should be.

Thus, to be sure you're not subject to memory leaks, or the weird kinds of bugs caused by implicit and unreleased references, you both Close and set to Nothing.

So my procedures look like:

Sub Something
On Error goto ErrorHandler

Dim d as DAO.Database
Dim a as Dao.Recordset
Dim b as Dao.Recordset
Dim c as Dao.Recordset

Set d = Currentdb
.
.
.Some code
.
.
:ExitHere
On Error Resume Next ' in case recordset is closed
a.close
b.Close
c.Close

Set a = Nothing
Set b = Nothing
Set c = Nothing
Set d = Nothing
Exit Sub

:ErrorHandler
msgbox "Error"
Resume ExitHere

End Sub

But you should never use

d.Close (It was never opened)