Wrox Programmer Forums
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old February 1st, 2008, 01:16 PM
Authorized User
Join Date: Feb 2007
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default Variables persisting in memory

I have a lot of VBA code which deals with the importation of raw data and the populating of base tables in my Access 2007 database. All this code is initiated from a form, where you browse for the file to be imported and click 'Import' (funnily enough).
I've noticed that once the import is complete and the form has closed, there are variables that are still persisting in memory, even though code execution has stopped. For example, I have a global string variable that contains the file path to the source data after the user has clicked 'Browse'. This variable still contains the filepath once execution has finished. I find I actually have to click on the stop button in the VBE to completely reclaim all memory.
Why is this? What are the rules with Access? With Excel, variables, both global and module level, go out of scope when execution has completed and forms are closed.

Old February 7th, 2008, 11:03 AM
Authorized User
Join Date: Feb 2007
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts


I wouldn't have though this a difficult question to answer for any Access experts on here.

I'll reiterate the problem again more clearly if it helps...

I want to know why public variables remain in memory after code execution has finished and all forms have unloaded. I could understand it if such variables were instances of objects that had not been set to nothing, but these are simple types like strings and integers. As I've said before, I have to click on the stop button in the VBE to completely reset all variables and reclaim memory.

It's not a major issue, but it would help my understanding of Access programming to know this. Coming from an Excel background, which behaves differently in this respect, it's not immediately obvious to me as it may be for others.


Old February 12th, 2008, 08:34 AM
Friend of Wrox
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts

Public variables are supposed to remain in memory while Access is open. That is why they are public. I frequently use this service to post values when moving between forms.

Typically at the end of a routine, I will reset the value of the variable, for example a string to "", or an integer to 0. Then when I use the code, I first check for string <> "" or integer <> 0 to make sure a new value has been taken.

If you don't want this public property, then don't reserve the memory space. Use "Dim sString As String" instead of "Public pString As String", and you will get the memory space back after your sub or function runs.

Did that help? Look up "Declaring Variables" in Access VBA.


Look it up at: http://wrox.books24x7.com

Similar Threads
Thread Thread Starter Forum Replies Last Post
Persisting Simple Association aadz5 Hibernate 1 August 13th, 2008 03:49 AM
persisting data in a web app badgolfer ASP.NET 1.0 and 1.1 Basics 3 October 20th, 2004 01:59 PM
Memory linguva Access 2 December 19th, 2003 07:22 PM
Persisting viewstate between pages drettberg ASP.NET 1.0 and 1.1 Professional 3 September 22nd, 2003 11:30 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.