UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> "out Of Memory" - But Only On The Client Machine!, Access 2016    
 
   
AccessApostle
post May 22 2019, 03:34 AM
Post#1



Posts: 8
Joined: 22-May 19



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


Go to the top of the page
 
gemmathehusky
post May 22 2019, 03:42 AM
Post#2


UtterAccess VIP
Posts: 4,684
Joined: 5-June 07
From: UK


you can get out of memory errors (system resources exceeded) if processors have multi-cores

I would see if that might be the problem.

I can't find the original link I used but it's to do with affinity settings for the number of cores you want access to use.

eg
https://www.experts-exchange.com/questions/...and-Prompt.html

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
cheekybuddha
post May 22 2019, 04:06 AM
Post#3


UtterAccess VIP
Posts: 11,296
Joined: 6-December 03
From: Telegraph Hill


When posting your code it it's always better to post the while procedure.

We can't see whether you are destroying your rs object variable, nor how you declare (and destroy) your daoWorkspace object variable.

Also, later versions of Access are apparently less forgiving of unqualified object references. It would be better to declare as:
CODE
    Dim rs As DAO.Recordset


Let us know.

d

--------------------


Regards,

David Marten
Go to the top of the page
 
AccessApostle
post May 22 2019, 04:12 AM
Post#4



Posts: 8
Joined: 22-May 19



QUOTE
I can't find the original link I used but it's to do with affinity settings for the number of cores you want access to use.



Checked that one out some weeks ago, on my computer too the affinity is set to 4 cores and it doesn't seem to make a difference when I set it to 4. The customer machine also has 4 cores.
This post has been edited by AccessApostle: May 22 2019, 04:13 AM
Go to the top of the page
 
AccessApostle
post May 22 2019, 04:16 AM
Post#5



Posts: 8
Joined: 22-May 19



QUOTE
When posting your code it it's always better to post the while procedure.

We can't see whether you are destroying your rs object variable, nor how you declare (and destroy) your daoWorkspace object variable.


I noticed the missing full reference too when I was posting the code here smile.gif will add and check if it makes a difference (but the memory consumption is already much higher even before any of this code is run).

Should I destroy the rs variable? The subroutine ends after this and the garbage collector should pick it up, correct?

The daoWorkspace is set outside of this procedure, my idea was to set it once and reuse it each time. I don't have any code to destroy it.
This post has been edited by AccessApostle: May 22 2019, 04:17 AM
Go to the top of the page
 
cheekybuddha
post May 22 2019, 04:24 AM
Post#6


UtterAccess VIP
Posts: 11,296
Joined: 6-December 03
From: Telegraph Hill


>> The subroutine ends after this and the garbage collector should pick it up, correct? <<
Correct, in theory! But there used to be an old bug where DAO recordsets were not destroyed properly when they went out of scope (memory leak). It is claimed to have been fixed, but defensive programming to explicitly set your object variables to Nothing when they are no longer required is always a good strategy! It can;t hurt to see if it affects your situation here.

>> The daoWorkspace is set outside of this procedure <<
How is it set? Are you using a singleton? If not, is there a chance that the call to set it can be repeated without the previous instance being destroyed?

--------------------


Regards,

David Marten
Go to the top of the page
 
gemmathehusky
post May 22 2019, 04:46 AM
Post#7


UtterAccess VIP
Posts: 4,684
Joined: 5-June 07
From: UK


There's an old bug/long standing view that failing to close recordsets can sometimes leave a running MS Access instance that can only be closed in task manager, although it's notoriously difficult to replicate. I have add it occasionally - I don't think I am leaving any objects open, but something is definitely stopping Access closing cleanly on occasion.

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
AccessApostle
post May 22 2019, 05:40 AM
Post#8



Posts: 8
Joined: 22-May 19



QUOTE
Correct, in theory! But there used to be an old bug where DAO recordsets were not destroyed properly when they went out of scope (memory leak). It is claimed to have been fixed, but defensive programming to explicitly set your object variables to Nothing when they are no longer required is always a good strategy! It can;t hurt to see if it affects your situation here.


Will try! Is there anything in the environment that could cause a difference whether or not this bug occurs?

QUOTE
>> The daoWorkspace is set outside of this procedure <<
How is it set? Are you using a singleton?


CODE
Private daoWorkspace          As DAO.Workspace

Public Sub initLotsOfStuff()

    '...
    Set daoWorkspace = DBEngine.Workspaces(0) 'The current database
    daoWorkspace.Close
    '...

End Sub


It seems I searched advice on this previously and ended up closing the daoworkspace directly after setting it. Is this good practice?

QUOTE
If not, is there a chance that the call to set it can be repeated without the previous instance being destroyed?


