Pruning of Database

[Skater]

Good day all-

I have an access database that I have been using for a very long time. To use a tree analogy, it has grown many branches and a lot of them need to be pruned. Is there a best practice or tips you have to help the process of removing all the dead wood from the DB.

Any help or guidance would be greatly appreciated

Thank you

Andy

 

[moke123]

define "Dead Wood"

 

[plog]

Sounds like a better analogy is an old house that has a ton of wires you want to get rid of but don't have a schematic of what goes where. Time to build that schematic before you start yanking wires. You start with the appliances (Forms/Reports) and work backwards to the fuse box (Tables).

I find a sheet of paper is best for this and I essentially create a hierarchy. 1 by 1 open every form/report and find out its source data and any drop downs based on data. At the top of the page write down the form/report name then for every datasource it has write it below the form/report name and draw a connecting line. If it's a table, put a square around it and you are done. If a query, circle it and find out what data sources it is made of--write them below the query, connect via a line, square around tables and circle around queries. Keep going down until you hit table bedrock. Repeat for every report/form.

That sheet of paper now tells you every object you need for your database. Open a new Access database and move just the objects you need into it. Test everything and you have your pruned database.

 

[CJ_London]

Suggest create a new db then import all the objects you know are current. Make sure everything works as intended. If you find something missing during testing, import that and retest.

if you want to see what is left in the old database go to the old db and copy this sql and paste into a new query

SELECT Old.name, switch(old.type=1,"local table", old.type=6,"linked table",old.type=5,"query",old.type=-32768,"form",old.type=-32764,"report",old.type=-32761,"module",true,"n/a")
FROM MSysObjects Old LEFT JOIN (SELECT name, type
FROM [MS Access;PWD=;DATABASE=C:\path\dbname.accdb].msysobjects)  AS N ON (old.Type = N.type) AND (old.Name = N.name)
WHERE N.name Is Null and flags not in (3,-2146828288) and old.type<>8

change 'C:\path\dbname.accdb' to the path/name of your new db

 

[GPGeorge]

Using the dependencies tool will be helpful to find orphan items but I agree with CJ. Start with a new db and import a "function" at a time.

If you decide to prune rather than build from scratch, turn off Name Auto Correct. Then prefix the items you think you don't need with "zzz". If you don't turn off Name Auto Correct, you will really mess up the database as Access attempts ro propagate the "zzz" renames.

I just wanted to second the suggestion that you start by renaming items with "zzz" or something like that. It saves a lot of pain when you realize that the item in question really WAS needed, but is now deleted. Naming it back is far less stressful.

Plus, running procedures with "zzz" items will cause things to break if they are needed, and that alerts you to locations where they are used.