Solved

Access 2007: How to check for memory leak?

Posted on 2008-10-09
9
1,701 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
 

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

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…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…

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

22 Experts available now in Live!

Get 1:1 Help Now