Solved

Access 2007: How to check for memory leak?

Posted on 2008-10-09
9
1,769 Views
Last Modified: 2013-11-29
I created a sales application in MS Access 2007. The sales order form has the basics in the header (order number, date, order discount, sales tax). The order lines are entered in a subform. Today we are having a high volume of sales due to a sale and I noticed something that has not happened before...for the past few orders the sales tax on the order header stayed at zero!

I store the sales tax percentages in a variables table. In the VBA code for the header form AND for the subform both, I have the attached code. Those public variables are then used like this:
1) From the header form I update the sales tax field whenever someone changes the order discount on order header level.
2) I update the order header sales tax from the sales order line form with each change of price, quantity or discount on an order line.

The sales app is about 1.6MB in size (accdb file)...I noticed that it was 3.5 MB in size when this problem occured. Could this be a memory leak problem? I try to clear large variables at the end of each routine/function, such as SQL statements, record sets etc. Should I also set all simple variables (Currency, String, Long etc) to null? Is there a good way to trace memory usage during the running of an Access app to see what garbage it leaves behind?

Option Compare Database
 
Public gvarCityTaxPercentage As Single
Public gvarCountyTaxPercentage As Single
Public gvarStateTaxPercentage As Single
 
Option Explicit
 
Private Sub Form_Load()
 
    gvarCityTaxPercentage = DLookup("[CityTaxPercentage]", "tblVariables")
    gvarCountyTaxPercentage = DLookup("[CountyTaxPercentage]", "tblVariables")
    gvarStateTaxPercentage = DLookup("[StateTaxPercentage]", "tblVariables")
    
End Sub

Open in new window

0
Comment
Question by:mobidutch
  • 4
  • 4
9 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22682435
Memory leaks in VBA are *very* uncommon.  Set your object variables to Nothing and you should be OK
(but even that is not always strictly necessary).

If you are creating custom classes it is possible to create a memory leak.  Are you doing any of that?
0
 

Author Comment

by:mobidutch
ID: 22682881
No..that would be well beyond my level of current expertise. This was my first time programming since I last coded something in C++ waaaaaay back in my college years :)

My functions/routines do use quite a few varXXXXX variables since I find that makes my code more readable, and most of those I don't touch at the end of a routine...I figured Access would kill those. I did develop the habit of closing databases, recordsets and such, as in the code snippet attached. So you are suggesting that I should set all used variables to Nothing as well?
    'Release variables
    rstOrderLines.Close
    dbsPOS.Close
    Set rstOrderLines = Nothing
    Set dbsPOS = Nothing
    strSQL = ""

Open in new window

0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22683498
C++... That would explain your concern about memory leaks :)

It is *always* a good idea to close Recordset and Connection objects when you are done, and it is also good
to be in the habit of setting your object variables to Nothing, although VBA usually does a good job of cleaning
those up.  That setting to Nothing thing only applies to objects; you cannot do that for variables of type Long,
String, Boolean, etc.
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Author Comment

by:mobidutch
ID: 22683516
Right..that's what I though. Thanks for confirming that. So why would the accdb file grow by 2MB..is that normal?   And for the love of something, I can't figure out from my code how it could possibly calculate tax as zero. It makes NO sense to me that the order total on the order header gets updated just fine, but the tax soes not. And in fact....given that the order total did NOT include the tax amount, I must conclude that somehow it lost track of the public variables that contained the tax percentages. Hmmmm. Well..maybe I'll just migrate the reading of the percentages to all individual functions that use them.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22683879
In the grand scheme of things, a jump in size of 2 MB is not really a big deal.  Some things to keep in mind:

1) Always do a compact on close, and regularly do a forced compact/repair.  As with SQL Server, when you
delete data, Access does not immediately release the disk space back to the operating system, and a
compact handles that (much like shrinking a SQL Server db).  The process also does other housekeeping.

2) If you recently added/tweaked a number of forms, reports, and/or VBA modules, that can swell the size
of the database, because Ofice apps in general do a bad job of that sort of housekeeping.  Sometimes the
only way to reclaim space wasted that way is to export all of your objects to a new, clean file.
0
 

Author Comment

by:mobidutch
ID: 22684341
Appreciate the advice, thanks!

Still, I am wondering how it could happen that the public variables containing the tax percentages were reset to zero (at least..I THINK that is what must have happenbed). I don't get it.

As a remedy, I am going get rid of the public variables and instead read the values on form open and assign them to invisible unbound form fields.  We'll see. Thanks!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22691856
mobidutch,

That's the catch with public variables.

From what I have always read and heard, you should only have one place in your code to set/reset them.

Most of the time programmers (myself included) will set and clear the Public variable at multiple points in the code.
Either because it gets the code working and is easy to do, or for testing purposes.
A lot of times when cleaning this up, it is easy to miss a stray:
    sglpubTaxRate=0
... in your code, and not notice it.
;-)

JeffCoachman
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 22692137
mobidutch,

Instead of relying on public variables, which can be cranky, why not store those values in "hidden" textboxes
on your form?  The Load event can set the values, and then the rest of your code can just refer to them...

Regards,

Patrick
0
 

Author Closing Comment

by:mobidutch
ID: 31504814
Yup...that was my line of thinking too in the previous post. I'll give that a shot. Thx!
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

810 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