Relationships

[Call_Me_Sam]

Hi, one of my databases is suffering under the heavy burden of queries that have been written over time BUT may not actually relate to the main purpose of the database..i.e. other members of my team have added their tuppence worth, and now it has come to redesign and i am struggling to know where to start with understanding which queries are important to the process. The queries are currently fired by numerous macros (these the analyser suggests converting to VB, which i can do) but how many queries underpin forms as well??

Has anyone heard of a tool or method that i could see how the queries relate to one another, which are important etc... There is some form of naming convention but i can see this got out of hand and would like to bring it back in line, but little steps first.

Many thanks to any suggestion

 

[neileg]

I'd make a start by
1) make more than one copy of the db to work on so you don't wreck the working version
2) In the copy, delete all the queries
3) Open each form in turn and see what error pops up
4) reinstate the query identified in the error meassage by copying from another copy of the database
5) reopen the form in case there are mode queries required

In future, use a split db so that users clutter up their own copy of the front end.

 

[Call_Me_Sam]

I'd make a start by
1) make more than one copy of the db to work on so you don't wreck the working version
2) In the copy, delete all the queries
3) Open each form in turn and see what error pops up
4) reinstate the query identified in the error meassage by copying from another copy of the database
5) reopen the form in case there are mode queries required

In future, use a split db so that users clutter up their own copy of the front end.

Cheers Neil, i am guilty of some of the rogue queries. I spent most of yesterday opening each individual query and looking at the relationships by hand (as it were). The trouble is i found that very few of the forms and controls rely on stored queries and that it is the data processing that complicates which queries are vital to the running of the system.

I wasa hoping there would be something out there that could do it visually. me and my mad ideas..

 

[The_Doc_Man]

OK, for forms/reports, there are two kinds of queries... the ones built BEFORE you built the form/report, and the ones you built with the form or report wizard. The latter always have a funny name if you examine the querydefs object by enumerating the collection that it really is. You might have to do this in VBA, but here is what I used to do for this problem.

I put together some code that would use VBA to enumerate collections. For each collection, it would look at things that could reference other things in the same database. I eventually came up with a "dependencies" list where I could easily see that a query derived from some tables and a form was derived from that query.

For the query, you can see the table/field combinations in the query.

For the form or report, you can see the source name in the Recordsource. And the names with tilde characters (~) were synthesized during Wizard use. Unless that has changed since the last time I played with that feature.

If you aren't sure about what I just suggested, look up "COLLECTIONS" in Access Help and follow some of the examples that let you elaborate all collections.

 

[Thomko]

Call_me_Sam:

I had similar problems a while ago. To fix this I wrote a tool that identifies dependencies between DB objects. Worked pretty well to identify orphaned and broken queries (e.g. if the underlying table/query has been already deleted). It also covers links from macros and forms, what seems to be your scenario. It is free and open source. Here the link if you want to try it:
http://mdb-analyzer.thomko.de/

Thomko

 

[Call_Me_Sam]

thanks Doc Man and Thomko, i'll definitely have alook at your analyser Thomko.

though toying with VBA is a great challenge as well..

many thanks