Tuesday, February 2, 2010

Microsoft Access was unable to create the .accde, .mde or .ade file

Problem

Many times we have faced issue that after creating the MDB file, when we try to compile the database to MDE file ,Access gives the following error :

Microsoft Access was unable to create the .accde, .mde or .ade file

On clicking the button 'Show Help', it shows following error message:

This error is usually associated with compiling a large database into an MDE file.  Because of the method used to compile the database, a considerable number of TableID references are created for each table.  The Access database engine 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 Access 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.


Solution:

The main reason of database not getting compiled to mde is that it failed during compilation. That is , yiu have some code tha failed to compile.

Steps:

1. Press Alt + F11 to display VBA editor.
2. In the menu 'Debug' , you will find 1st menu item as 'Compile AccessDb'.
3. Click that option.
4. If there is no error, the database will get compiled.
5. If during compilation, any error is encountered, the VBA editor will display that error and you can rectify that.
6. Once all errors get rectified, you will be able to compile the database.


Once successfully compiled, try making the mde again ans it will definitely make the mde file.

22 comments:

Anonymous said...

I compiled, no errors. went to make mde and it failed. Says collating sequence not valid or something. Using 2010.

Anonymous said...

You Rock Dude!

Anonymous said...

Thanks, man. A life saver!

Anonymous said...

Thanks for the great post. You taught me something today AND helped me to get my database to ACCDE!

Anonymous said...

Thanks man, it works.

I just wonder why is this not written in the help...

Anonymous said...

Varun - It worked! your an absolute star. Many thanks indeed.

I echo the comment above - why on earth has Microsoft not posted this suggestion instead of the ridiculously long winded [and fairly useless] explanation in the 'help' [ha ha]? I have spent hours re-working my database in an attempt to rectify the problem.

Moqadas Khawarin said...

Thank you so much for your very valuable post. it highly and greatly helped me. appreciated

Anonymous said...

Thank you, yes now I can make the MDE file. I spent hours trying to solve this, and your steps took only a minute to solve. So thank you.

Varun Sharma said...

Thanks buddy..Good to see the article helped you all..

Varun

Jeremiah said...

Awesome, took just seconds to do it and was making the accde file. Many thanks

Anonymous said...

If that doesn`t work (because there`s no compiler error but the code itself is "damaged"): cut the code of the module / formcode and paste it into notepad. Compile again (there may be new errors.. doesn`t matter). Copy and paste the code from notepad to the code windo in access and recompile..

Mohan Vardharajan said...

Thank you so much for your valuable suggestion.

Anonymous said...

Thanx alot ....

Derek Brown said...

Very helpful, Thank you!

Luke G. said...

Worked perfectly for me! Thanks!

Sewa Kantor said...

Thanks it works, but I get error message "Unexpected error (40230)"

Hannah said...

Worked perfectly, thank you so much!

Anonymous said...

"Once successfully compiled, try making the mde again ans it will definitely make the mde file."

i followed these steps and it didn't work... so maybe not use the word 'definitely' ... also, don't use the word 'ans' because, well, that's not a word.

David Boyle said...

If you have a link to a code module in an accdb that doesn't compile, you need to fix that too.

Anonymous said...

Many thanks Varun :)

Anonymous said...

Thanks so much!

Anonymous said...

Thanks very much - So often the "official" Microsoft help assumes that you have already tried the most obvious. In this case I had not bothered to compile code that I wasn't using. As soon as I sorted that, the MDE was possible.