Compiling an Access 2007 accdb into accde

Question

I'm trying to use the built in Make ACCDE functionality in access to make a standalone application that interfaces to an SQL-Server 2008 back end. When I run the Make ACCDE I receive an error that states that too many TableID references are being used (the limit is stated to be around 2048).

Through trial and error (deleting forms until it compiles) I've discovered which form is causing the issues. It seems to compile if I delete all the VBA code from the form.

I was wondering if anyone has run into this problem before and if there is a specific issue that causes it? I've run into this problem a few times now and it's a real pain to go through my code deleting half of it to find where the error is coming from.

Answer 1

I've occasionally seen similar errors. I'd suggest importing all your objects into a new ACCDB. Compact & Repairing only cleans up tables and indexes and doesn't do much in the way of corruption diagnosis or cleanup for the other objects.

If you do have it isolated to one particular form try doing a copy and paste on the form. This should be enough for Access to save a clean copy of the form.

Next time you encounter this though could you record the exact error message including the number and email me at tony at granite.ab.ca. I'd like to add this to my list of such errors for my corruptions web page as this appears to be an Access 2007 specific message.

Answer 2

For future coding, you should make sure you follow good coding practices:

1. in VBE options, turn off COMPILE ON DEMAND and make sure REQUIRE VARIABLE DECLARATION is turned ON (this will cause new modules to be created with OPTION EXPLICIT by default).

2. add the compile button to your toolbar in the VBE so its easily accessible. When coding, hit the compile button after every few lines of code (and SAVE after each compile). This will prevent you from writing too many uncompilable lines of code, so it will be easier to fix them to be compilable.

3. on a regular basis, make a backup of your database and decompile and recompile it. This will clean out any crud that has accumulated in the compiled p-code (decompile strips all the compiled p-code and leaves only the canonical code text) and can flush out compile errors that had previously been hidden by corrupted p-code.

By following these best practices, you will hardly ever encounter code corruption.