How can I find out if a query is related to other data in my database?

[MikeMikeCH]

Der Community

I have a database with several forms, tables, queries and reports. I am actually editing the database and would like to delete some of the queries. Before I delete a query I would like to know if this query is related to another data (form, report) in the database in order not to delete a related query.

Can somebody tell me how and if I can quickly find this out?

Thanks a lot

Mike

[PhilOfWalton]

Firstly, I suggest that whenever you create a new object (and particularly a query) you either right click on the query in the Nave Pane, and under View Properties, enter what the query is for.
Alternatively with the query in design view, if you left click in the grey area, you get the query properties and you can enter the Description. (Remember to press the return key after you have entered the description, else it won't be saved).

Anyway, that's for the future.

I have a rather buggy program that will help, but will see if I can sort out the problems, and send it later.

Phil

[ADezii]

1. The Method that I use is to generate Dependency Information regarding a specific Query, or all Queries. In this Demo I use the Order Details Extended Query of the Northwind Sample Datbase.
2. Base Code:
   

   Dim AccObj As AccessObject
   Dim dpdInfo As DependencyInfo
   Dim intCtr As Integer
   Dim strQueryName As String
    
   strQueryName = "Order Details Extended"
    
   'Must enable the Track name AutoCorrect in order to update the
   'Deependency information for all of the Objects in the Database:
   Application.SetOption "Track Name AutoCorrect Info", 1
    
   Set AccObj = Application.CurrentData.AllQueries(strQueryName)
   Debug.Print "QUERY Name: " & strQueryName
   Debug.Print "---------------------------------------------------------------------------------"
   Debug.Print "[Fields: " & fGenerateFieldList(strQueryName) & "]"
    
   Set dpdInfo = AccObj.GetDependencyInfo
    
   Debug.Print " |-- Number of Dependencies : " & dpdInfo.Dependencies.Count
    
   For intCtr = 0 To dpdInfo.Dependencies.Count - 1
     Debug.Print "  |-- Depends on: " & dpdInfo.Dependencies(intCtr).FullName
     Debug.Print "   |-- " & IIf(dpdInfo.Dependencies(intCtr).Type = acQuery, "Query", "Table") & _
                 " - " & dpdInfo.Dependencies(intCtr).DateCreated
   Next
   Debug.Print "---------------------------------------------------------------------------------"

3. Required Function:
   

   Public Function fGenerateFieldList(strQueryName As String) As Variant
   On Error Resume Next
   Dim qdf As DAO.QueryDef
   Dim intFldCtr As Integer
   Dim varBuild As Variant
    
   Set qdf = CurrentDb.QueryDefs(strQueryName)
    
   For intFldCtr = 0 To qdf.Fields.Count - 1
     If InStr(qdf.SQL, qdf.Fields(intFldCtr).Name) > 0 Then
       varBuild = varBuild & qdf.Fields(intFldCtr).Name & ","
     End If
   Next
    
   varBuild = Left(varBuild, Len(varBuild) - 1)
    
   fGenerateFieldList = varBuild
   End Function

4. OUTPUT for the Order Details Extended Query:
   

   QUERY Name: Order Details Extended
   ---------------------------------------------------------------------------------
   [Fields: ID,Quantity,Unit Price,Discount,Status ID,Extended Price,Status Name]
    |-- Number of Dependencies : 2
     |-- Depends on: Order Details
      |-- Table - 12/5/2016 8:14:48 PM
     |-- Depends on: Order Details Status
      |-- Table - 12/5/2016 8:14:49 PM
   ---------------------------------------------------------------------------------

5. Hope this works for you.

[ADezii]

1. Here are two additional points that I wish to mention:
   1. The Example that I gave you displays the Object(s) that the Query depends on.
   2. In order to display the Object(s) that depend on the Query, you must use the Dependants, NOT Dependencies, Property of the AccessObject Object.
2. Hope this helps.
3. I do realize that this is a little confusing (LOL).

[PhilOfWalton]

Mike

This is passably OK.

First select the database you want to analyse (I strongly suggest a copy)

Then press "Analyse Database" (The Analyse modules has yet to be written)

Click on an item in the "Master Record Set" and this will show where the object is used, and what objects it uses.

If you double click the red items in the 2 right hand subforms, you make those the master record set.

Phil

[ADezii]

I created a Demo for you that will list both Dependent and Dependant Information for a specific Query. Simply click the Command Button, the results will be displayed in the Debug Window.

[MikeMikeCH]

Dear all

Thank you for your help. Somehow it did not work.

@Phil: I could not open my database in your tool. Only a database that was already there from you. But on this database I had no access since it is on your computer.

@ADezii: I only could push on a button called "Guess which button to click" but nothing more happened...

Mike

[ADezii]

Something did happen - as indicated in Post# 6 the results will be displayed in the Immediate Window. I will make a simple revision to the Code to make the results more obvious when I get a chance.

[MikeMikeCH]

Dear ADezii

Thanks for your work. I am really very happy about it. But I would need a sample as I do not understand the whole VBA-Code instructions that you mentionned in your earlier posts...

Mike

[PhilOfWalton]

Mike,

Here is a more stable version of the Analyse Program.

Let me know progress

Phil

[MikeMikeCH]

Thanks, Phil

I will try it this Wednesday and give a feedback!

Mike

[ADezii]

This should clarify things for you.

[MikeMikeCH]

Dear Phil and ADezii

I am really sorry, but maybe I am a bit dull. But how can I use your Files? I tried but somehow it does not work. Do I first have to open up my database and then your file or can I somehow include my database into your files?

Thank you!!!!

Mike

[PhilOfWalton]

I'll let ADezii speak for himself.

With the Analyse 2017 Db, all you should do is open Analyse 2017 Db, Open a form called "SetUpRelations". This will open up Explorer where you select the database you want to analyse.
Alternatively, use the "Select Database" Button on the "SetUpRelations" Form to select your database that you want to analyse.
You should then start seeing data in the form. from your Db.

Then press the "Analyse Database" Button to get further information.

Let me know of your progress.

Phil

[MikeMikeCH]

Dear Phil

Thank you, now it worked! The connections of my queries were shown in your database. For the connections of forms, it did not work properly, I tried this as well. But it did not show me the connections even though there were some connections.

Thanks a lot for helping me!!!

Mike

[PhilOfWalton]

What do you mean by "connection" between forms? It should show any subtitles. Am out all day, but will check this evening.

Phil

[MikeMikeCH]

Dear Phil

With connections I mean the "relationships" (objects used in... and is used in...)

With the queries it worked but with the forms it did not. As I only needed it for queries, that is not a problem for me...

Thank you!

Mike

[PhilOfWalton]

Mike. It should be working

After you press the "Analyse Database" button, do you see all the Forms & Reports in your DB being opened up.

Here are a couple of pictures. I the first I have selected the form "Investments in the left hand panel, and in the yellow panel it lists 20 subforms, combo boxes & list boxes (you need the scroll bar to see all 20)

The second image shows what happens after double clicking the SubCodes in the Yellow Panel

This shows where SubCodes is used (Green Panel - Obviously in the Investment Form, but also in the Indices Form)
It also says there is Combo Box with the Rowsource and the Subform's RecordSource.

If you double Click the Name on the Navy Master object panel, you see the object in design view (it is not working for forms on your version as there are 2 lines of code missing, but it should work for reports & queries)

If you want, in the Module "ZZSetUpRElations", In the Function "ShowObject", add the missing lines immediately below Case -32768

It should look like this

Case -32768                                     'Form
    apiSetForegroundWindow AccApp.hWndAccessApp
    apiShowWindow AccApp.hWndAccessApp, SW_NORMAL
    AccApp.DoCmd.OpenForm Frm!ObjectName, acDesign
 
Case -32764                                     'Reports
    apiSetForegroundWindow AccApp.hWndAccessApp
    apiShowWindow AccApp.hWndAccessApp, SW_NORMAL
    AccApp.DoCmd.OpenReport Frm!ObjectName, acDesign
 

Phil

[ADezii]

@MikeMikeCH:
Didn't want you to think that I left you stranded. Phil's example and Attachment is much, much better than the limited solution that I came up with. Good Luck with your Project.

[MikeMikeCH]

Der ADezii

Thanks a lot!!

Mike

[MikeMikeCH]

Dear Phil

Thanks for your further information. On my computer I can Analyse the database and find my forms, queries and tables.

It only shows me the relationships (Objects used in... / Is used in...) for tables and queries but not for forms. When I chose a form, the whole yellow and green part stays clear although the forms are used in other objects or at least use other objects... This is ok for me because I do not need it for my forms.

If I can give you further information about it, please let me know!

Thanks a lot!!!!!!!!

Mike

[PhilOfWalton]

Very Odd.

The only way I have got it to fail is if there is something the matter with the database being analysed (e.g. a compile error or missing table etc)

There should be 2 Access windows open and it is worth checking there is no error message in the database being analysed. Also, move the database being analysed around and see if you can see the rainbow coloured progress meter in the "Analyse 2017" database.

Phil

[MikeMikeCH]

Dear Phil

Thank you for your answer. I found out the solution. I had to rechose the database and again analyse it. Then it worked :-)

Thank you so much!

Mike

[MikeMikeCH]

I really want to mention that the tool of Phil is really amazing! Thank you!!!