Solved

Access application size increases continuously while only doing an OnTimer

Posted on 2014-01-23
20
340 Views
Last Modified: 2014-02-06
Hi

I have an Access app in which I wanted to add the storage of a heart beat when it is running. So I added a form timer on the main form, and on the timer event (every 5 seconds currently), I store in a small table under the name of the application the current date an time, so that other apps can find out if that app is running. I have used that technique a number of times in the past and never had problems.

But this time, the app grows by 12 KB everytime the time event is processed ! I have checked my table update , I close the table and set the recordset variable to Nothing, so it doesn't look like there could be some garbage created and not collected.

Any idea what it could be ?

Thanks for suggestions.
Bernard
0
Comment
Question by:bthouin
  • 8
  • 7
  • 3
  • +2
20 Comments
 
LVL 84
ID: 39803406
Every 5 seconds sounds like a bit much ... but you may need it.

Have you performed maintenance on the database? Make a backup first, then Compact it. In 2010, you click the File menu and then click the Compact option.

Is there anything else stored in the table? Does the table contain only a single column?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39803444
Are you writing a new record to this table, or simply updating an existing record?

Is this table you are writing to embedded within the application, or is it in an external mdb/accdb file linked to your application.  You might want to consider the latter and determine which file is growing, the application or the backend containing that table.
0
 
LVL 1

Author Comment

by:bthouin
ID: 39803470
Changing the 5 seconds to 15 seconds doesn't change the behaviour.

Yes, I always compact my apps after each and every change. I decompile if I get a problem (rare, but that happens). Same problem after compacting.

My table has 3 columns: app name, timestamp and status. Status is taken from a textbox on the form, timestamp is obviously Now().

BUT: the app runs in productions on a Windows 2008R2 server. I ran the app on the source system, where I develop, which is a Windows 2003 Server, Enterprise Edition (accessed over Citrix), and there, NO SIZE INCREASE !

Access version on 2003 Server: 2007 SP3 (12.0.6652.5000)
Access version on 2008R2 Server: 2007 without any SP's (12.0.4518.1014)

Could the lack of upgrade to latest SP level be the reason ?
0
 
LVL 57
ID: 39803542
I think your going to find it's related to the OS rather then Access, but certainly you should try and get the revs to match.

I've found 2008R2 just works differently then prior OS's in regards to Access.  So far, that's mainly in the area of task scheduler for me.

Jim.
0
 
LVL 1

Author Comment

by:bthouin
ID: 39803653
Hi Jim

>>I think your going to find it's related to the OS rather then Access<<
So you mean there might not be any solution to my size leak but to run on another server version ? If yes, which one would you recommend ?

>>that's mainly in the area of task scheduler for me<<
That's interesting, because I'm using the task scheduler quite a bit on 2008R2, but not on 2003, however I've never had troubles with the 2008R2 scheduler. What differences did you notice ?

Bernard
0
 
LVL 41

Expert Comment

by:pcelba
ID: 39803875
What you mean by "the app grows by 12 KB everytime the time event is processed"?

1) Does the size of the app (database file) grows on the disk?
2) Does the memory consumed by the app grows?

The 2) is more probable answer to me... So this is a standard bug when the app does not release all the memory allocated by some internal process.
But it does not matter if it would even be 1). The app can simply allocate some database space which then becomes unnecessary...

Solution:
Service Packs or even version upgrades are the standard way how to fix these bugs. Nothing else. Of course, you may restart the application as a work around when it grows over the given limit...
0
 
LVL 57
ID: 39803883
<<So you mean there might not be any solution to my size leak but to run on another server version ? If yes, which one would you recommend ?>>

  Possibly.  But I would not say that's a reason to switch; just run a compact on close.

  Or are you saying it continues to grow even if you do that?  If so, then it is something in Access.

