too many modules

[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

 

[Marshall Barton]

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.

 

[Marshall Barton]

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.

 

[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.

 

[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.

 

[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.

 

[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.

 

[Marshall Barton]

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.

 

[Marshall Barton]

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.

 

[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.

 

[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.

 

[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.

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.

 

[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.

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.

 

[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.

 

[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.

 

[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.

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

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.

 

[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.

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

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.

 

[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

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.

 

[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

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.

 

[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.