Reducing Memory Overhead

A modern desktop computer running Access will typically have anywhere between 32 and 512 MB of memory (RAM). This is where all of your application's code is executed. The more memory your computer has, the more likely it will be that the data needed by an application will be available in memory, and therefore the less frequently the application will need to read from and write to the disk. Reading and writing to disk is a relatively slow process and the less disk access is required, the faster the program will typically run.

As a general rule, more memory equals better performance. In the dim and distant past, computers were limited to around 32 or 64 kilobytes of memory. To put this in perspective, that is about 2000 times less than the amount in the machine that I am using to produce this chapter. Even if you had an operating system or program able to use 96 MB of RAM in those days and that is much more than was utilized in many mainframes the sheer cost of the memory would have torn your scheme to shreds.

It's not surprising, therefore, that with such limited memory available, programmers spent a great deal of time shoe-horning their quart of code into the pint pot that was their computer. The key phrase was 'disciplined programming'; the language was typically assembler or machine code (almost impenetrable to the layman) and the results produced were a testimony to the ingenuity and patience of the programmers involved.

But these days we live on easy street... if a program is running slowly, just spend $20 on another 64 MB of RAM for your machine! This isn't a completely heinous attitude after all, it might cost $40,000 in man-days to recode the program so that it runs as quickly on the old machine as if you just bought the memory.

However, that is not to say that we should let this newfound freedom allow us to churn out sloppy code. Memory, although relatively cheap, is still precious. The less memory your program takes up, the faster it, and all the other programs running simultaneously , should perform.

Additionally, if you are writing an application that will be used by a thousand users, then every extra megabyte of memory required by your application equates to 1000 MB of memory across all those machines.

In other words, for most projects, producing an application with a small memory footprint is still a very real coding priority.

You should, therefore, bear the following guidelines in mind when developing any application:

• Use the right data type

• Group procedures into modules

• Reclaim memory where possible

• Don't load unnecessary modules/libraries

• Save the database as an MDE file

Use the Right Data Type

Different types of variable take up different amounts of memory. The size of the memory taken up by each of the data types is shown in the table below:

Data type	Storage size	Range
Byte	1 byte	0 to 255
Boolean	2 bytes	True or False
Integer	2 bytes	-32,768 to 32,767
Long	4 bytes	-2,147,483,648 to 2,147,483,647
Single	4 bytes	-3.403E38 to -1.401E-45; 0; 1.401E-45 to 3.403E38
Double	8 bytes	-1.798E308 to -4.941E-324; 0; 4.941E-324 to 1.798E308
Currency	8 bytes	-922,337,203,685,477.5808 to 922,337,203,685,477.5807
Decimal	12 bytes	-7.923E28 to 7.923E28 (varies with number of decimal places in number stored)
Date	8 bytes	January 1, 100 to December 31, 9999
Object	4 bytes + the size of the object	A reference to any object
Fixed String	1 byte per character	Up to approx. 65,400 characters
Variable Length String	10 bytes + 1 byte per character	Up to approx. 2 billion characters
Variant (numeric)	16 bytes	As double
Variant (string)	22 bytes + 1 byte per character	As variable length string

As you can see, variables of type Long take up twice as much memory as variables of type Integer . But then again, optimization is a question of compromise Long variables can hold a much wider range of values than Integer variables can.

The problem of memory usage becomes even more marked when dealing with arrays. This line:

 ReDim adbl(9, 9) As Double 

declares an array containing 100 elements and takes up around 800 bytes of memory, compared to the 200 or so bytes taken up by this one:

 ReDim aint(9, 9) As Integer 

For more detailed information on calculating the memory requirements of arrays, refer back to the Memory Considerations section in Chapter 11.

As a rule, if memory footprint size is a coding priority as it nearly always is you should choose the smallest variable that can hold the values that you will be dealing with. To remind you to explicitly assign types to variables, you should tick the Require Variable Declaration option on the Editor tab in the VBA Tools/Options... dialog. This is also a good coding practice, because it avoids variable name mismatch, and minimize the bugs caused by this problem.

