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.

 

[Isaac]

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

 

[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 ...

 

[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