Time to clean up

[Skater]

Good day all-

I have a database that has been around for about 20 years in some form or other. I would like to clean it up, remove objects that are not used anymore. Anyone have any advice to do this in a productive, non destructive manner?

Thank you

 

[Isaac]

It is fairly easy to write code that loops through all forms to see if all any objects have that query as a rowsource or recordsource - plus a ctrl+f in vba for the query name should eliminate some.

Another option - I used this when cleaning up a database that had hundreds of linked tables, but only used about 50 - to see if the table name is used in any database querydef objects.

However, I think some people have written tools that can be easily reused, too. Possibly MZtools or others, I think you can learn more and enjoy writing your own but those tools are out there.

 

[Uncle Gizmo]

One thing you could try is to create a new empty database, then copy items into the new database. Start with the main form. MS Access will tell you what's missing, tables, subform and the like. Keep doing this until you've got a new working copy of your database.

 

[Uncle Gizmo]

copy items into the new database.

In this YouTube I demonstrate how to copy items into the new database:-

 

[Isaac]

@Uncle Gizmo that's kind of a neat idea, thanks for sharing
I never thought about that

 

[Pat Hartman]

1. www.FMSINC.com has a great tool for analyzing databases called Total Access Analyzer. It identifies unused objects and certain types of errors as well as documenting the structure. Access also has some built in tools but TAA is more refined and easier to use.
2. If you are familiar enough with the objects, I like copying the objects into a new database and starting fresh. Don't forget to include your import/export specs as well as any library references you need for your code.
3. If you go with modifying the existing database, I rename objects rather than delete them. I use "zzz" as the prefix to put them to sleep

 

[gemma-the-husky]

You could spend days on this project, trying to removing redundant objects and code, without really achieving so much. To some extent it might depend how big the database is, but it might be easier just to leave it be. In any event, take a copy first, because you may well delete something and then change your mind, or even find you removed something you actually needed.

 

[Isaac]

One big reason to remove old objects is simply size. Nowadays I prefer deployment/versioning methods that 'always' copy the Server version to the Client .... and those small FE sizes are like gold to me. Especially with everyone and their brother at home on VPN ...

 

[Pat Hartman]

Keep up with the neatness as you go. When you replace code, comment it out until you're sure you no longer need it. Then remove it once the new code works. Rename objects you replace with "zzz" prefix. Remove once you know the new version is a keeper. Don't be a hoarder. If old code or objects are "valuable", document them and put them in a "tools" database if you think you will reuse the objects in another project. Keep your work product tidy.

 

[CJ_London]

If renaming, make sure you turn of autocorrect - file>options>current database - otherwise renaming objects will update any queries, forms etc that reference them automatically. So you'll be no further forward.

There is also the Database Tools>Object dependencies facility on the ribbon, although this won't pick up tables only referenced from vba or in sql subqueries for example

 

[Pat Hartman]

Thanks CJ. I forgot to mention that. I always turn off Name Auto Corrupt (Correct) and leave it off unless I actually want it to do something for me for this very reason.