Find where queries used? 1

[VicM]

I've inherited a DB to debug and take to completion. After reviewing the design, I've come to the conclusion that much of the functionality could have been more simply coded.

However, without having to go thru every form, subform, report, subreport and code window, is there a utility in Access which reveals where queries are being used? I thought the analyzer might provide the information, but it doesn't appear to.

Thanks,

Vic

 

[SkipVought]

Hi,

Take a look at the Access Object Model.

Skip,

_{[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it!}

 

[VicM]

Skip,

I'm somewhat familiar with the Object Model, but I'm not sure how this can help me discover where a query may be used; whether it be in a record source, row source, or some event of form, report, or control on either.

Are you suggesting I enumerate thru the collections and target those properties? That would seem to me almost as time consuming in creating the routine as it would to just go thru everything manually.

I was hoping there was some system table or a utility that kept track of where the queries were used.

Thanks,

Vic

 

[RoyVidar]

Get hold of the 2003 version (just for this test), then check out the Object Dependencies thingie (available for instance on right click of a query object) - else, do a search here, and you'll find this is a recurring issue, whith no real good answers, except probably lot of work;-)

See - even if there are tables which may give you something (hint MSysObjects, MSysQueries ... I think the member FancyPrairie posted a version some time ago, try a search on those two table names in the Access fora) then there's all the other places where you're using the stuff - dynamic SQL in code, dynamic assigning of recordsources of forms/reports, rowsources of combos ...

Here's another method (also involving a bit of work), where you dump all your objects to searchable textfiles, then use your favourite text search tool (or write som VBA to do it?) thread705-834817.

Roy-Vidar

 

[BoxHead]

Vic,

Don't know if this is helpful or not, but the following code will iterate through the queryDefs collection and append the query name and sql to a table (tblQuerySql).

I created the table with its two fields (qryName & qrySql) as a text field and a memo field respectively.

The code is run on a command button.

Private Sub C1_Click()
Dim dbs As DAO.Database, rst As DAO.QueryDef
Set dbs = CurrentDb
DoCmd.SetWarnings False                         [COLOR=green]'Turn warnings off[/color]
DoCmd.RunSQL "DELETE * FROM tblQuerySql;"       [COLOR=green]'Clear existing data from table[/color]

For intI = 0 To dbs.QueryDefs.Count - 1
Set rst = dbs.QueryDefs(intI)                   [COLOR=green]'Find the first queryDef in the collection[/color]
DoCmd.RunSQL "INSERT INTO tblQuerySql (qryName,qrySQL) SELECT '" & rst.Name & "', '" & rst.SQL & "';"  [COLOR=green]'Append to table[/color]
Next intI                                       [COLOR=green]'Move to the next queryDef[/color]
DoCmd.SetWarnings True                          [COLOR=green]'Turn warnings back on[/color]

End Sub

The queries having names beginning with '~' refer to the control source or record source of forms, reports, combos.

HTH


John

Use what you have,
Learn what you can,
Create what you need.