best way to "clean up" an access app?

[pduncan]

Is there a module or function that will tell me if a query is used (or not used) by an application? I have several queries that I think I can delete, but not 100 % sure.
I did document this app fairly well (I thought) but still have 40 or so queries in the window. I can only account for about 15 of them.

When developing an access app, I generally tend to make a query, test it, then grab the SQL and use it via code. I try to make an effort to delete the query but I guess I got lazy on a few of these.

- Or –

should I leave well enough alone? Nobody but myself will be looking past the forms, therefor any orphans are really just taking up space (but realistically that can’t be much - can it?) I am leaning to this more non-evasive approach, but I know that 2 or 3 years from now, something will happen and I will drive myself nuts tracking all those orphans down.

Any ideas or suggesations? How do you handle situations like this?


Thanks, PDUNCAN

 

[afryer]

Hi,

As far as I know there is no easy way of detecing whether or not a query is in use. If the queries are called from within code in your application then the easiest way I can think of is to cut each query that you are not sure about and then try and compile your program. If the query is referenced and cannot be found then a compile error will occur.

With regards to leaving them alone, in my opinion (and speaking from personal experience) these things always come back to haunt you and if you can remove them now then you will have less problems debugging later.

Hope this helps

Andrew

 

[Savil]

What i do is just rename the suspect queries by appending a number one one the end and see if the db falls over over a few days. If it does then you can rename them back again.

 

[Crowley16]

I remember a similar thread a while ago about creating something that can tell if a object is being called by any other object in the database...

The end result was that although it is possible, it's far too much hassle for it to be ever worth while...

--------------------
Procrastinate Now!

 

[oharab]

If you have the capital ($199.00) then Speed Ferret may be helpful. This will search (& replace if you want) through everything in your database, tables, queries, forms, code, etc looking for your search string.
Whatever you do with them, and I'm only mentioning it as a reminder, I'm sure you would anyway, make sure you have a backup of the original before you go deleting things!

hth

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"

NoFX-The Decline
----------------------------------------------
Want to get great answers to your Tek-Tips questions? Have a look at F

 

[MichaelRed]

Finding queries which are 'directly' referenced by other objects is not so difficult. There was a recent thread using MSysObjects (hidden system table) which does this nicely and includes (direct) reeferences from controls (control source property).

Finding where there is a full named reference in code is also straightforward, involving only itterating through the querydefs collection and doing a search for the name property of each querydef throughout the code.

What is a bit messy for a few (un-refenerate hard core CODE users) is finding queries which use a naming convention to set up for a serial execution. These often have a numeric character placed strategically (at the end?) and they (the names) are placed in a loop which replaces the numeric with the loop index and executes the query:

Public Function basReportData()

    Dim Idx As Integer
    Dim qryDef As DAO.QueryDef

    While Idx <= 8
        Set qdf = "qryReportData" & Trim(str(Idx))
        QueryDef.Execute qdf

        Idx = Idx + 1
    Wend

End Function
[code]

Of course I have long ago abndonded this practice:

[tab]Firstly, I no longer work in an environment where it is necessary.

[tab]Secondly, I have realized the pitfalls of being to clever (same problem, find out where the )*&^(*&@#^(*!@& things are used / referenced.

[tab]Thirdly, I no longer ply this ttrade commercially, so such exercises are beyond  the realm of even useful.





MichaelRed

 

[Erik8]

If you use Office 2003, you simply right mouse click on the name of the object and select the last item, "Object Dependencies..."

You then have the option to view a tree of either "Objects that depend on me" (default) or "Objects that I depend on."

In the tree, you can drill down to all generations of child dependencies of the direct dependencies, regardless of the object types.

- Erik

 

[Darrylles]

Savil,

Don't you find that the object that uses the 'renamed' query updates it's reference to it also?

e.g. FormA recordsource = "MyQuery". Rename "MyQuery" to "MyQuery_temp".

Look at the form recordsource value, does it not now = "MyQuery_temp"?

Regards,

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk