Set rs = Nothing – or – rs.Close (A2000)

Topic

New Reply

I’m a little confused over the correct or best way to close off variables rs and db. I’ve seen examples that use rs.Close and some that use Set rs = Nothing and I’ve ended up using both in my code so that my usual code looks like:

Set rs = Nothing
rs.Close
Set db = Nothing
db.Close

What’s right?

Reply | Quote

Replies

The two instructions serve a different purpose and should be executed in a specific order.

If you open a recordset, you create a connection to the underlying table.
When you’re done with it, you should first close this connection. Then, release the memory occupied by the recordset object.

If your database object is the current database, there is no need to close it, but it is useful to release the object memory if you don’t need the variable any longer. If you used OpenDatabase to create a connection to another database, you should close this connection before releasing the memory.

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(“tblSomething”)

‘ code to handle records in the recordset go here

‘ Close connection to table
rst.Close
‘ Release object memory
Set rst = Nothing
Set dbs = Nothing

If you had used

Set dbs = OpenDatabase(“C:AccessMyDatabase.mdb”)

instead of = CurrentDb, you would need to insert

dbs.Close

before Set dbs = Nothing.

Reply | Quote

Many thanks Hans

Reply | Quote