Set db = Nothing... explanation?

[RichO]

I'm sure this topic may have been covered somewhere in the forum but I couldn't find it. Is there any way to do a search for exact key words? I tried to put it in quotes but it didn't work.

Anyway, what is the reason behind "Set db = Nothing" and/or "Set rst = Nothing" and what can happen if you don't use this regularly after working with recordsets and db commands in VBA?

Thanks for the info.

 

[Mile-O]

When you dimension an object variable (e.g. Database, Recordset) then a portion of memory is set aside for it. When the sub or function ends then the memory is not reclaimed by the system unless you specifically set them to Nothing.

 

[Pat Hartman]

Also there is a bug in some versions of Access that prevents a db from closing properly if there are any open recordsets.

 

[RichO]

That's probably why our work DB (networked) crashes if someone closes out using the X in the upper right hand corner as opposed to the command button that was set up for exit.

Thanks.

 

[Pat Hartman]

You can resolve the problem by using a hidden form. It will be the last object to close and you can use it to close any open recordsets by putting code in the form's close event.

 

[RichO]

How exactly do I go about this?

Do I open the invisible form on startup and have it open the usual startup form?

In the On Close event, what do I put? Set db = Nothing? And how do I close all recordsets?

Thanks

 

[Pat Hartman]

You would set the hidden form to open at startup. In its Open event, you can open your normal startup form. In order to close all open recordsets, you would have to define the recordsets as public. You can then close them and set them to nothing. I haven't done this (I close my recordsets immediately after using them) so I don't know what problems you might run into. Try it with one recordset first. See what happens if the recordset is already closed when you try to close it again. You will need error trapping I'm sure.

 

[Oldsoftboss]

This is a subject I have wondered about also. I have seen the Set rst = Nothing and presumed it did as above. I have also seen rst.Close and then both together. Could anyone comment further as to the difference to rst.Close and Set rst = Nothing.
Should you use either in preference to the other or both or use them at different times in different situations.

Dave

 

[Mile-O]

Could anyone comment further as to the difference to rst.Close and Set rst = Nothing.

• rst.Close simply cuts the connection to the recordset;
• Set rs = Nothing clears the recordset object from memory

or,

• turning the TV off in the living room;
• throwing the TV out the window, restoring space in the living room