Solved

Access 2007: How to check for memory leak?

Posted on 2008-10-09
9
1,812 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Creating a Temp Table in MS Access 5 48
Copy all value in one form to another 4 42
MS Access Error Handling 6 31
How do a DCount on a report 1 18
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

696 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