Do not sort out this query

[nagyf]

I have a database full of garbage.
I want to sort out the queries which are not needed.
But if I delete a query which is used
1. in another query
2. as row rource of como or list box in a form
3. as row source of a table field
4. in a module
:-(
I have to dig it out from an auld backup.

What do you recommend, how can I collect, follow and documentate, where are the queries used in other object?
Anyway, where can I read about drawing methods of dependecies of database objects?
TIA
[tt]
Ferenc Nagy
|\ /~ ~~|~~~ nagyf@alpha0.iki.kfki.hu Fax: (36-1)-392-2529 New!
| \ | | Institute of Isotope and Surface Chemistry
| \ | -+- 1525 Bp. POB 77. Tel. 36-1)-392-2550
| \| |

http://alpha0.iki.kfki.hu/~nagyf

`-' ' `-' "The goal of the life is the struggle itself"
[/tt]

 

[MichaelRed]

There was a recent post on this topic which covered the issue for tables queries. It would be a 'scratch' in hte surface of the problem, but would not come close to the issue of embedded SQL as recordsource for forms, control source, or queries generated via VBA as unnamed recordsets, or even named recordsets which are (drastically) altered.

Way back when (ver 2.0?) I did generate a complete database crossreference -at the field level- for a small project. I'm sure it no longer comes even close to working, as it referenced several of the (bygone) MSys* tables to get object lists to process, and even with that help, it took me about two weeks to get it done correctly. At that rate, it is almost cretain that a third party tool (a.k.a. Speed Ferret) would be a good investment. Being 'independent', I no longer have the luxury of such tools, but is was an excellent resource in earlier times and maintains a good reputation.

An alternative which I have used in your situation is to generate a new db, importing the UI objects, linking the tables and simply run the test suite. It will stop (error) every time it encounters a missing object. Simply go back to the "original" and retrieve the object and re-try. When the app runs properly, all of the necesary objects are in the new db. The caveats here are primarily in the modules. For general modules, you should only copy individual PROCEDURES, not whole modules, as there are often un-referenced procedures. For form modules, you need to generate a list of procedure names and check that htey are referenced within that form module and remove any which are NOT. This (last) item may cause some additional faults, but if it does, the code is poorly structured, and you can correct the issue by placing the orphaned procedure(s) in an appropiate location.

Given even the concept of the above, anything beyond a trivial app warrants the purchase of the third party tool. It is probably still under $200, which is a pittance in comparision to the cost of your time to do any of the alternatives.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[LittleSmudge]

In the query screen you could rename the query by adding a "Z" to the start of the name.

The code will then be unable to find it - but it will still list in alphabetical order.

Then run the database and test. When you find that you need a query you can just remove the "Z" and it's back.


Also, you could go into the VBA code window and do a search on each query name in turn - Make sure you select "Search ALL modules".
This will only find queries that are referenced in VBA, it won't find queries embedded in Form or Report designs.



'ope-that-elps.

G LS

 

[nagyf]

The trial and error method will be tiresome, and the test might be incomplete.
Michael, do you know athird party product which parses at least the SQL-s of the queries?
[tt]
SELECT pampam FROM ... Q1 .... JOIN Q2 ....
^ ^
These are the used lower level queries
[/tt]
WHERE pampam;
There might be hits in the SELECT and WHERE parts, e. g.
dlookup("x","Q3","y=1&quot.
Thanx
[tt]
Ferenc Nagy
|\ /~ ~~|~~~ nagyf@alpha0.iki.kfki.hu Fax: (36-1)-392-2529 New!
| \ | | Institute of Isotope and Surface Chemistry
| \ | -+- 1525 Bp. POB 77. Tel. 36-1)-392-2550
| \| |

http://alpha0.iki.kfki.hu/~nagyf

`-' ' `-' "The goal of the life is the struggle itself"
[/tt]

 

[MichaelRed]

As stated, a recent thread posted here covered the BASIC issue thread181-350415 includes a starting point - does not go into subqueries, where clauses ...

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over