Bob Collison
asked on
Access 2003 DB Compile Fails On Too Many table Ids
Hi Experts,
I have a large MS Access 2003 MDB. The one MDB contains multiple distinct Modules each of which has its own Main Menu form.
In trying to compile it into a MDE it fails on too many open TableIDs for the MS Jet DB Engine V4.0.
What is the best method to overcome this problem? With each Module being quite distinct I could put each Module in a separate MDB / MDE and open them from a MDB / MDE containing the Application Main Menu. If so what code do I use to start them up?
Is there a better way to overcome this problem?
Thanks.
Bob Collison.
I have a large MS Access 2003 MDB. The one MDB contains multiple distinct Modules each of which has its own Main Menu form.
In trying to compile it into a MDE it fails on too many open TableIDs for the MS Jet DB Engine V4.0.
What is the best method to overcome this problem? With each Module being quite distinct I could put each Module in a separate MDB / MDE and open them from a MDB / MDE containing the Application Main Menu. If so what code do I use to start them up?
Is there a better way to overcome this problem?
Thanks.
Bob Collison.
ASKER
Hi Marco,
Normally I do a Debug > Compile and cleanup any issues before doing a Compact / Repair followed by a Make MDE but in this case I missed doing it. There was a compile error which I fixed and it created the MDE.
However, I do have a large number of Tables, Reports, Forms with a lot of development still to go so I may yet run into this problem. Therefore I would still like to know what my options are especially for running each Module as a separate MDE from a Common Module in MS Access.
Suggestions?
Thanks.
Bob Collison.
Normally I do a Debug > Compile and cleanup any issues before doing a Compact / Repair followed by a Make MDE but in this case I missed doing it. There was a compile error which I fixed and it created the MDE.
However, I do have a large number of Tables, Reports, Forms with a lot of development still to go so I may yet run into this problem. Therefore I would still like to know what my options are especially for running each Module as a separate MDE from a Common Module in MS Access.
Suggestions?
Thanks.
Bob Collison.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What do you mean when you say that each module has its "Main Menu form". A module does not have such thing as a menu or a form, it's only a container for code.
ASKER
Thanks for the solution and information regarding too many Objects.
ASKER
Hi James,
The whole Application has 10 Application Modules based on 'Subjects' e.g. Member Module, Finance Module, Event Module, etc. based on Subject DB Design (as opposed to Application DB Design. Each of these has a Form Object that provides access to and control of that Module. In addition there is an overall Application Menu Form Object from which the Application Module Menus are run. Although there is some Common Tables for lookups etc. (e.g. Address Master based on relational concepts) most of the data relating to a Subject is controlled within that module. Each Module has its own Back End DB for its data. This allows Users to have multiple copies of each Module DB. A Utility provides for the rapid switching / linking from one Module DB copy to another.
Thanks.
Bob Collison.
The whole Application has 10 Application Modules based on 'Subjects' e.g. Member Module, Finance Module, Event Module, etc. based on Subject DB Design (as opposed to Application DB Design. Each of these has a Form Object that provides access to and control of that Module. In addition there is an overall Application Menu Form Object from which the Application Module Menus are run. Although there is some Common Tables for lookups etc. (e.g. Address Master based on relational concepts) most of the data relating to a Subject is controlled within that module. Each Module has its own Back End DB for its data. This allows Users to have multiple copies of each Module DB. A Utility provides for the rapid switching / linking from one Module DB copy to another.
Thanks.
Bob Collison.
Bob,
Couple of comments to add in:
1. I would have tried using /decompile, just to make sure everything was good.
2. I would have gone through the DB and ensured that any form which had no code had it's "HasModule" property set to false.
3. If this was on the 64 bit edition of Office. The 32 bit edition doesn't seem to have as many issues.
Jim.
Couple of comments to add in:
1. I would have tried using /decompile, just to make sure everything was good.
2. I would have gone through the DB and ensured that any form which had no code had it's "HasModule" property set to false.
3. If this was on the 64 bit edition of Office. The 32 bit edition doesn't seem to have as many issues.
Jim.
ASKER
Hi Jim,
Thanks for the comments I'll keep them in mind for the future. Normally as I complete each bit of development I save it, Compact / Repair and then Compile. If it compiles I keep a copy of the Source as a backup in case I run into issues. If it doesn't compile I debug it or if necessary revert to the backup and start over (doesn't happen often).
I am developing on a 64 Bit Windows 7 Pro PC with Office 2010 Pro installed except for MS Access. The MS Access that I am using / developing in is Office 2003 Pro and for installations I provide the MDE Version along with MS Access 2003 Pro Runtime via InstallShield.
Before releasing the code I use FMS Total Access Analyser to review all errors / suggestions at which time I ensure that the HasModule property is set correctly for each form.
Thanks again.
Bob Collison
Thanks for the comments I'll keep them in mind for the future. Normally as I complete each bit of development I save it, Compact / Repair and then Compile. If it compiles I keep a copy of the Source as a backup in case I run into issues. If it doesn't compile I debug it or if necessary revert to the backup and start over (doesn't happen often).
I am developing on a 64 Bit Windows 7 Pro PC with Office 2010 Pro installed except for MS Access. The MS Access that I am using / developing in is Office 2003 Pro and for installations I provide the MDE Version along with MS Access 2003 Pro Runtime via InstallShield.
Before releasing the code I use FMS Total Access Analyser to review all errors / suggestions at which time I ensure that the HasModule property is set correctly for each form.
Thanks again.
Bob Collison
On the /decompile, that's something that is a little different. If you've "compiled", the VBA project carries two copies of the code; source and a partial compile (VBA only does an incremental compile, not a full compile in the usual sense of the word).
Sometimes due to corruption or Access bugs, the compiled version gets out of sync with the source. As a result, you may not be able to generate a MDE even though your code compiles fine.
The inability to produce a MDE almost always deals with code because a MDE is nothing more than a MDB with the source code stripped out.
You can either use /decompile (and for details, see here: https://www.experts-exchange.com/Database/MS_Access/A_2043-Decompile-What-it-is-what-it-does-and-how-to-use-it.html ) or import everything in a new DB container (only source code is copied when you do this).
That may or may not fix the issue, but often does.
Having a valid value for has module will reduce the number of table ID's required when creating a MDE and may get you past the limit, which is 2048. That varies a bit though as it's not a hard and fast limit, which brings up another point; when you go to create the MDE, make sure you do so after just opening Access. That way, nothing else is in memory at the time.
That can manage to eek out a few more table ID's on the limit.
Jim.
Sometimes due to corruption or Access bugs, the compiled version gets out of sync with the source. As a result, you may not be able to generate a MDE even though your code compiles fine.
The inability to produce a MDE almost always deals with code because a MDE is nothing more than a MDB with the source code stripped out.
You can either use /decompile (and for details, see here: https://www.experts-exchange.com/Database/MS_Access/A_2043-Decompile-What-it-is-what-it-does-and-how-to-use-it.html ) or import everything in a new DB container (only source code is copied when you do this).
That may or may not fix the issue, but often does.
Having a valid value for has module will reduce the number of table ID's required when creating a MDE and may get you past the limit, which is 2048. That varies a bit though as it's not a hard and fast limit, which brings up another point; when you go to create the MDE, make sure you do so after just opening Access. That way, nothing else is in memory at the time.
That can manage to eek out a few more table ID's on the limit.
Jim.
ASKER
Hi Jim,
Thanks very much for the insights. I'll use them in the future.
Thanks again.
Bob Collison.
Thanks very much for the insights. I'll use them in the future.
Thanks again.
Bob Collison.
It may also be caused by corruption, try doing a Compact and Repair (Database Utilities->Tools).
If none of the above suggestions work, create a new db and import all objects from the original file.