MDE Creation Failure

[JohnA]

Hi there, please help me with this...
I have a big mdb file (110ΜΒ, MSAccess 2000 version with 110 tables, 350
queries, 160 forms containing code, 230 reports containing code, 120 macros,
and 1 module with many subs and functions) and I can' t create a mde file
from it.
I created a new empty MSAccess 2003 version mdb, imported all objects of the
original mdb and finally compacted and repaired the new mdb.
When trying to create the mde file, it fails displaying an msgbox with
prompt "Microsoft Office Access was unable to create an MDE database.".
Using a web search engine I discovered that I must compile each form and
correct any errors they may have. But I have 160 forms and 230 reports !!!
Isn't there any other way??
Have you got any ideas?
Sorry for the big message, I tried to explain all parameters of the problem
to help you help me...
Please please, help me with this...

Many thanks,
John

PS: Pressing the "Show Help>>" button on the msgbox, it displays the text:
This error is usually associated with compiling a large database into an MDE
file. Due to the method used to compile the database, a considerable number
of TableID references are created for each table. The Microsoft Jet database
engine version 4.0 can only create a maximum of 2048 open TableIDs at one
time. Exporting a database as an MDE potentially can exceed this limit if
the database has a large number of objects (table, macro, form, report, etc).
There is no accurate method to estimate the number of TableIDs the Jet
database engine uses during the process of compiling a database as an MDE.
However, each VBA module and each form uses one TableID, as a result, if the
database has 500 forms, and each form's HasModule property is set to Yes, as
many as 1,000 TableIDs are used.

 

[gg.20.keen4some]

Open any code module, then click the "Debug : Compile <project name>"
option. This will automatically compile all of the code in your
database. Then click Ctrl-S, close the visual basic editor, and try
again.

HTH,
TC

 

[TC]

Open any code module, then click the "Debug : Compile <project name>"
option. This will automatically compile all of the code in your
database. Then click Ctrl-S, close the visual basic editor, and try
again.

HTH,
TC

 

[Craig Alexander Morrison]

You MUST compile all code, as an MDE strips out all the source code having
first compiled it.

You SHOULD not have tables in your MDE create a split FE/BE arrangement,
this is just the way most professional developers would go.

Get rid of all the macros and replace them with VBA there is a tool for
this.

Use the StartUp form option to kick the application into life rather than an
AutoExec macro.

Summary you MUST compile all code, you should really have been making sure
it was compiled throughout the development.

110MB is very small for a database, but could be considered large for an
application, do the FE/BE split.

Search the newsgroups for splitting an application if you are uncleara about
this.

 

[JohnA]

Thank you all!
I followed your instructions for compiling my project and I found
garbage/code in many form modules (I think when I delete an object access
doesn't delete the code...). Thanks 2 u I now have my MDE file!!!!!!!!! I
splitted also the db and now I'm going to test if it works ok.
THANK U !