Modules and Object Overload

[drexasaurus]

We have a large project that's fairly old, and from experience I try to prevent too many objects from cluttering the Access file, such as reports, forms, queries, etc, as this seems to make the file quickly huge and create stability problems, crashes, etc..

Does anyone know, or have experience with projects with a large amount of class modules or standard modules? Can this be a problem as well, or can I have (almost) as many modules in a project as I want without causing any issues except perhaps complication or potential confusion?

[ItsMe]

The way I understand it is there is an advantage with Class Modules over Standard Modules. I believe a Standard module is available as soon as you launch the app. Where a class module needs to be instantiated before any system memory is reserved. So if you have 700 variables of Variant type declared publicly in one Standard module or twenty Standard Modules, you will see an impact at startup vs. using those same variables in various class modules like behind a form, behind a report, and (stand alone) Class Modules.

Either way, don't think it makes a bit of difference in the overall file size. Just in allocation of memory.

My approach is to use forms as much as I can and make sure I destroy it if I create. I try to keep my query objects to a minimum and adjust the SQL as I go but, I believe Access likes query objects compared to using SQL strings in VBA.

[orange]

drex,

Do you do regular Compact and Repairs? This will remove accumulated bloat that arises from deleting objects; developing new queries, forms, reports and building new versions. Old versions (ie non current version of table,query,form, report..) will take up space that you can not recover without doing C&R.

[Missinglinq]

Some things to bear in mind:

Doing a Compact & Repair on a regular basis is, as orange said, a great way to hold down bloat, but don't fall into the trap of using the app's 'Compact on Close' option! There is a great deal of empirical evidence that C & R's are a frequent cause of database corruption. because of this, it is prudent to always create a backup copy of your file before doing a C & R.

Also, vis-à-vis creating and deleting objects, you should keep in mind that each object has a lifetime limit. The limit for Modules is 1000, and this includes the Modules associated with Forms and Reports. You can find a list of the limits here:

http://www.databasedev.co.uk/access_specifications.html

The 'lifetime' portion of lifetime limit means that even if an object is deleted, it still counts toward the limit. While doing a C & R will recover the space deleted objects were allocated, to my knowledge it does not change the total count of the number of any given object. The only solution, if a limit is reached, would be to create a new, blank file and import all objects into it.

Linq ;0)>

[drexasaurus]

Yes, I compact and repair regularly, without the compact and repair on close method. It would seem that there doesn't seem to be too much concerns about the number of modules, or using smaller modules verses larger, etc. Sometimes there seems to be a solution that could be more easily solved with a handful of class modules than one complex module, but I am naturally hesitant to add ten objects to a large project without pause. In this case, seems to be unfounded. Thanks!

Some things to bear in mind:

Doing a Compact & Repair on a regular basis is, as orange said, a great way to hold down bloat, but don't fall into the trap of using the app's 'Compact on Close' option! There is a great deal of empirical evidence that C & R's are a frequent cause of database corruption. because of this, it is prudent to always create a backup copy of your file before doing a C & R.

Also, vis-à-vis creating and deleting objects, you should keep in mind that each object has a lifetime limit. The limit for Modules is 1000, and this includes the Modules associated with Forms and Reports. You can find a list of the limits here:

http://www.databasedev.co.uk/access_specifications.html

The 'lifetime' portion of lifetime limit means that even if an object is deleted, it still counts toward the limit. While doing a C & R will recover the space deleted objects were allocated, to my knowledge it does not change the total count of the number of any given object. The only solution, if a limit is reached, would be to create a new, blank file and import all objects into it.

Linq ;0)>

That's interesting. I didn't know that! I don't seem to think that will be an issue, thankfully.