too many modules

J

jWhytis

Hello All,

I have an MSAccess database that is slowly creeping toward the maximum
allowed module count. I have been asked to find a workaround past this
limit. We are currently using Access 2003 and are planning to migrate to
Access 2007. Before the databases are distributed to the users they are
converted to mde files.

I have created a solution that launches a “parent†access .mdb/mde and
spawns “child†mdb/mdes using automation. The idea here would be to spread
the forms & modules across multiple mdbs and switch between them based on
user need. This works with the notable problem of when the child apps need
to act on the parent app the only way to reference the “parent†is to use the
getObject method. While it works in test, the parent app has to be the first
MSAccess application open and the doc on getObject says the order of objects
returned is not guaranteed.

Because of these problems I’m being asked if there is a way to:
1. Create two mdes, Amdb.mde and Bmdb.mde and from Amdb.mde call a form in
Bmdb.mde in such a way the form from Bmdb.mde comes up in Amdb.mde.
2. Use references to create forms/modules that reside outside the mde file
but can be used within the mde – Perhaps ActiveX controls or dlls?
3. Some other thing we have not been clever enough to think of.

Any help would be appreciated.

jWhytis
 
C

Chris O'C via AccessMonster.com

1000 modules are a *lot* of modules. Are you sure you can't combine some of
the procedures and properties into common standard modules?

If not, create library mdes and add them to your references like you would
for dll libraries.

When coding a library db, use codedb and codeproject instead of currentdb and
currentproject to call/open the library's objects like tables and forms
rather than the current db's objects.

Chris
 
M

Marshall Barton

jWhytis said:
I have an MSAccess database that is slowly creeping toward the maximum
allowed module count. I have been asked to find a workaround past this
limit. We are currently using Access 2003 and are planning to migrate to
Access 2007. Before the databases are distributed to the users they are
converted to mde files.

I have created a solution that launches a “parent” access .mdb/mde and
spawns “child” mdb/mdes using automation. The idea here would be to spread
the forms & modules across multiple mdbs and switch between them based on
user need. This works with the notable problem of when the child apps need
to act on the parent app the only way to reference the “parent” is to use the
getObject method. While it works in test, the parent app has to be the first
MSAccess application open and the doc on getObject says the order of objects
returned is not guaranteed.

Because of these problems I’m being asked if there is a way to:
1. Create two mdes, Amdb.mde and Bmdb.mde and from Amdb.mde call a form in
Bmdb.mde in such a way the form from Bmdb.mde comes up in Amdb.mde.
2. Use references to create forms/modules that reside outside the mde file
but can be used within the mde – Perhaps ActiveX controls or dlls?
3. Some other thing we have not been clever enough to think of.


It's is very difficult to imagine a db with a thousand
form/report modules, class modules and standard modules.

How about just combining bunches of your standard modules?

A relatively sane approach is to create a library mde with
many/most of your existing standard modules.

Using forms/reports in one db from another db is not
something to get involved with if there is any other way to
deal with the problem.
 
J

jWhytis

Thanks Chris,

I know 1000 modules is alot, we have alot of forms/reports. :) We only
have 32 modules listted in the Modules tab. The rest, some 850, are behind
forms and reports. Combining/moving modules alone wont help me unless I can
move the code behind the forms/reports. I'm looking at kb88175 and it
indicates a library might do just that. I'll check it out and let you know.
 
J

jWhytis

Thanks Marshall,

Hard to imagine and true, we have enough forms and reports with code behind
them that we are worried about module count. We only have 32 pure modules,
the rest are behind forms and reports. I will look into the library database
concept and see what it does for us. Thanks for the help.
 
C

Chris O'C via AccessMonster.com

Instead of kb88175, you'd be better off with the article for Access 97
libraries and add ins:

http://technet.microsoft.com/en-us/library/cc767940.aspx

Before you go to the trouble of using libraries, see if you can't combine
some of those 850 forms and reports that have similar looks and functionality.
When the form opens it can show the fields and filters for form1 if the
form's openargs is the string "form1" or shows the fields and filters for
form2 if openargs is "form2".

Chris

I know 1000 modules is alot, we have alot of forms/reports. :) We only
have 32 modules listted in the Modules tab. The rest, some 850, are behind
forms and reports. Combining/moving modules alone wont help me unless I can
move the code behind the forms/reports. I'm looking at kb88175 and it
indicates a library might do just that. I'll check it out and let you know.
1000 modules are a *lot* of modules. Are you sure you can't combine some of
the procedures and properties into common standard modules?
[quoted text clipped - 29 lines]
 
M

Marshall Barton

jWhytis said:
Hard to imagine and true, we have enough forms and reports with code behind
them that we are worried about module count. We only have 32 pure modules,
the rest are behind forms and reports. I will look into the library database
concept and see what it does for us.

OMG!

That is one whopper of an app. Are you sure you can not
combine many of those reports and/or forms using a little
VBA to maky specific adjustments?

