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.

Anyone?

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.

Thankyou!

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.

mmcdonal

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