It has been said before but I make no apology for saying it again always, always be wary of the Variant data type. Although there are situations in which it is useful and sometimes necessary to use a Variant data type, you should bear in mind that not only does it take up significantly more memory than the other data types, but it can also lead to errors in your code going undetected. For example, I'll wager that no one reading this will be able to accurately predict what 10 values the following procedure will print in the Immediate window Try it out and see how many you guessed correctly!

 Sub AreYouSure() Dim v1 As Variant Dim v2 As Variant Dim v3 As Variant v1 = 1 v2 = "1" v3 = "(1)" Debug.Print v1 + v2 Debug.Print v1 + v3 Debug.Print v2 + v3 Debug.Print Debug.Print v1 & v2 Debug.Print v1 & v3 Debug.Print v2 & v3 Debug.Print Debug.Print v2 + v1 + v3 Debug.Print v2 & v1 + v3 Debug.Print (v1 & v2) + v3 Debug.Print v1 + (v1 & v2) + v3 End Sub 

The answers are as follows :

 v1 + v2                   = 2 v1 + v3                   = 0 v2 + v3                   = 1(1) v1 & v2                   = 11 v1 & v3                   = 1(1) v2 & v3                   = 1(1) v2 + v1 + v3              = 1 v2 & v1 + v3              = 10 (v1 & v2) + v3            = 11(1) v1 + (v1 & v2) + v3       = 11 

Group Procedures into Modules

VBA only loads modules or classes when a procedure in that module or class is called. This is called loading on demand. Therefore, if you have a routine that calls three procedures and they are all in separate modules, all three modules will be loaded into memory. By judiciously grouping related procedures into the same module, you can minimize the number of modules loaded into memory at any one time.

Reclaim Memory Where Possible

You can use the Erase statement to reclaim the memory used by a dynamic array. When you have finished with the array, using the Erase statement will discard the data in the array and free up the memory that it had been using.

The Erase statement doesn't reclaim space from static ( fixed-size ) arrays. However, it does reinitialize them. For more information on reinitializing arrays, refer back to Chapter 11.

You can also reclaim the memory used by object variables when you have finished with them. You do this by setting them to a special value called Nothing .

 Set objExcel = Nothing 

Remember, however, that the memory used by an object cannot be reclaimed if there is another reference to the object elsewhere in code. We used this to our advantage when we examined classes where we prevented an instance of a popup form from being destroyed by placing a reference to it in a collection declared at the module level of another form. This ensured that the popup form was not destroyed until the second form was closed and the collection went out of scope.

Don't Load Unnecessary Libraries

We saw earlier how library databases can be a useful way to store and re-use frequently needed procedures. They can also be used to house wizards and add-ins, such as the control and form-design wizards that ship with Access. However, each of these library databases needs to be loaded into memory when used and that can have a significant hit on the amount of memory that is being used. So, to reduce the memory footprint of your installation, you should use the Add-In Manager (available from the Tools/Add-Ins menu in Access) to unload any library databases or add-ins that are not essential.

Save as an MDE

Previously, we looked at the final touches that you should apply to your application before you give it to the end users of the databases. One of those things is converting your database into an MDE file. This conversion compiles any modules within the database and then strips out the original source code. This in turn has the twin advantages of making your database more secure and reducing the memory footprint of your application, because the source is not kept.

Bear in mind that you cannot modify the design of MDE files and that you should always keep an original source version of your database in MDB format.

A Final Recommendation Buy More Memory!

No, it's not cheating! It is recommended that you have 32MB (Win98), 40MB (WinME, NT4 or later), 72MB (Windows 2000), or more of memory if you are running Access

If you are running on a network, or intend to use other Windows applications at the same time, you will find that the extra few dollars it will cost to buy another 32 or 64MB will be well worth it.