<<What differences did you notice ?>>

 Applications that used to run under 2003 without issue hang in the 2008 task scheduler.   Most of the problem I believe is actually within Access.

 One thing I found was changing Application.quit to DoCmd.Quit got rid of a lot of them.

 That lead me to believe that the problems with the task schedule lead back to the age old ones:

1. Access not cleaning up references as it should

2. The fact that there is a bug in the Access terminate process.

 Not sure if you've got it or not, but often Access will throw a C000005 (Memory Access violation) as it goes to shutdown.

 It did that under 2003 server as well, but it never seemed to cause an issue other then it logged it everytime.

and by the way, to be clear, this is with A2000/2003

Jim.
0
 
LVL 1

Author Comment

by:bthouin
ID: 39804483
Hi again

>> just run a compact on close<<
That's not an option. My app shouldrun all day waiting for requests to do 3 different kind of processing. If it grows by 12KB every 5 seconds and it starts at 2MB, you can compute how big it will be after 10 hours running: unacceptably big.

>>are you saying it continues to grow even if you do that?<<
After stopping it and compacting, it goes back to 2MB, when started again, it grows again by 12KB after each OnTimer event.

>> If so, then it is something in Access.<<
What in the code ? I'll post the important pieces of code tomorrow (I'm home now), let's see if you find something wrong there.

I never had hanging apps in the scheduler. My apps kill themselves anyway by comparing the current time with a stop time stored in a parameter table in the DB, but to kill themselves I always use DoCmd.Quit, I never notice that there was an Application.Quit...

>> often Access will throw a C000005 (Memory Access violation)<<
No, never had that. I use Access 2007 and 2010, though. I sometimes get the "Access has to shutdown because of an error", but that happens even while writing code, when I shuffled too much code around or changed too many things in a form, so not necessarily when running the code. Then I have to decompile. So yes, it does not clean after itself properly, that's for sure.

The only problem I had with the scheduler is that I have to give him as the start command not only the name of the Access app but the whole shebang with the path and name of where the MSACCESS.EXE is located before the app path and name. I don't remember doing that on previous cases ( a couple of years ago) when using it.

Bernard
0
 
LVL 57
ID: 39804592
<<>> just run a compact on close<<
That's not an option. My app shouldrun all day waiting for requests to do 3 different kind of processing. If it grows by 12KB every 5 seconds and it starts at 2MB, you can compute how big it will be after 10 hours running: unacceptably big.
>>

 I guess I'm surpised you hadn't run into this before then.  A change was back in JET, oh let's about A97 I believe to always save a new record on a new page.

 That was done in order to avoid contention for the last page if a lot of users were doing adds.    But the by product is the database can grow quickly if all your doing is adding.

 You should have seen this to some degree with your old setup.  In fact I'm kind of scratching my head trying to understand why you did not.

 A page is only 4096 bytes, but the OS may hand JET a much larger chunk because of the file system (ie. Cluster size).

 You may need to modify your application to compact on the fly every so often as there is no setting to avoid this.  You can't control the way records are added.

Jim.
0
 
LVL 57
ID: 39804630
<<I have an Access app in which I wanted to add the storage of a heart beat when it is running. So I added a form timer on the main form, and on the timer event (every 5 seconds currently), I store in a small table under the name of the application the current date an time, so that other apps can find out if that app is running. I have used that technique a number of times in the past and never had problems.>>

 Rather then use a table, the technique I use is to write two text files:

<appname>_Start.txt
<appname>_End.txt

 and I use the file Date/time.   When the applications starts up, I kill both files and save a new _Start.txt.  When it ends, I write out _End.txt

This let's me know:

1. If an application is currently running (_Start but no _End)
2. How long it's been running (Now() - _Start date/time)
3. If not running (Both _Start and _End exist)
4. How long the last run was (_End Date/time - _Start Date/time)

 The best part about this is that I do not need to open a DB.  It's very low impact.  Of course I don't get a log of when things ran, but you could easily do an external log file as well.

Jim.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 41

Expert Comment

