Redundant Queries and Tables etc

[MrChips]

I made a cardinal error ... When the specs changed half way through a job that I had spent a couple of months on, I should have restarted from scratch and pulled in tables/queries/forms and code that I needed, building on a clean base.

However, I carried on with the database and ignored stuff that was no longer needed, building new work streams alongside of what was already there. As the new spec was simpler, (It is now two databases), I now have a number of queries and tables etc that I no longer need.

I don't have the nerve to just delete bits (and then see if the database still works).

I use Access10 .... Is there any application that you know about that can interrogate the database from forms down to tables, to see what is actually needed .... thus leaving a bundle of unconnected and not-wanted stuff.

Ideally, what i would like to be able to do is to
'mark', somehow, each form and report that I know that I need, and for some sort of analyser to ferret down to see what tables, queries, subforms, reports and code etc etc. is required to support them.


Any ideas or advice would be warmly welcomed.

 

[pr2-eugin]

Hi I do not want to sound negative, but I do not think any application is better than you. Since you designed the DB, you know it much better than any application would ever know.

So what you need to do is make a backup copy; thus you will be able to revert back to the copy if there is some problem. Study the relationships, separate the tables and forms and then see which tables are not required.

One step at a time, you will be there in no time. I am saying this, because I did integrate two systems one was not even designed by me.. But carefully analysing and establishing new relationships, the system works great now.. If you did find that 'application' fine, but it is best to do it yourself, if you are stuck people are here to help.

 

[MrChips]

I guess you are right Paul.
I had just hoped that there might be something out there that could produce a hierarchical report to make things a bit easier.

 

[spikepl]

V-tools can at least help you to verify eg. where table/query names are used using the Deep Search facility.

Google v-tools

 

[MrChips]

Thanks Spike, I will have a quick look.

 

[jdraw]

If it is major functions, queries, tables and Forms that you are unsure if and where used, but you do have some testing procedures, then 1 trick that is often used is

rename one of the unsure entities eg TblInvoices to XtblInvoices and run your tests
when xTblInvoice is not found, you know that tblInvoices is used. Similar approach for the other things.

This is a long way to knowing what is used, but it is a sure way. Once you find things are required , you document same so any subsequent need to know what's needed is not a manual effort nor a guess.

 

[MrChips]

I have done this in the past ... and it does work.
I am also using the
[Database Tools] [Object Dependencies]
and recording each in a spreadsheet .... but it is such a lengthy process.
Eventually I will know what is needed and i will delete the rest ... but, no doubt, will be very thankful for the [External Data][Excel] etc facility to bring back those that I get wrong.
thank you for taking the time to assist me ... it is appreciated.

 

[speakers_86]

What ive done before is make a new shell, import known objects you need, and proceed to use the program. Each missing object will raise an error and give you the name of the missing object.

 

[jdraw]

Does your material have a lot of functions/subs and vba code?

If so you could add some code to print out the name of the function/sub and the time and get a list of the logic flow.

With some vba you can find
-record sources of forms and reports fairly quickly,
-queries involving tables....