The problem with putting forms and reports into a library is
specifying which db the record source table resides in. If
you can work through that, the only other issue is the
library db needs a public function to open its forms. The
function would need all the arguments you use with the
OpenForm method and use those arguments in the function's
OpenForm line. Then the main db's code would call your
function instead of using OpenForm. Similarly to open
reports.
 
G

Graham Mandeno

Another thing to consider is that many of your forms and reports might have
empty modules, containing nothing more than:

Option Compare Database
Option Explicit

If so, then you can set the HasModule property for these to False.

Also, you might have many form/report modules where the only code is to
handle an event like, say, the click of a "Close" button. You can write
generic public functions to perform these common tasks - for example:

Public Function CloseForm(f as Form)
On Error resume next
DoCmd.Close acForm, f.Name
End Function

Then, for your button's OnClick property, instead of:
[Event Procedure]
use
=CloseForm([Form])

Then, once again you can set HasModule to False.

For a report, often the only code in the module is:
Private Sub Report_NoData()

Once again, the NoData event can easily be handled by a generic function.
 
J

jWhytis

thanks Chris,
We have already been down that road. We have been combining forms or
creating generic forms that collect common info before calling a specific
form/report for a while. If a form/report still exists in the database it is
collecting info that is unique to it or its function.

Chris O'C via AccessMonster.com said:
Instead of kb88175, you'd be better off with the article for Access 97
libraries and add ins:

http://technet.microsoft.com/en-us/library/cc767940.aspx

Before you go to the trouble of using libraries, see if you can't combine
some of those 850 forms and reports that have similar looks and functionality.
When the form opens it can show the fields and filters for form1 if the
form's openargs is the string "form1" or shows the fields and filters for
form2 if openargs is "form2".

Chris

I know 1000 modules is alot, we have alot of forms/reports. :) We only
have 32 modules listted in the Modules tab. The rest, some 850, are behind
forms and reports. Combining/moving modules alone wont help me unless I can
move the code behind the forms/reports. I'm looking at kb88175 and it
indicates a library might do just that. I'll check it out and let you know.
1000 modules are a *lot* of modules. Are you sure you can't combine some of
the procedures and properties into common standard modules?
[quoted text clipped - 29 lines]
but can be used within the mde – Perhaps ActiveX controls or dlls?
3. Some other thing we have not been clever enough to think of.
 
J

jWhytis

Marsh,
Yes, it is a big app, we have been down the road of combining and
consolidating and feel we have gotten all we can get there.
 
J

jWhytis

Thanks Graham,

I feel good about not having many empty modules, the development team is
aware of the problem and we have had several projects to simply go thru and
set the "Has module" property appropriately. While most of the forms have
code to perform edits on user input it is possible the report/no data event
could give us a few modules back.

Graham Mandeno said:
Another thing to consider is that many of your forms and reports might have
empty modules, containing nothing more than:

Option Compare Database
Option Explicit

If so, then you can set the HasModule property for these to False.

Also, you might have many form/report modules where the only code is to
handle an event like, say, the click of a "Close" button. You can write
generic public functions to perform these common tasks - for example:

Public Function CloseForm(f as Form)
On Error resume next
DoCmd.Close acForm, f.Name
End Function

Then, for your button's OnClick property, instead of:
[Event Procedure]
use
=CloseForm([Form])

Then, once again you can set HasModule to False.

For a report, often the only code in the module is:
Private Sub Report_NoData()

Once again, the NoData event can easily be handled by a generic function.

--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

jWhytis said:
Thanks Marshall,

Hard to imagine and true, we have enough forms and reports with code
behind
them that we are worried about module count. We only have 32 pure
modules,
the rest are behind forms and reports. I will look into the library
database
concept and see what it does for us. Thanks for the help.
 
J

jWhytis

Another thing to consider is that many of your forms and reports might have
empty modules, containing nothing more than:

Option Compare Database
Option Explicit

If so, then you can set the HasModule property for these to False.

Also, you might have many form/report modules where the only code is to
handle an event like, say, the click of a "Close" button. You can write
generic public functions to perform these common tasks - for example:

Public Function CloseForm(f as Form)
On Error resume next
DoCmd.Close acForm, f.Name
End Function

Then, for your button's OnClick property, instead of:
[Event Procedure]
use
=CloseForm([Form])

Then, once again you can set HasModule to False.

For a report, often the only code in the module is:
Private Sub Report_NoData()

Once again, the NoData event can easily be handled by a generic function.

--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

jWhytis said:
Thanks Marshall,

Hard to imagine and true, we have enough forms and reports with code
behind
them that we are worried about module count. We only have 32 pure
modules,
the rest are behind forms and reports. I will look into the library
database
concept and see what it does for us. Thanks for the help.
 
J

JimBurke via AccessMonster.com

I can't think of a way to move code out of a form completely. You normally
have code that is triggered by form controls and/or form events, and you have
to have at least SOME code in the form's module. You could get to the point
where the form procedures just call other procedures in a separate module,
and you could dramatically cut down the amount of code in the form module,
but that still wouldn't eliminate the form module.

