Can't open more databases

[Guest]

When I am going throu my Access App. I receieve this error message from time
to to time.
I press the End Button and it continues working as if nothing had happend.
What is it?

Thanks a lot, Lina

 

[Guest]

Are you declaring databases in code and not destroying them? This used to
happen to me a lot until I understood the problem. If you have statements like
Dim mydb As Database ' or DAO.Database

then you need to explicitly destroy it with
Set mydb = Nothing

Important note: When you create objects like this, you need to have
errorhandling so it will be destroyed even after an error.

On Error GoTo Err_Handler
Dim mydb As Database
'do stuff with your new friend the database

Err_Resume:
Set mydb=Nothing
Exit Sub ' or Function
Err_Handler:
Msgbox Err.Description
Resume Err_Resume

I'm not convinced that this is your problem, but it's very possible.
Barry

 

[Guest]

Hey Barry, thanks a LOT!!!
Yo have been very helpfull!

 

[Guest]

Barry, one more question.
What if I declare something like this
Private rsSQL As DAO.Recordset
Should I kill it as well?

Thanks a lot, Lina

 

[Guest]

Yes! Anytime you instantiate an object that requires the New keyword, you
should kill it. One or two un-killed objects won't hurt, but an app that's
left up for a while or one in which you do a lot of these will eat memory and
eventually cause errors. For this reason, it's considered a best pratice to
explicitly destroy all objects when you're finished with them. Kind of like
Godzilla!

Barry

 

[Guest]

Thank you very much!

Yes! Anytime you instantiate an object that requires the New keyword, you
should kill it. One or two un-killed objects won't hurt, but an app that's
left up for a while or one in which you do a lot of these will eat memory and
eventually cause errors. For this reason, it's considered a best pratice to
explicitly destroy all objects when you're finished with them. Kind of like
Godzilla!

Barry

 

[Guest]

Hello Barry
I have been doing what you recomended me. It's getting better, the error
message appears less than before. I do not know if I made a mistake, trying
to have a module to kill this opened recordsets.

I made a module like this. And then I called it form the error message you
show me how to make it. I just pass the recordsets I am using. I do not know,
if this is correct or I may be leaving things unclose and that's why I am
still getting the same error message, even thou it is less frequent.

Public Sub TreeViewErrorHandler_Click(rst1 As DAO.Recordset, _
Optional rst2 As DAO.Recordset, _
Optional rst3 As DAO.Recordset)

If Not IsMissing(rst1) Then Set rst1 = Nothing
If Not IsMissing(rst2) Then Set rst2 = Nothing
Set rsSQL = Nothing
Set rsSQL1 = Nothing

Set dbs = Nothing

End Sub