Tuesday, February 5, 2013

Access 2010 corrupt database "File not found"

I had the unplesant experience of a corrupt database yesterday.  After doing some work, suddenly everthing started generating a "File not Found" error.  Clicking buttons, using the ribbon bar, trying to run any macros.  My database was fucked.

I tracked it back to a module that I had added... put a little bit of code in and then deleted earlier.  When I closed and opened the database the module still showed up in the tree in the VBA editor, but seemed to be "Empty" as every time I tried to open it... it flicked up the "File not found" error.  All the other modules worked as normal.

Anyway,  after trying to delete it, over-write it, modify its name etc... I gave up and created a new database, imported everything from the old one and got moving. The steps are:

NOTE. Make a list of the "References" in the VBA editor for your database at this point. (See below for more details to avoid getting screwed when you close the corrupt database. Just write them on a piece of paper or something low tech. They cannot be exported.)

1) Create a new database
2) In the new database go to "External Data" > Access
3) Select "Import tables, Queries, forms, reports, macros, and modules into the current database.  Select the old database file using the browse button and then click the OK button.
4) You will get a mega select dialog where you can select all the objects you want to import.  Generally you will want to use the "Select All" button on each of the tabs to make sure you get "Everything" from your old database.  Make sure you do not have the "Corrupt" item selected... if you are not sure what is corrupt... take it in a couple of bites and test the new database between bites.

5) Once the import has finished and you save the new database, you may still need to "Wire up" a couple of things in the new database. (Go to File > Options > Current Database) The things I had to setup were:

Application Options Section
* Application Title
* Application Icon
* Display Form

Ribbon and Toolbar Options
* Ribbon Name

These are just the options for my "Development" database. I turn off the navigation pane and the Default ribbon before I deploy the database to the users.

Hope this helps someone.

In the Visual Basic Editor, I has to recreate all the References. I had to open the old database and make a list of everthing and then manually add the references to the new database.

Unfortunatly, when I came to try to re-open the old database it was now throwing a much larger error

"The database cannot be opened because the VBA project contained in it cannot be read. The database can be opened only if the VBA project is first deleted. Deleting the VBA project removes all code from modules, forms and reports. You should back up your database before attempting to open the database and delete the VBA project. To create a backup copy, click Cancel and then make a backup copy of your database. To open the database and delete the VBA project without creating a backup copy, click OK."  mmmm shit!

Well after making a backup...  I opened the Database and deleted the VBA project and found that the references had gone with it..... fuckcicle!

I tried going back to one of the production copies but they have been compiled and the VBA project will not show the references.... fucked again.

Don't bother trying to decompile a previous version either:

When I try this it gives me the error message "The VBA project in the datbase is corrupt".  Basically when it was compiled, it was stripped of all the symbols etc and they cannot be re-created.

Fucked again.

So my options are to "discover" the references that I need by trial and error.  I remember I had about 8-10 references, so it should not be tooooo hard.  I think the calendar control was the worst to find.

Where you have used early binding in your VBA you can discover missing references simply by running Debug > Compile Database.  This will highlight any types that you are using that have not been defined.  You can then search the net for which object library contains that type and references it correctly.

Bugger... just have to test everything in the damn database again.

No comments:

Post a Comment