Finding unused Queries

[Lou_Reed]

I would like to check my db for unused queries. I made a lot of queries and I know some are just hanging out there being of no use at all.



I found online a VBA program that might help:

Public Sub CheckQueries(ByVal str as String)

Dim qu As QueryDef

For Each qu In CurrentDb.QueryDefs
      If InStr(qu.SQL, str) > 0 Then
          Debug.Print qu.Name
      End If
Next

End Sub

The question is will this code work. If it will work, then where do I place it in the already existing db?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[ranman256]

dont.
an extra query lying around wont hurt anything and doesnt take up any room.
deleting a query that IS needed , does hurt.

[CJ_London]

The question is will this code work

yes it will work. The bigger question is 'will it work the way I want'. And I don't know the answer to that because I don't know what you want it to do. 'I would like to check my db for unused queries' is not a sufficiently detailed requirement

where do I place it in the already existing db

in a new or existing module

You can use the object dependencies function on the developer tab to find out where the query is used

[Micron]

I've read on MSDN that Access doesn't have a developer tab, but maybe that's outdated info. Can't find it in 2007 and have never seen it, but don't know why you'd need one either. However, you're using 2010, so I'm curious as to what's on that tab that you can use that I can't?

[Lou_Reed]

I will look and get back to you. Please be more specific.

Respectfully,

Lou Reed

[CJ_London]

I've read on MSDN that Access doesn't have a developer tab,

my bad. typing from memory. In access it is called Database Tools

[orange]

Lou,

How do you know this? Do you know, or do you suspect?

I know some are just hanging out there being of no use at all.

[John_G]

Lou -

That code will not do what you want, and I dont think that is what is is intended to do. I looks like it is supposed to find queries that are used as part of other queries, but it won't do that reliably either. Don't bother with it.

Even if that code was written properly, it will not find queries that are used or referenced in VBA code.

[ssanfu]

You might look at the "RubberDuck" add in at http://rubberduckvba.com
Click on "Learn More" under Code Inspections

I just installed it 2 days ago and haven't had a lot of time to do much testing, but it does look promising.
Project is being hosted on github.com.

[orange]

Steve,
Did you learn about rubber duck through the Steve Bishop videos?
I'm interested in your review/usage.

[ssanfu]

@orange, Actually, it was mentioned in an article in databasejournal.com. I was looking for articles on how to connect Access forms/reports using views/SPs.....
Haven't had time to try it out yet.

[orange]

Steve,

https://www.youtube.com/watch?v=RetQsyKkHlo
and
https://www.youtube.com/watch?v=sKUYfiWSFLY