locked
Out of Memory error - memory consumption very different between machines RRS feed

  • Question

  • TL;DR: access app consumes significantly more memory on the customer machine than on my machine and crashes on 'out of memory'. What are factors that can drive this and how can I solve it?

    Over the last few months I have developed a rather large application in MS Access. The customer is a factory of a corporate multinational, with corporate IT systems on an installation of 365 ProPlus 32 bit  click to run, semi annual channel, Version 1902, 16.0. 

    I have developed on 1802 click to run 64 bit but have since changed to 32 bit version 1802, and now changed my channel to switch to version 1904.

    The customer (a team of 3 people) has been testing the app. Initially this went without problems.

    Recently, after adding some additional functionality, the customer began getting out of memory errors after working in the application for a while. On my dev system I get nothing of the sort. I used the app extensively and I hope similarly to the customer but have not had the error. I have also had colleagues of my own test the app without errors.

    The task manager shows on my machine that access used about 70 MB of RAM after opening the app, it may increase to 90 MB when using the app.
    The task manager on the customer's machine shows 600 MB when opening, and it goes up from there when using. Removing latest functionality from the app brings this figure down, but not to the level shown on my machine.

    Questions:
    How is it possible that with such similar versions we get such radically different memory consumption?
    Can I track what drives that?
    The error mostly occurs when writing from VBA to table (see code below), is there anything I can optimize there?
    I can imagine I could try to optimize memory, but given the figures on my machine it seems hardly necessary. If I were to try, given the size of the app and amount of access features used, what would be the best place to start?

    Further details:

    I have also added logging. As suggested by https://stackoverflow.com/a/20021521/7399061 I use GlobalMemoryStatus and record Mem.dwTotalVirtual - Mem.dwAvailVirtual. I am not 100% sure what I am recording exactly, but I do see again a very different pattern. My system stays steady around 700 MB, while the client starts at 900 and goes up to 1.4GB and then reports out of memory.

    It does not have large tables; some imports of hundreds or max thousands of rows. It operates about 8 forms simultaneously (some of which are hidden unless there is specific interaction).
    The app is based on ChrisO's drag and drop example - some forms contain several hundred labels that have code that lets the user drag and drop them around visually.
    All calculation happens in VBA (thousands of lines, about 30 modules and classes).
    Some forms show tables - for this reason the app writes back from VBA to tables, say 200 rows. I update these using DAO. What I see is that typically the out of memory occurs when writing back to table.

    [code]
        Dim rs As Recordset
        Set rs = CurrentDb.OpenRecordset(Name:="Table1", Type:=RecordsetTypeEnum.dbOpenDynaset)

        If ... Then
            
            daoWorkspace.BeginTrans 'Start the transaction buffer

            If ... Then
                For Each a In collection.Items 'about 200 items
                    With rs
                        .AddNew
                        ![Field A] = "A"
                        ![Field B] ="B"
                        '... about 10 fields
                        .Update
                    End With
                Next a
            End If
            daoWorkspace.CommitTrans 'Commit the transaction to dataset
            rs.Close
        End If
    [/code]
    Wednesday, May 22, 2019 8:38 AM

All replies

  •     Set rs = CurrentDb.OpenRecordset(Name:="Table1", Type:=RecordsetTypeEnum.dbOpenDynaset)

        If ... Then

            daoWorkspace.BeginTrans 'Start the transaction buffer

            If ... Then

            End If
            daoWorkspace.CommitTrans 'Commit the transaction to dataset
            rs.Close
        End If

       

    Hi ExcelApostle,

    A quick look at your code.

    I always Set and Close a recordset on the same level of If statements.

    In your case you Set the recordset outside the top If statement, but closes it inside.

    Does it help if you move the rs.Close line outside the If statement?

    Imb.

    Add: In my experience, an Out of memory is almost always an uncontrolled loop in the code.

    • Edited by Imb-hb Wednesday, May 22, 2019 9:12 AM add
    Wednesday, May 22, 2019 9:10 AM
  • Good advice, I will move it (and specify it as a DAO.Recordset while I'm at it).

    I get what you say about an uncontrolled loop but:
    -It doesn't explain why memory is higher directly after starting the app, before any of this code is run

    -It doesn't explain why everything runs fine on my machine

    Wednesday, May 22, 2019 9:19 AM
  • You might try changing the order of the With and For Each lines to see if that makes a difference because you will define and open the recordset only once and then do your For Each. It should only loop through each record one time then. You will also need a .Edit line to edit the recordset.

    If ... Then

     With rs 

      .Edit

      For Each a In collection.Items 'about 200 items
         .AddNew
         ![Field A] = "A"
         ![Field B] ="B"
         '... about 10 fields
         .Update
      Next a

     End With

    End If

    Wednesday, May 22, 2019 3:43 PM
  • You might try changing the order of the With and For Each lines to see if that makes a difference because you will define and open the recordset only once and then do your For Each. 

    I have interpreted the With clause as syntactic sugar, that is:

    With A
         For each b in c
             .x
         Next b
    End With

    is equivalent to

    For each b in c
        a.x
    Next b
    Is there reason to assume any difference in execution between the two? Putting the Set rs = within the loop would be a different case, but this should be the same.

    Wednesday, May 22, 2019 5:44 PM
  • >>syntax sugar

    I like that. :O) There is no difference in execution. It's just a bit of shorthand.


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Friday, May 24, 2019 3:48 PM