On second thought, maybe instead of using event procedures you could use
macros, and the macros could use RunCode to call procedures - it might be a
bit of work, but that may allow you to eliminate all code from some forms.
Any code that refers to form controls would have to be modified, i.e.
instead of using Me!ctlName you'd have to code Forms!frmName!ctlName.
On a bit more reflection, perhaps we can move code from multipe simple forms
to one module...
Another thing to consider is that many of your forms and reports might have
empty modules, containing nothing more than:
[quoted text clipped - 77 lines]
 
J

John W. Vinson

I can't think of a way to move code out of a form completely. You normally
have code that is triggered by form controls and/or form events, and you have
to have at least SOME code in the form's module

Well... actually you don't. If you put the event code in a Function (not a
Sub) in a standard module, you can execute the code by putting

=Functionname(Form, <other arguments>)

in the Event Properties cell for the desired event. The function would have

Public Function Functionname(frm as Form, <arguments>)

The form reference is of course only needed if the code needs to reference the
form; you can also include control references if you need to see a control's
Text, Value, Oldvalue etc. properties.
 
J

jWhytis

At one point many years ago we hit the limit and had to go to macros. It
works but no one was happy with it. An upgrade in Access versions gave us a
higher module count. History repeasts itself.

JimBurke via AccessMonster.com said:
I can't think of a way to move code out of a form completely. You normally
have code that is triggered by form controls and/or form events, and you have
to have at least SOME code in the form's module. You could get to the point
where the form procedures just call other procedures in a separate module,
and you could dramatically cut down the amount of code in the form module,
but that still wouldn't eliminate the form module.

On second thought, maybe instead of using event procedures you could use
macros, and the macros could use RunCode to call procedures - it might be a
bit of work, but that may allow you to eliminate all code from some forms.
Any code that refers to form controls would have to be modified, i.e.
instead of using Me!ctlName you'd have to code Forms!frmName!ctlName.
On a bit more reflection, perhaps we can move code from multipe simple forms
to one module...
Another thing to consider is that many of your forms and reports might have
empty modules, containing nothing more than:
[quoted text clipped - 77 lines]
something to get involved with if there is any other way to
deal with the problem.
 
J

JimBurke via AccessMonster.com

Along the lines of using macros, but simpler since you don't have to create
the macro to do the call. Didn't realize you could do this - I was thinking
the only other option besides event procs was macros. I would think this
method could be a way of eliminating some (or many) form modules, unless
there's something I don't understand about the issue here.
 
C

Chris O'C via AccessMonster.com

Not this time. Access 2007 is limited to 1000 modules too.

Chris
 
S

Stuart McCall

John W. Vinson said:
Well... actually you don't. If you put the event code in a Function (not a
Sub) in a standard module, you can execute the code by putting

=Functionname(Form, <other arguments>)

in the Event Properties cell for the desired event. The function would
have

Public Function Functionname(frm as Form, <arguments>)

The form reference is of course only needed if the code needs to reference
the
form; you can also include control references if you need to see a
control's
Text, Value, Oldvalue etc. properties.

Hi John

This technique works well. I use it extensively. With one or two caveats:

You can't do anything useful with the keydown, keypress and keyup events
because Access supplies the keycode, keyascii via the event proc parameters.

Also the NotInList event can't be called this way either.

Also you can't cancel an update in BeforeUpdate (although a trick to work
around this is to put DoCmd.CancelEvent in the function instead).
 
J

Jack Leach

What is the maximum amount of form's reports that any individual user might
require for a specific task? Surely not 1000 (I wouldn't think so anyway).

The reason I ask is, if you have X number of users, and alltogether they use
this many forms/reports that is giving you the module count issue, you may
want to consider seperate front ends for different 'types' of users. Even if
that is not possible, you may want to see about breaking this FE into
seperate smaller ones based on catagory of tasks to be completed. This would
facilitate the user possibly having to close one db before opening another
for a different type of task, but it may be something to consider.

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)



jWhytis said:
At one point many years ago we hit the limit and had to go to macros. It
works but no one was happy with it. An upgrade in Access versions gave us a
higher module count. History repeasts itself.

JimBurke via AccessMonster.com said:
I can't think of a way to move code out of a form completely. You normally
have code that is triggered by form controls and/or form events, and you have
to have at least SOME code in the form's module. You could get to the point
where the form procedures just call other procedures in a separate module,
and you could dramatically cut down the amount of code in the form module,
but that still wouldn't eliminate the form module.

On second thought, maybe instead of using event procedures you could use
macros, and the macros could use RunCode to call procedures - it might be a
bit of work, but that may allow you to eliminate all code from some forms.
Any code that refers to form controls would have to be modified, i.e.
instead of using Me!ctlName you'd have to code Forms!frmName!ctlName.
On a bit more reflection, perhaps we can move code from multipe simple forms
to one module...

Another thing to consider is that many of your forms and reports might have
empty modules, containing nothing more than:
[quoted text clipped - 77 lines]
something to get involved with if there is any other way to
deal with the problem.
 
J

jWhytis

thanks Jim, John and Stuart for fleshing this out. Many of our forms can be
grouped together into functional units of 5 or 6 so if I can transfer the
code from a functional group into one module the savings would be great.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top