Fixing Old (and often broken) Access Databases.
My routine for fixing databases
Step By Step
I get old Access database applications all the time. Often they're broken in some way, and there are some warning signs to alert you. But if time allows, just do this periodically. These are the warning signs:
Code just doesn't work (e.g. a command button doesn't do anything), or SQL Statements in code just don't do anything.
Forms are very slow going into design mode.
Table content may be wrong (drastic, but I've seen this).
And, perhaps, performance overall slow, it seems like it's always spinning its wheels, and Frequent and inexplicable crashes, especially when closing the application
Note that if you research this, you'll find instructions that allow you to decompile and then recompile. This often works, but I've found you get this and a deeper deconstruction with the recommended procedure below. This is because Access has many levels of compilation legacy code may work (and not decompile) until you try to change it. The other issues include severe and long-forgotten major problems such as missing members.
These problems are often signs of underlying corruption in the database. Don't freak out, the following procedure has saved me many times. Follow this and you may find everything acts like new again. Here's the step-by-step procedure I follow:
First, move all objects to a new database container: Bet you never heard that term before. I hadn't, until one day in 1996 I called Microsoft Support because my Access application suddenly stopped working. That was back in the day when one could actually get a real live Microsoft employee (US Based) on the phone. At the time, I worked for AlliedSignal, Microsoft's largest customer, so they treated me well. The engineer said to move all the objects to a new database container, and quickly realized I was perplexed and explained that meant creating a new database and then importing all the objects into it. It worked like a charm, and still usually does, but time taught be some tricks that make the process go a lot smoother. Here's the order I found works best.
Open the code window (Alt + F11). Then, go to the Tools menu, pick References and take a screen shot (I love Techsmith's Snagit). Close the application and name it ...Old. Before you go, if you have a split application, note which tables are local and which are linked. Tip, name local tables tlkNameOfTable to quickly differentiate them from linked tables. Also, I like to zip up versions as I go so I can always look at an older build.
Create a new Access Database. Name it the same as the old one. Tip: Versioning (V1.1, V1.2, etc. in the file name) builds really helps down the road. Once you have a new "container", create a bogus module, open the code window, then go to Tools > References and make sure they're identical to the older version. Delete the bogus module. Do the next import steps in order.
Import the local tables (use the options section of the wizard to bring in relationships and toolbars)
Link the non-Local tables if you have a split architure.
Import all these other objects in one fell swoop: the Queries, Forms, Reports, Modules and Macros: The queries import best if they can "see" the tables when they arrive, that's why I suggest doing the tables first in a separate step.
Open The Code Window and Recompile. You'll find many references on the web suggesting you do a decompile and then recompile. That's useful, but to put it in technical terms, it's a bit of pain in the ass. This method does a complete Decompile and I think it goes deeper than just doing a Decompile/Recompile. I know because I've first done the Decompile/Recompile and then when that didn't work had to use the method outlined here. And, this method uncovered a lot more problems!
Fix the Broken Code. Almost every time I've done this, I've found code errors that had to be fixed. Recently, the most common have been the need to explicitly name the code library needed for recordsets. In the past, we could just say Dim Db as Database, and that compiled code may have worked in newer versions of Access as long as it never blasted through a true decompile. Now, you can usually just change to the default DAO (data access objects) which is native to Access. ADO can be used, but there are rumors it's being phased out. So, usually just changing to Dim Db as dao.databases and Dim RST as dao.Recordset and Dim qry1 as dao.querydef is all it takes. In fact, if there are a lot of them, I just keep hitting Debug > Decompile and copy/paste "dao." in every place it stops. Other issues can and will crop up. Missing Member errors usually occur in code behind forms where the control was deleted, but somehow the code wasn't recompiled. Any way, seek and ye shall find answers to all the debug questions.
Finally, set up the options the way you had them. Tip: Turn off autocorrect: it's buggy and causes more problems than it solves. Then, do a compact/repair and you're in business.
Hopefully, you'll find this procedure makes your application snappy and functional again. If you still have problems, just call me.
Database maintenance: often overlooked is similar to never putting oil in your automobile's engine: Pay me now or pay me later... (I worked at AlliedSignal's Fram Filter subsidiary. I didn't stay long, but did provide them with the SureGrip filter before I left and that eventually became their single best seller.