By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,705 Members | 1,852 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,705 IT Pros & Developers. It's quick & easy.

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

P: 41
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
Jul 28 '17 #1

✓ answered by PhilOfWalton

Mike,

Here is a more stable version of the Analyse Program.

Let me know progress

Phil

Share this Question
Share on Google+
23 Replies


PhilOfWalton
Expert 100+
P: 1,430
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
Jul 28 '17 #2

ADezii
Expert 5K+
P: 8,619
  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:
    Expand|Select|Wrap|Line Numbers
    1. Dim AccObj As AccessObject
    2. Dim dpdInfo As DependencyInfo
    3. Dim intCtr As Integer
    4. Dim strQueryName As String
    5.  
    6. strQueryName = "Order Details Extended"
    7.  
    8. 'Must enable the Track name AutoCorrect in order to update the
    9. 'Deependency information for all of the Objects in the Database:
    10. Application.SetOption "Track Name AutoCorrect Info", 1
    11.  
    12. Set AccObj = Application.CurrentData.AllQueries(strQueryName)
    13. Debug.Print "QUERY Name: " & strQueryName
    14. Debug.Print "---------------------------------------------------------------------------------"
    15. Debug.Print "[Fields: " & fGenerateFieldList(strQueryName) & "]"
    16.  
    17. Set dpdInfo = AccObj.GetDependencyInfo
    18.  
    19. Debug.Print " |-- Number of Dependencies : " & dpdInfo.Dependencies.Count
    20.  
    21. For intCtr = 0 To dpdInfo.Dependencies.Count - 1
    22.   Debug.Print "  |-- Depends on: " & dpdInfo.Dependencies(intCtr).FullName
    23.   Debug.Print "   |-- " & IIf(dpdInfo.Dependencies(intCtr).Type = acQuery, "Query", "Table") & _
    24.               " - " & dpdInfo.Dependencies(intCtr).DateCreated
    25. Next
    26. Debug.Print "---------------------------------------------------------------------------------"
  3. Required Function:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fGenerateFieldList(strQueryName As String) As Variant
    2. On Error Resume Next
    3. Dim qdf As DAO.QueryDef
    4. Dim intFldCtr As Integer
    5. Dim varBuild As Variant
    6.  
    7. Set qdf = CurrentDb.QueryDefs(strQueryName)
    8.  
    9. For intFldCtr = 0 To qdf.Fields.Count - 1
    10.   If InStr(qdf.SQL, qdf.Fields(intFldCtr).Name) > 0 Then
    11.     varBuild = varBuild & qdf.Fields(intFldCtr).Name & ","
    12.   End If
    13. Next
    14.  
    15. varBuild = Left(varBuild, Len(varBuild) - 1)
    16.  
    17. fGenerateFieldList = varBuild
    18. End Function
  4. OUTPUT for the Order Details Extended Query:
    Expand|Select|Wrap|Line Numbers
    1. QUERY Name: Order Details Extended
    2. ---------------------------------------------------------------------------------
    3. [Fields: ID,Quantity,Unit Price,Discount,Status ID,Extended Price,Status Name]
    4.  |-- Number of Dependencies : 2
    5.   |-- Depends on: Order Details
    6.    |-- Table - 12/5/2016 8:14:48 PM
    7.   |-- Depends on: Order Details Status
    8.    |-- Table - 12/5/2016 8:14:49 PM
    9. ---------------------------------------------------------------------------------
  5. Hope this works for you.
Jul 28 '17 #3

ADezii
Expert 5K+
P: 8,619
  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).
Jul 28 '17 #4

PhilOfWalton
Expert 100+
P: 1,430
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
Jul 28 '17 #5

ADezii
Expert 5K+
P: 8,619
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.
Attached Files
File Type: zip Depends.zip (33.3 KB, 37 views)
Jul 28 '17 #6

P: 41
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
Jul 31 '17 #7

ADezii
Expert 5K+
P: 8,619
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.
Jul 31 '17 #8

P: 41
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
Jul 31 '17 #9

PhilOfWalton
Expert 100+
P: 1,430
Mike,

Here is a more stable version of the Analyse Program.

Let me know progress

Phil
Jul 31 '17 #10

P: 41
Thanks, Phil

I will try it this Wednesday and give a feedback!

Mike
Jul 31 '17 #11

ADezii
Expert 5K+
P: 8,619
This should clarify things for you.
Attached Files
File Type: zip Depends_2.zip (35.2 KB, 30 views)
Jul 31 '17 #12

P: 41
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
Aug 2 '17 #13

PhilOfWalton
Expert 100+
P: 1,430
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
Aug 2 '17 #14

P: 41
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
Aug 2 '17 #15

PhilOfWalton
Expert 100+
P: 1,430
What do you mean by "connection" between forms? It should show any subtitles. Am out all day, but will check this evening.

Phil
Aug 2 '17 #16

P: 41
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
Aug 2 '17 #17

PhilOfWalton
Expert 100+
P: 1,430
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

Expand|Select|Wrap|Line Numbers
  1. Case -32768                                     'Form
  2.     apiSetForegroundWindow AccApp.hWndAccessApp
  3.     apiShowWindow AccApp.hWndAccessApp, SW_NORMAL
  4.     AccApp.DoCmd.OpenForm Frm!ObjectName, acDesign
  5.  
  6. Case -32764                                     'Reports
  7.     apiSetForegroundWindow AccApp.hWndAccessApp
  8.     apiShowWindow AccApp.hWndAccessApp, SW_NORMAL
  9.     AccApp.DoCmd.OpenReport Frm!ObjectName, acDesign
  10.  
Phil
Aug 2 '17 #18

ADezii
Expert 5K+
P: 8,619
@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.
Aug 2 '17 #19

P: 41
Der ADezii

Thanks a lot!!

Mike
Aug 3 '17 #20

P: 41
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
Aug 3 '17 #21

PhilOfWalton
Expert 100+
P: 1,430
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
Aug 3 '17 #22

P: 41
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
Aug 3 '17 #23

P: 41
I really want to mention that the tool of Phil is really amazing! Thank you!!!
Aug 3 '17 #24

Post your reply

Sign in to post your reply or Sign up for a free account.