473,322 Members | 1,401 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Closing a Recordset vs. Setting to Nothing

twinnyfo
3,653 Expert Mod 2GB
Hey Friends,

This is more of a conceptual question rather than a problem. During my experience in Access VBA coding, I have seen various methods for closing one's session with a Recordset. I have seen some coders "close" the Recordset:

Expand|Select|Wrap|Line Numbers
  1. rstMyRecords.Close
and I've seen others set the Recordset equal to "Nothing" like this:

Expand|Select|Wrap|Line Numbers
  1. Set rstMyRecords = Nothing
Is there any type of substantive difference between these two methods and/or are there any advantages of one over the other? I know the bottom line is that good coding always does "something" to tell the DB that it is done with that Recordset, but I've just never heard discussion about these two ways of doing that.

Any insight would be appreciated, as I am always willing to learn more about how all this stuff works.

Thanks and warmest regards,
twinnyfo
May 16 '13 #1

✓ answered by ADezii

To the best of my knowledge, Closing a Recordset does not release the Resources assigned to its Object Variable, thus you must set this Variable to Nothing. Many times, especially in Error Handling Code, you may not know if a Recordset actually exists, so:
Expand|Select|Wrap|Line Numbers
  1. If Not rsPCs Is Nothing Then
  2.   rsPCs.Close
  3.     Set rsPCs = Nothing
  4. End If

4 12669
Seth Schrock
2,965 Expert 2GB
You might check out this thread: Closing a recordset in VBA. The last part of the thread goes off on the CurrentDb trail, but I think that the first couple of posts might help somewhat.
May 16 '13 #2
ADezii
8,834 Expert 8TB
To the best of my knowledge, Closing a Recordset does not release the Resources assigned to its Object Variable, thus you must set this Variable to Nothing. Many times, especially in Error Handling Code, you may not know if a Recordset actually exists, so:
Expand|Select|Wrap|Line Numbers
  1. If Not rsPCs Is Nothing Then
  2.   rsPCs.Close
  3.     Set rsPCs = Nothing
  4. End If
May 16 '13 #3
zmbd
5,501 Expert Mod 4TB
ADezii is correct.
My CompSci Prof taught:
"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.
May 16 '13 #4
twinnyfo
3,653 Expert Mod 2GB
Hey, guys!

Thanks for the info. This is exactly what I was looking for!

ADezii, this helped me understand the concept easily.

Seth, your links gave me additional insight into these concepts, and now I need to clean up my DB....

Z, good principles to live by!

Thanks again to all!
-Twinnyfo
May 17 '13 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

6
by: Felix McAllister | last post by:
Hi, When using win32com.client, how do you test for a 'nothing' com object as you can in VB? I have an example here when using ADO to loop over multiple recordsets returned from a query. I get the...
5
by: Roger Withnell | last post by:
I'm updating a record by opening a recordset, setting the fields and the updating it with objRS.Update. I need to set an image datatype to NULL. objRS("field") = NULL works for datatypes int...
0
by: sneal | last post by:
A little background: We have an Access 2002 based user interface to our SQL Server 2000 database. The interface is a tabbed form with two of the tabs containing a subform. Data is pulled from the...
2
by: CJM | last post by:
Is it sufficient to Set a Connection or Recordset to Nothing, or ought they specifically be closed first? E.g. Set rs = oConn.Execute(sSQL) 'Do stuff Set rs = Nothing
8
by: LabGeek | last post by:
Do I have to recreate a recordset everytime I want to use it, or can I simply create a recordset on the form open event and access it for the life of the form? I guess my question is really what...
4
by: Paul H | last post by:
A typical chunk of code...... Set db = CurrentDb Set rs = db.OpenRecordset("tblFoo") <Do some stuff here> 'How much of the stuff below do I need? 'Do I need to close the recordset?...
6
by: robert.waters | last post by:
Hello, Do you need to close an adodb recordset returned from a connection.execute(SQL) action? ex) set rcd = con.execute("SELECT * FROM TABLENAME") I know that close()ing objects that you...
6
by: MLH | last post by:
MyRst.Close Set MyRst = Nothing MyDB.Close Set MyDB = Nothing Would the above be the correct order in which to conduct the operations?
19
by: zacks | last post by:
I have a .NET 2.0 MDI application where the child form has a Tab Control. Each of the Tab in the Tab Control has a Validating event to handle what it should do when the user changes tabs. But...
1
by: Spoogledrummer | last post by:
Hi, I just completed an application that queried the database to see if a value existed in there, if it didn't then it did one thing, if it did it did another. at first I was usingIf not rsblah.EOF...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.