In theory yes, if the program encounters an error and access is not restarted. The memory issue occurs well before that (and as said, before hitting this code.
Go to the top of the page
 
cheekybuddha
post May 22 2019, 05:54 AM
Post#9


UtterAccess VIP
Posts: 11,296
Joined: 6-December 03
From: Telegraph Hill


You could try:
CODE
' ...
  If daoWorkspace Is Nothing Then Set daoWorkspace = DBEngine.Workspaces(0) 'The current database
' ...


I'm not sure why you would want to close the workspace??? Especially since you did not open it in the first place.


I'm afraid it's unlikely there is a simple solution to this - many factors are at play.

It will be a process of elimination.

First things to try:
1. Qualify all things like DAO.Recordset.
2. Explicitly destroy your object variables when done
3. Look in to things like adding dbRefreshCache if iterating over hundreds of records

User pacala_ba has posted about issues with System Resources being exceeded, and they required an Access hotfix - but be warned, making sense of his threads is a challenge in itself!!!

Have you considered trying SQL statements instead of recordset code?

--------------------


Regards,

David Marten
Go to the top of the page
 
AccessApostle
post May 22 2019, 06:45 AM
Post#10



Posts: 8
Joined: 22-May 19



QUOTE
Have you considered trying SQL statements instead of recordset code?


Yes, it's significantly slower.

Again, good suggestions to improve my code, that perhaps reduce memory or prevent issues, much appreciated! Nevertheless, none of it explains the huge difference in memory consumption between machines which is my main interest.

Go to the top of the page
 
cheekybuddha
post May 22 2019, 07:32 AM
Post#11


UtterAccess VIP
Posts: 11,296
Joined: 6-December 03
From: Telegraph Hill


I don't know whether any of these situations have any similarity to yours:

https://www.UtterAccess.com/forum/index.php...ystem+resource/

https://www.UtterAccess.com/forum/index.php...ystem+resource/

Are you and your client both using the same OS + version?

Also, are you both connecting to the same backend, and is it an Access database?

--------------------


Regards,

David Marten
Go to the top of the page
 
AccessApostle
post May 22 2019, 09:03 AM
Post#12



Posts: 8
Joined: 22-May 19



Client and I are both on 365 ProPlus. I have been trying both 32 vs 64 bit and different channels (monthly vs semi annual). Will try the exact same access by changing the channel once more.

OS: I don't know. Both windows 10 but no idea if there is any 'special cause' in how they deploy windows by corporate.

The problems described are different and hotfixes are a thing of the past with 365.
Go to the top of the page
 
Phil_cattivocara...
post May 22 2019, 11:57 PM
Post#13



Posts: 279
Joined: 2-April 18



QUOTE (gemmathehusky)
I can't find the original link I used but it's to do with affinity settings for the number of cores you want access to use.
Could it be this? https://www.devhut.net/bugs/system-resource-exceeded-error/

--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 
AccessApostle
post May 23 2019, 02:04 AM
Post#14



Posts: 8
Joined: 22-May 19



As discussed, it did not seem to matter.
Go to the top of the page
 
AlbertKallal
post May 23 2019, 06:57 PM
Post#15


UtterAccess VIP
Posts: 2,813
Joined: 12-April 07
From: Edmonton, Alberta Canada


QUOTE
Will try! Is there anything in the environment that could cause a difference whether or not this bug occurs?


Unfortunately, the CTR (click to run versions) of Access do seem to have "more" difficulty releasing resources, and they also seem to gobble up more memory at alarming rates.

you do seem to suggest that you are running a version of 365 on your dev machine, but if you are running a non CTR version, then that would explain the difference.

I don't tend to see this as much with recordsets, but if you automating say Access, or some PDF viewer, then yes, I most certainly see a significant increased use of memory, and a significant increase of Access not releasing resources until such time it is re-started - the result is a increase in out of resources errors.

I don't yet have a "tool bag" of experience of yet to suggest how to prevent such issues. But, closer attention to closing reocrdsets etc. does seem to help.

so, one significant difference is using CTR vs non CTR. However, from what you shared, it does suggest you are using CTR on your dev machine also. The additional resource "hogging" comes from the fact that CTR versions of office are now app-V (virtualized apps - they run in their own virtual sandbox process - and even standard API task calls to kill that running instance don't even work anymore). And when using CreateObject() say for Excel (or outlook), then a whole new virtualized copy is created in memory as opposed to using the existing copy - MUCH more memory and resources are required for this process).

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada
Go to the top of the page
 
cheekybuddha
post May 23 2019, 07:19 PM
Post#16


UtterAccess VIP
Posts: 11,296
Joined: 6-December 03
From: Telegraph Hill


Very interesting, Albert. Especially about the API calls to kill process having no effect.

Keep us posted as your tool bag fills up.

d

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th June 2019 - 08:11 AM