Results 1 to 12 of 12
  1. #1
    jgelpi16 is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Mar 2010
    Location
    Charlotte, NC
    Posts
    544

    Question DAO.Recordsets - When to close and open

    Currently I have a function that is called and each time I finish the function I am closing the recordsets within the function. Is this necessary? I read somewhere that if you don't close the recordset it could freeze up Access. The reason I am asking is I have one Loop that loops through a table of 50k +/- records and calls this function that is opening the recordsets each time. As you can imagine this is taking a while. I'm trying to trim down the time it takes and was wondering if you could leave recordsets open to save time.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Open the recordset at beginning of the Loop
    Move thru the recordset and do whatever processing is needed
    when you reach EOF close the recordset.

    Concept:
    Code:
    Sub leetest()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    Set db = CurrentDb
    'Open the recordset
    Set rs = db.OpenRecordset("Select PlusTen(col1) as MyCol from LeeTable")
    'set up the process loop
    Do While Not rs.EOF
    MsgBox rs!MyCol
    rs.MoveNext    'advance to next record in recordset
    Loop
    'end the process loop
    'close the recordset
    rs.Close
    End Sub

  3. #3
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    2,392
    and consider as to whether the Loop method is the most efficient.

    if it is an update, calculation or append - doing it with sql is often much faster.

    hope this helps.

  4. #4
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    the other thing those two guys might not have noticed (or maybe they did!) is the fact that your post doesn't make that much sense!

    you're opening a set and looping 50K recs?

    or you're opening a set on EACH record in the 50K rec set and then closing it before moving on to the new rec?

    or you're calling a function that opens a set, but the function is called on each interation in the 50K set? so you have 2 sets always open at the same time?

    per NTC, I would guess, at the minimum, that there is a faster way, be it sql or other.

    as far as leaving rec sets open, NO, usually you can't, and the reason is because one set usually doesn't satisfy more than one condition you want to give it, especially if a WHERE clause exists when it's opened. you absolutely cannot use the SET statement consecutively without using the ".close" method to close the previous set. Doing this would certainly crash Access at some point due to overload of memory stacking, or other consequence. I don't know this for sure, but it does make logical sense.

    (actually I think the memory issue would only be relevant as to the allocation for the recordset object dimmed, regardless of how many times it is SET)

  5. #5
    jgelpi16 is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Mar 2010
    Location
    Charlotte, NC
    Posts
    544
    aje - Just making sure you were paying full attention . I have a fairly complicated loop within a loop.

    In the outer most loop I am checking for not .EOF and that is referring to a recordset. I am passing the employee ID to my function which has another .EOF loop for a second recordset. Would a query/SQL be better than a simple "FindFirst" as far as speed goes? If so why? My outer-most loop/recordset is based on the 50k (table1 for arguments sake) records and the inner loop is based on 8k +/- (table2). What I am having to to is check to see if each record in table1 exists in table2 (with some pre-defined criteria/qualifiers). If the field exists I then use the inner loop to check for changes in the record and if there are I'm just appending the record from table1 to table2. Essentially I am creating an audit trail. I'm having trouble wrapping my head around the advantage to having a query vs. findfirst/findnext.

  6. #6
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    2,392
    sql can be much faster inherently due the concept that it involves working with an entire record set rather than line by line. try it and see. that's the key. of course speed isn't always an issue and looping can be sufficiently fast in small record set scenarios. but when one is into big db record sets then looping isn't viable ever....just speaking generically.....

  7. #7
    jgelpi16 is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Mar 2010
    Location
    Charlotte, NC
    Posts
    544
    Great, thanks for the clarification. I'll try SQL as right now my code is taking about 7 hours to execute. Although not a big issue because it can run in the background I still would like to trim it down if possible.

  8. #8
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    Quote Originally Posted by jgelpi16 View Post
    Great, thanks for the clarification. I'll try SQL as right now my code is taking about 7 hours to execute. Although not a big issue because it can run in the background I still would like to trim it down if possible.
    7 HOURS!? on 50K records only? That seriously needs to change, bud! 50K records is nothing, so the complication of code would have to be overwhelming for that time frame.

    At any rate though, good luck with the sql!

  9. #9
    jgelpi16 is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Mar 2010
    Location
    Charlotte, NC
    Posts
    544
    hah, thanks. In my defense I have over-simplified the code for arguments sake here. I'll look into the SQL method. In using that can you enter the SQL directly in to the db.OpenRecordset("(SQL CODE)", dbOpenDynaset) or do I need to assign to a variable first?

  10. #10
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    Quote Originally Posted by jgelpi16 View Post
    hah, thanks. In my defense I have over-simplified the code for arguments sake here. I'll look into the SQL method. In using that can you enter the SQL directly in to the db.OpenRecordset("(SQL CODE)", dbOpenDynaset) or do I need to assign to a variable first?
    OpenRecordset needs a select statement. that's all. make sense? it's a (de)glorified table, nothing more. and I don't think NTC meant 'sql' in this context. I'm sure he meant using the query builder and updating your table through query usage that way.

  11. #11
    Rawb is offline Expert
    Windows XP Access 2000
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    I ran into a similar issue a while back. I had a function that iterated through the contents of a Table. To make things more readable, I made stuck all the calculations inside another function (that opened and closed about a dozen other Snapshot-type Recordsets) and just called that function each time I iterated through the loop.

    Let me tell you. . . the performance was HORRIBLE. It would take the function upwards of 2-3 minutes to iterate through even a few dozen records!

    What I did to improve performance was to open the recordsets in the main function (before the loop started). Then I did a .FindFirst in each Recordset to find the appropriate Record and passed the (already opened and properly "positioned") Recordset to the function by reference.

    Because I went from opening and closing Recordsets by the hundreds to only opening a dozen or so, the function now runs in under a second.

  12. #12
    Rawb is offline Expert
    Windows XP Access 2000
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    Oh, and to answer your last question.

    It's always a good idea to Dim a variable before you Set it. Unless you're specifying "Option Explicit" in your Module however, it's not - technically - required.

    Also, if you're using a Transaction, Access won't let you run a straight SQL Query using DoCmd.RunSQL. So if you want "big picture" Transactions, you'll have to do everything through VBA.

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

Similar Threads

  1. Open Form with instance method & wait to close?
    By GeorgeBakoyannis in forum Forms
    Replies: 1
    Last Post: 10-09-2010, 11:00 AM
  2. Replies: 1
    Last Post: 04-11-2010, 04:05 AM
  3. VBA to open excel, import on close of excel
    By bdaniel in forum Programming
    Replies: 0
    Last Post: 03-20-2010, 02:45 PM
  4. Counting large ADODB recordsets
    By harpyopsis in forum Programming
    Replies: 3
    Last Post: 10-16-2009, 10:05 AM
  5. Recordsets, add new, DAO and ADO
    By Arca in forum Programming
    Replies: 1
    Last Post: 04-07-2008, 03:57 PM

Tags for this Thread

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