Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Jan 2005
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Has something changed in Access 2007 that would prevent some VBA code from completing execution?

    Description:
    I have around 500,000 records on which I have vba perform a ton of calculations and store the results in a table.
    Using Access 2003, this update took around 2 hours to complete. During execution, Access showed "not responding" in the title bar.
    No problems- everything worked.

    Using the same database, same data, and same vba code in Access 2007, the code never terminates.

    Is there something new in 2007 that could cause this? I'm lost.

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    [quote name='bst73' post='779550' date='12-Jun-2009 13:12']Has something changed in Access 2007 that would prevent some VBA code from completing execution?

    Description:
    I have around 500,000 records on which I have vba perform a ton of calculations and store the results in a table.
    Using Access 2003, this update took around 2 hours to complete. During execution, Access showed "not responding" in the title bar.
    No problems- everything worked.

    Using the same database, same data, and same vba code in Access 2007, the code never terminates.

    Is there something new in 2007 that could cause this? I'm lost.[/quote]
    Do you have any idea how far it got in Access2007? How many records (if any) were processed perhaps?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Lounger
    Join Date
    Jan 2005
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='MarkLiquorman' post='779570' date='12-Jun-2009 21:22']Do you have any idea how far it got in Access2007? How many records (if any) were processed perhaps?[/quote]

    It looked like it made it to around 500,000 (there were actually around 527,000 records, with around 20,000 not updated), but given the structure of the table, it is hard to tell exactly (not all records are to be updated, if they meet certain criteria).

    I did some troubleshooting and I manually ran some groups of records around the point where it looked like it stopped- by running the code on the smaller batches, they updated.

    I am presently in the process of updating the entire dataset in small groups (what a nice step backward in technology...).

    I also added a little 'watch window' (i.e., a form with a text box that the code updates with status info) thinking that 'forcing' a link between the code and the database would help resolve the 'not responding' issue- it didn't.

    Some other 'literature reviews' online have led me to suspect service pack 2 (SP2) might help this problem (I am running SP1).

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I've seen some indications when processing really large recordsets in either 2003 or 2007 that there may be some sort of memory leak in the Jet engine. We have a process that updates about 350K records based on reviewing and processing some 3.5M detail records, and we have to run it in chunks or it will either hang or crash. I'm going to be testing SP2 with that process this weekend and I'll report our success or failure with it.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •