Results 1 to 6 of 6
  1. #1
    Persist is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2010
    Location
    Melbourne Australia
    Posts
    32

    When you open a record set multiple times, should you set it to nothing each time.

    I want to open a recordset ordered in one way and process it, and then open it ordered differently and process it in another way.
    Should I set it to nothing after each opening?
    Should I include the first "Set rst = Nothing" in the following code?


    I think I should do this. Just want to check.

    Code:
       Dim dbs As DAO.Database
       Dim rst As DAO.Recordset
    
       Dim strSQL As String
       Set dbs = CurrentDb
     
       strSQL = "SELECT Task, Priority, TheDate FROM tTasks ORDER BY TheDate"
       Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
       ' Process the recordset
       rst.Close
       Set rst = Nothing ' ****** Do I need this statement 
    
       strSQL = "SELECT Task, Priority, TheDate FROM tTasks ORDER BY Priority"
       Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
       ' Process the recordset
       rst.Close
       Set rst = Nothing

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    In my opinion, you don't need to set it to nothing if you are immediately setting it again.
    It adds unnecessary processing and therefore a (small) time delay.

    However, do make sure you tidy up at the end of the current task by setting rst=Nothing or you may have issues later

    For similar reasons you shouldn't be repeatedly doing Set db=CurrentDb And Set db=Nothing - I notice you haven't done that repeatedly in this section of code
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I agree with Colin.



    You create it once, you destroy it once.

    Open and close the recordset as many times as you want/need, but you only destroy it once at the end of the sub/function.
    Code:
    Dim dbs As DAO.Database
       Dim rst As DAO.Recordset     '<<-- Create it at the beginning
    
       Dim strSQL As String
       Set dbs = CurrentDb
     
       strSQL = "SELECT Task, Priority, TheDate FROM tTasks ORDER BY TheDate"
       Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
       ' Process the recordset
       rst.Close
       Set rst = Nothing ' ****** Do I need this statement   '<<-- NO!!! you still want to use it
    
       strSQL = "SELECT Task, Priority, TheDate FROM tTasks ORDER BY Priority"
       Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
       ' Process the recordset
       rst.Close
       Set rst = Nothing     '<<-- Destroy it  at the end

  4. #4
    Persist is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2010
    Location
    Melbourne Australia
    Posts
    32
    Re-reading Steve's post, I think I am getting it.

    The "Set rst = dbs.OpenRecordset(..)" statement ONLY opens rst.
    I was thinking the OpenRecordset statement both opened rst and created rst.

    It is the "Dim rst As DAO.Recordset" that creates it: '<<-- Create it at the beginning
    So I should include "Set rst= Nothing" only once at the end.

    Now I am surprised because my subroutine does work with the two "set rst = nothing" statements.
    It seems that the work of the Dim statement is not entirely obliterated by the first destroy.
    Otherwise I would need a second Dim statement.
    I do have "option explicit" in my code and this did not find an error.

    Is there a good reference that explains this stuff - for access 2010

    Thanks

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Many years ago in the days of 2k memory I used to have to manage memory in code for larger apps. The principle is pretty much the same today

    in your example, rst is basically a pointer to an area of memory where the recordset resides


    dim allocates memory space to the pointer (7 bytes I believe)
    set assigns and populates an area of available memory with the recordset and populates the rst memory with a pointer to this space
    close releases the memory used for the recordset and returns it to the 'available memory pool'. rst pointer remains unchanged
    nothing clears rst of a value (i.e. to empty. does not clear memory of space requirement which will not happen until the rst is out of scope, e.g. leaves procedure)

    if you don't close, nothing should release the memory used for the recordset, but due to the complexities of memory management, can be missed - resulting in what is called a memory leak. A factor in all OS and programming languages, some better than others.

    using nothing is just best practice

    If you want to keep the pointer memory reserved (can't think why in this instance) use static instead of dim

  6. #6
    Persist is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2010
    Location
    Melbourne Australia
    Posts
    32
    Getting clearer.
    That does explain why my code with the two "set rst = nothing" statements still works.
    Thanks

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Add multiple time entries into record
    By mitch75 in forum Access
    Replies: 5
    Last Post: 01-01-2017, 07:29 AM
  2. Replies: 2
    Last Post: 06-01-2016, 07:53 AM
  3. Replies: 3
    Last Post: 03-11-2014, 07:32 PM
  4. Replies: 2
    Last Post: 11-14-2011, 09:16 AM
  5. Replies: 5
    Last Post: 12-10-2009, 10:33 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums