Solved

Access 2007: How to check for memory leak?

Posted on 2008-10-09
9
1,738 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

920 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

16 Experts available now in Live!

Get 1:1 Help Now