by:pcelba
ID: 39804659
The last paragraph sounds like MS Access is not so suitable for database programming :-)

FYI, I remember this behavior in other products, as well.
0
 
LVL 57
ID: 39804683
<<The last paragraph sounds like MS Access is not so suitable for database programming :-)>>

 No, it's just like every other database system; you always try and minimize connections, and avoid repeatedly opening and closing them.

 That's why we've got things in windows like ODBC Pooling.

 So If I can accomplish my task with a pair of files and get what I need, why would I want to open any database JET or otherwise?

 But JET certainly has a weakness in that it doesn't re-use deleted space if that's what you were refering to.

Jim.
0
 
LVL 41

Expert Comment

by:pcelba
ID: 39804975
No, reused DB space is another story.

FoxPro 1 was writing to the disk temp file after each screen output... So it resulted in serious run-time limitation - disks were not so large that times...
0
 
LVL 1

Author Comment

by:bthouin
ID: 39805708
Hi Jim

>> But the by product is the database can grow quickly if all your doing is adding.<<
Sorry, I should of course have mentioned that this app, like all my apps, is a front-end with only forms and logic, the database is, for the moment, a separate Access DB, soon all my tables (about 100 of them) will be in SQLServer DB. I don't do logic and DB in the same Access file any more since years. Always 2-tier.

For all sorts of reason, I cannot use files to keep track of running, because my app is a kind of "server app" and other apps have to know what it's doing, so they have to look at that table to see if they may run at all, or, if they are already running, if they should exit. That's why the only practicable way is to use the DB as a means of communication, so to say. All my apps also log what they do (the important bits at least) in a log table in the common DB.

And minimizing connections is not a concern: few users, but spread out in different locations, no high data volumes, small DB (last time I compacted it, it was around 300 or 400 MB).

I'm developing with Access off and on since more than 30 years, but quite intensively for the last 8 years or so.

Anyway, when I hit the office in an hour or so,  I'll make a little excerpt from my code, let's see if you guru guys can find the fly in the ointment.

Bernard
0
 
LVL 1

Author Comment

by:bthouin
ID: 39805851
Attached is the code which causes the app to grow.Heartbeat.txt
0
 
LVL 57
ID: 39805975
<<Anyway, when I hit the office in an hour or so,  I'll make a little excerpt from my code, let's see if you guru guys can find the fly in the ointment.>>

  You need to turn this over to Microsoft.  Your tripping over some kind of bug.

  There's nothing there that should cause a database to grow assuming this form remains open for the life of the app.

Jim.
0
 
LVL 1

Author Comment

by:bthouin
ID: 39806174
Hi Jim

Thanks for that. I was starting to doubt myself... I have asked the IT to apply all the service packs to the Access version on the server. When they've done that, I'll try again and keep you posted.

Bernard
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 350 total points
ID: 39806310
As a possible work a round, consider using a variable to hold the polling interval rather then manipulating the form timer itself.

In fact that might be a good test just to see if that's the issue; keep the timer constant.

It has to be either the form timer or the recordset object, but your already cleaning up the object properly, so I'm thinking it has to do with the form timer.

As a test, you could also comment out everything with the recordset and then see if just running with the timer changing still causes it to grow or not.

Jim.
0
 
LVL 1

Author Comment

by:bthouin
ID: 39838313
Hi Jim

Sorry for late reply, I could only test again recently. You were right, changing the setting of the timer from a number to a variable and setting that varaible according to needs stops the app from growing ! So you get the points :-)

Bernard
0
 
LVL 57
ID: 39838655
Glad to here you've got it solved.

Thanks,
Jim.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

MS Access 2003 or later To MySQL Migration Project Hello All, this is my second article in the category of MS-OFFICE Automation. In internet I am not able to find any comprehensive resource on the Migration of MS Access back-end to MySQL so I fin…
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

758 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

20 Experts available now in Live!

Get 1:1 Help Now