Memory (RAM) use grows with Access 2007
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!
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!
And just to be clear, it's the memory listed for MSACCESS.EXE that is going up?
JimD.
ASKER
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
Yes, I have been very careful to close all objects. Typical code is:
rst.Close
Set dbs = Nothing
Have not played with MaxBuffers
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
"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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
<<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.
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.
ASKER
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.
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.
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
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
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Memory leak in still present but reduced. Further work is ongoing.
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.