Pain in the Access

Geek50 Comments

Last weekend, I had some free time so I decided to try and convert my Microsoft Access application from Access 97 to Access 2003. Access 97 has done well for me and I have it working perfectly for me. However, there a just a few features in later versions of Access that I would like to take advantage of. I took the .MDB file and converted it to a 2003 format. No problem. Looks good. Then I went to generate the .MDE file (the equivalent of compiling the database) and it failed with this crazy error:

Microsoft Office Access was unable to create an MDE database.

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.

Huh? Searching I found a knowledge base article that says, “Yep, this is an issue”. Thanks. Going back it seems that this same limitation exists in all versions of Access. But I don’t believe it totally because I can create a .MDE from my same database in Access 97 but not in Access 2003. Nothing changed on my end so Microsoft must have changed something inside the database. Maybe they use more TableIDs for objects now or something. I am stuck.

So far I have tried:

  • Taking out about 50 random modules just to see if I could drop below the limit. That didn’t work. The algorithm for TableIDs must have really changed!
  • Creating an empty database and importing my code into it. Thought maybe it would get rid of some old cached items or state of some sort. That didn’t work.
  • Bang my head into a concrete wall over and over. It just makes me feel dizzy.

Anybody have a “big” .MDE? How many objects do you have in it? Maybe a solution will surface somewhere.