Maximun Number of Mof VBA Modules in Access 2007 DB

[giovanniroi]

Dear Friends,
I ask You to help me to understand if the Maximum Number of Modules defined in the Microsoft specification for an Access 2007 DB ( 1000 modules) he intends for maximun number of modules or maximum number of routine ( one module can contein more routine).
The same rule is appliable also for the modules in the Form? (es. one form with code = one module)?.

Thank's you for help.
Regards.

Giovanni Roi

 

[HiTechCoach]

from Microsoft's Access 2007 specifications (Click Here)

Number of modules (including forms and reports that have the HasModule property set to True) = 1,000

It is true a Code Module can contain many Sub's and Functions.

So you can have a total of 1000 objects with code modules.

Forms with Code + Reports with Code + Modules  = 1000 max

I generally avoid code behind forms unless absolutely required. Instead I use embedded macros and functions in a shared VBA code module. Lots of form code can be shared. I set the event to =MyFunction to get around having code in behind the form.

I also create multiple front ends. By using two front end I can have 2000 modules.

See: Splitting your Access database into application and data

 

[giovanniroi]

Dear Boyd,
thank's for Your information. You confirm me that I can have 1000 modules and that I can have many code if I insert in one module more Function or Subroutine.
Normaly I insert in one module from 10 to 30 Functions and in many case these are shared Function utilizable from many different component.
If I need to use more of 1000 module I develop more front-end.

My application now is in Access 2007, can I have important benefit if a upgrade to Access 2010?

Best regards

Giovanni Roi

 

[gemma-the-husky]

Giovanni

1000 modules is an enormous number.

However could you get close to that. You would have to put every function into its own module wouldn't you?

In Access, I don't think you can control the loading of different modules. I think module management might be important if you are trying to mange your app, by overlaying code modules - but I don't think you can do that directly in Access, can you?

 

[giovanniroi]

Dear Dave,
in Access I don't have problem to put one function in one module ore many function in one module.I prefer the second way because in this case any module manage a set of application functionality and the maintenance of the code is more easy in this case. In Access the loading of the module and loading of the Function/Subroutine are manage directly from the application that you make and there are no problem to manage many function in the same time.

Regards

Giovanni Roi

 

[HiTechCoach]

Giovanni
However could you get close to that. You would have to put every function into its own module wouldn't you?

This count also includes forms and reports with VBA code, not just VBA modules.

I have seen a Front end that had so many forms and report with VBA code that if you tried to open a report in design mode and then do a Save As that it would not save because it has passed the 1000 modules limit. It had only 10 VBA code modules.

 

[gemma-the-husky]

This count also includes forms and reports with VBA code, not just VBA modules.

I have seen a Front end that had so many forms and report with VBA code that if you tried to open a report in design mode and then do a Save As that it would not save because it has passed the 1000 modules limit. It had only 10 VBA code modules.

thanks for that nugget. I have some big apps with loads of forms and reports, and I am sure pretty well all the forms, but not all reports, have modules.

One to bear in mind.

 

[HiTechCoach]

thanks for that nugget. I have some big apps with loads of forms and reports, and I am sure pretty well all the forms, but not all reports, have modules.

One to bear in mind.

Yes it is.

 

[HiTechCoach]

This is also a reason why I have a Modules of function to perform standard form and report tasks. With a lot reports and forms I can call the functions so there is no VBA code module behind the form/report!