[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Memory (RAM) use grows with Access 2007

Posted on 2011-03-15
13
Medium Priority
?
748 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 58
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 58
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 750 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 - Microsoft MVP, Access and Data Platform) earned 750 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
 
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 58
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 - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 750 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

867 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