Many unused Queries..How Do I find them? 1

[chaft]

Hi

I made loads of queries through out my project to try stuff out and a lot of the time they didn't work. I now have loads of queries, but I can't work out which are being used by a report or form and which were from my trials.

Any ideas of how I get rid of them without deleting something useful?

thanks

 

[PHV]

Have a look here:

http://mdbdoc.sourceforge.net/

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MichaelRed]

I like to use the MSysQueries linked to MSysObjects to find references to queries and tables. Of course this doesn't find queries embedded in cose (or their references).



MichaelRed

 

[chaft]

Now I'm inundated with Information...

What exactly should I be looking for to help me reduce my queries? I'm no expert and I don't want to delete any by mistake.

If any of you could suggest a strategy I'd Appreciate it.

 

[chaft]

no wait! I think I get it now... the recordsource for the reports and forms should identify the queries I'm using and as I'm not knowledgable enough to use VB driven SQL I shouldn't lose any queries.

I'm going to give it a try at least. Let you know if I succeed.

 

[MajP]

1) Queries do not take up a lot of resources so I would not get wrapped up around it
2)More important is to use a good naming convention to manage your queries. I use something like

qryDelSomeName (a delete query)
qryUpdSomeName (update query)
qryAppdSomeName (append query)
qryFrmSomeFormName (query used for a form)
qrySomeName (general select qry)
qryCalcSomeName (qry used for calculation)

This way I can look at the db window and know pretty much what the query will do

3) You could run this code to see the recordsource of all forms and reports

Public Function getRecordsource() As String
  Dim frm As AccessObject
  Dim rpt As AccessObject
  getRecordsource = "Forms" & vbCrLf & vbCrLf
  For Each frm In CurrentProject.AllForms
    DoCmd.OpenForm frm.Name, acDesign, , , , acHidden
    If Not Forms(frm.Name).RecordSource = "" Then
      getRecordsource = getRecordsource & frm.Name & vbCrLf & "Recordsource:" & Forms(frm.Name).RecordSource & vbCrLf & vbCrLf
    End If
    DoCmd.Close acForm, frm.Name
 Next frm
 getRecordsource = getRecordsource & "Reports" & vbCrLf & vbCrLf
 For Each rpt In CurrentProject.AllReports
    DoCmd.OpenReport rpt.Name, acViewDesign, , , acHidden
    If Not Reports(rpt.Name).RecordSource = "" Then
      getRecordsource = getRecordsource & rpt.Name & vbCrLf & "Recordsource:" & Reports(rpt.Name).RecordSource & vbCrLf & vbCrLf
    End If
    DoCmd.Close acReport, rpt.Name
 Next rpt
End Function

may inundate you a little less information
4) Remember if qryA is built on qryB and qryC you may not see that

5)the recordsource for the reports and forms should identify the queries ...
If I understand you, yes you can build the recordsource directly in the form or report. This makes it a little easier to manage, but it has a drawback. If the recordsource is defined in the form or report it does not have a chance to be optimized like a stored query. A stored query will run faster. Depending on the complexity of you database, you may never notice the difference.

 

[chaft]

gah...it doesn't show which form controls rely on which query.
It only shows Reports and Form query dependencies and not their individual controls

 

[MichaelRed]

I like to use the MSysQueries linked to MSysObjects to find references to queries and tables. Of course this doesn't find queries embedded in cose (or their references).



MichaelRed

 

[MajP]

I assume you are referring to row sources of list boxes and text boxes.

Public Function getRecordsource() As String
  Dim frm As AccessObject
  Dim rpt As AccessObject
  Dim ctl As Access.Control
  getRecordsource = "Forms" & vbCrLf & vbCrLf
  For Each frm In CurrentProject.AllForms
    DoCmd.OpenForm frm.Name, acDesign, , , , acHidden
    getRecordsource = getRecordsource & frm.Name & vbCrLf & "Recordsource:" & Forms(frm.Name).RecordSource & vbCrLf
    For Each ctl In Forms(frm.Name).Controls
      If ctl.ControlType = acListBox Or ctl.ControlType = acComboBox Then
          getRecordsource = getRecordsource & ctl.Name & " RowSource: " & ctl.RowSource
      End If
    Next ctl
    getRecordsource = getRecordsource & vbCrLf
    DoCmd.Close acForm, frm.Name
 Next frm
 getRecordsource = getRecordsource & "Reports" & vbCrLf & vbCrLf
 For Each rpt In CurrentProject.AllReports
    DoCmd.OpenReport rpt.Name, acViewDesign, , , acHidden
    getRecordsource = getRecordsource & rpt.Name & vbCrLf & "Recordsource:" & Reports(rpt.Name).RecordSource & vbCrLf
    For Each ctl In Reports(rpt.Name).Controls
      If ctl.ControlType = acListBox Or ctl.ControlType = acComboBox Then
          getRecordsource = getRecordsource & ctl.Name & " RowSource: " & ctl.RowSource
      End If
    Next ctl
    getRecordsource = getRecordsource & vbCrLf
  DoCmd.Close acReport, rpt.Name
 Next rpt
End Function

 

[Remou]

I generally find listening to MichaelRed helps me a lot.

 

[MajP]

Remou and Michael,
Gentlemen, could you post some Sql showing how you would use the system tables to return some information? I can not figure out a way to query these tables, after playing with them a little more. I think there has to be a few tricks and tips. As far as I can tell these are the relevant fields

SELECT MSysObjects.Name, MSysQueries.Expression, MSysQueries.Name1, MSysQueries.Name2, MSysQueries.Attribute
FROM MSysObjects INNER JOIN MSysQueries ON MSysObjects.Id = MSysQueries.ObjectId;

This gives me a lot of information about the queries.
filtering on the attributes 1-11,255 gives you properties of the queries. I think I figured out the following
6 - calculated field information
7 - join information
8 - where information

I can not figure out any relationship to show dependancies on queries. Can you post SQL for the following if it is possible?
1)Queries related to forms and reports or controls
2)Queries not used in the database

 

[MichaelRed]

I do not have the time to go into this in detail. Somewhere (or several somewheres?) in the archives, there is a fair degree of additional information on the various additional attributes, but you have the essence of that part.

In addition, look closely at the expressions where they start with the tilde ("~") the expression also tells you whether the expression is embedded in a form, report ... and even the specific top level object and control on the object. These are reasonably obvious with scant inspection.

So, this information, although somewhat bulky, shows the usage of all stored queries in the application.

Of course the whats missing can also be important depending on the way the app is written. Any 'embedded' SQL statements are missed. References to queries and querydefs which are through code are not enumerated ...

For the exceptions, you need to parse the code for specific terms such as "strSQL", "sSQL", "QueryDef", "RunSQL" and a host of others which may (or may not) be used.

In the end, the use ad interpretation of the system tables will remove the majority of the unused QUERIES, but you will need to also look for additional opportunities to remove dead objects and code associated with these - then revisit the unused queries,

The overall process is unending. And necessary only for relatively large applications. An anther process, at least for MS Access apps is to generate and use a process which minimizes the growth of the dead objects and code. I prefer to work on Ms A, Apps with a three tier structure:

A production system. Includes ONLY proven (e.g. tested) objects and procedures. These are created from a previous Production version, with selected objects from an approved test version.

Test versions are created by copying the current Production version to a new test case and copying only the necessary objects from a development version to accomplish the desired change(s) and deleting any "Obsolete" objects from it. Users are then required to test (exercise) the test case until it meets the stated objectives with out side effects.

A but elaborate, but then I deal with a number of databases where the FE exceeds 50MB, so growth is a B A D thing!!



MichaelRed