Access 2007: How to check for memory leak?

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

mobidutchAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Patrick MatthewsConnect With a Mentor Commented:
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
 
Patrick MatthewsCommented:
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
 
mobidutchAuthor Commented:
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Patrick MatthewsCommented:
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
 
mobidutchAuthor Commented:
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
 
Patrick MatthewsCommented:
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
 
mobidutchAuthor Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
mobidutchAuthor Commented:
Yup...that was my line of thinking too in the previous post. I'll give that a shot. Thx!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.