Memory issues in 2010

[Gkirkup]

We recently moved from Access 2003 to Access 2010, and the users are having serious memory issues. Having open a couple of forms at once often results in an error message or a white screen. What can I do to free up more memory?

Robert

 

[Pat Hartman]

• If it is not necessary to have multiple forms open at once, rework the application to prevent it.
• If there is a lot of VBA behind the forms, you can move it to standard modules. That way you can limit the form code to calls to the standard modules and only load the code as it is needed.
• If you are using DAO/ADO, make sure you close all objects before leaving the procedure.
• If you are using SQL strings in code, saved querydefs would be more efficient.

 

[Gkirkup]

Pat:
This is happening mostly on one large form. It's as wide as Access allows. Users will sometimes have other forms open and I have no control over that.
There is quite a lot of VBA code on the big form, so I take your point that it could be moved. How would I move - say - On Open code? Have that code in a module and then in On Open just a call to that function in the module? Would that save memory?

Robert

 

[Gkirkup]

Pat:
This is happening mostly on one large form. It's as wide as Access allows. Users will sometimes have other forms open and I have no control over that.
There is quite a lot of VBA code on the big form, so I take your point that it could be moved. How would I move - say - On Open code? Have that code in a module and then in On Open just a call to that function in the module? Would that save memory?

Robert

 

[Pat Hartman]

You'll need to make a few changes to the code to move it from a form to a standard module since the code probably uses "Me." or "Me!". For starters, change all those references to "frm.". Then you would take each procedure and make a new procedure in a standard module. For example. For the Open event, create a procedure named "myOpenEvent".

Public Sub myOpenEvent(frm as Form)
    'all the code goes here after you replace Me. with frm.
End Sub

Then in the actual open event, you would replace the code with one line to call the new procedure:

Call myOpenEvent(Me)

The "Me" argument will pass a reference to the current form to the procedure and the procudure refers to it as "frm." since only Form and Report class modules actually use "Me" to reference their objects.

Modules are loaded individually so put code that is used together in the same module where possible. It does you no good to force Access to load 5 code modules instead of one so you will need to analyze the code and do your best to organize it to minimize the modules that need to get loaded.

 

[Gkirkup]

Pat: Thanks, that is most helpful.

Robert