Solved

Memory (RAM) use grows with Access 2007

Posted on 2011-03-15
13
732 Views
Last Modified: 2012-05-11
When my Access 2007 accde or accdb starts, task manager shows allocated memory at 28m (28,000k). Over time (2 hours), useage can go up over 600m.  Many might call this a memory leak.

I have searched for memory leak issues to no success.

I have validated that all calls to data sources are closed after use.
This is not a compact and repair issue.

Maybe I need code that releases resources when a form is closed?

The application is a basic FE-BE configuration. For users, the FE is an accde with tables linked to both an accdb and SQL server. All users have Access 2007 SP2 on XP, Vista, Windows 7 or Server 2003 with all updates and SP installed.

Suggestions on further research welcome. Solutions even better!

0
Comment
Question by:PowerComputers
  • 5
  • 4
  • 4
13 Comments
 
LVL 57
ID: 35141805
 Assuming you haven't played with the MaxBuffers setting, then yes, sounds like you have a memory leak.

  Leak or not, I would go through any code and close anything that you open (file, recordset, database, etc) and set object vairables to nothing.

  As for the leak, I haven't heard of any yet with A2007, but so few are using it.  Even now, almost 4 years after release, we are just starting to see more then a few questions on it.

JimD.
0
 
LVL 57
ID: 35141815

 And just to be clear, it's the memory listed for MSACCESS.EXE that is going up?

JimD.
0
 
LVL 3

Author Comment

by:PowerComputers
ID: 35142306
Yes, MSACCESS.EXE is the Process in Task Manager that is going up.

Yes, I have been very careful to close all objects. Typical code is:
rst.Close
Set dbs = Nothing

Have not played with MaxBuffers
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
ID: 35142524
FYI this should be:

rst.Close
Set rst = Nothing

Set dbs = Nothing

  If not, your not releasing the object pointed to by the variable.

JimD.
0
 
LVL 75
ID: 35142582
"Yes, MSACCESS.EXE is the Process in Task Manager that is going up."
But what exactly does this db do ?
More importantly, is it a common FE or does each user have a copy on their workstation?  And if they do, is each users memory going up ?

mx
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 250 total points
ID: 35142621
And wherein Closing and setting objects/variables to Nothing is certainly best practice ... and I continue to do so in all cases, I'm pretty sure  that's not the reason for the memory increase.  Whereas this has been an issue in the distant past in Access, I really have seen little evidence if any in recent version.  BUT ... as noted, A2007/10 is in use to a much less degree than previous versions ... and maybe some new 'bug' has been introduced.

mx
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 3

Author Comment

by:PowerComputers
ID: 35143399
Thanks for all the responses.

I had not seen or used: Set rec = Nothing.  That is a wake up call!

I have implemented it for all instances where a recordset is instantiated and used.
We will monitor memory usage tomorrow.

The db is a 'wrapper' application that links to SQL tables used by an off-the-shelf app. We add additional functionality to accomodate business processes that the off-the-shelf app does not provide. So it has backend tables in both SQL and Access 2007. Each user has their own FE. Each FE suffers from the leak.

It does seem amazing that A2007/10 are so little used. I have to admit there was a learning curve to them. But I have 3 applications written in A2007/10. All are in daily use. I will be checking the other 2 to see if they have a leak.

I hope I am not the first to find a new bug.
0
 
LVL 57
ID: 35143635
<<I have implemented it for all instances where a recordset is instantiated and used.>>

  It's not just recordsets, it's any object variable.  Anything you use a SET xxxx =

  Access/VBA is supposed to clean these up automatically, but the garbage collection is far from perfect.

<<We will monitor memory usage tomorrow.>>

  If you can, try and note how it grows and when.  Often the pattern will tell you something about the problem.

<<It does seem amazing that A2007/10 are so little used. I have to admit there was a learning curve to them. But I have 3 applications written in A2007/10. All are in daily use. I will be checking the other 2 to see if they have a leak.>>

  Most developers I know by-passed A2007 because of the ribbon and a few other things.   Another big issue was the hardware.  Lots of folks still on five/six year old machines and running XP.

  It's just now with a turnover in hardware that A2010 is coming into play and many seem to be warming up to it where they did not with A2007.  Mainly I think just because to a certain extent, A2000/A2003 looks old and out dated.

  Let us know how you make out and let's hope it's not some new bug.

  Oh and a couple of other questions:

1. Does the memory usage ever head down?  Say when you start some more apps?
2. Does the memory go up under all client OS's?
3. How much memory do these clients have?

JimD.






0
 
LVL 3

Author Comment

by:PowerComputers
ID: 35144159
The interesting thing is that memory usage increases even when the app is at 'idle' - sitting freshly opened at the main menu with no user input and without focus. I have seen this on other A2007 apps too. It definitely shows jumps whenever a form is opened - evenforms that bind only a table. It never goes down.

I have not checked to see if the increase happens on all OSs, but it happens on Server 2003 and Vista, and Win7.
All clients have 2 to 4 gb RAM. Probably all have 4.
0
 
LVL 75
ID: 35144239
Idle ... that really makes no sense at all. Something else is going.
When this is happen, on the TM Processes tab ... if you sort by %CPU Usage - Descending ... where is ACCESS.EXE in this mix ... and how much does the value vary in this Idle mode ?

mx
0
 
LVL 3

Author Comment

by:PowerComputers
ID: 35184141
An update.
I did find that I had missed closing some intances of a qrydef.
After fixing that, the memory leak has slowed considerably. Closing forms now actually releases some memory.
Now I suspect that the ODBC to SQLmay be the cause.
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 250 total points
ID: 35871367
Has this question been resolved?  Can we close the question ?

thx.mx
0
 
LVL 3

Author Closing Comment

by:PowerComputers
ID: 35874386
Memory leak in still present but reduced. Further work is ongoing.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now