Un-nesting nested MS Access sql queries

Question

I've been given a small Access database to work on. The guy who created it wrote many custom queries to generate reports. I've been tasked with modifying the reports and the guy who initially wrote the queries is gone and left no documentation.

My biggest issue is that he nested the queries 5+ levels deep and it's incredibly difficult for me to read the way it is. The queries he wrote generally have this format but are way more complex.

SELECT thisCol, thatCol, theOtherCol
FROM CustomQuery1, CustomQuery2, CustomQuery3

And CustomQuery{1,2,3} are each written the exact same way where they reference multiple other sub-queries. Not only do I find this incredibly hard to read but I worry if I modify one of the queries that perhaps it's called elsewhere in another query that I'm not aware of that will break another report. I'm wondering if there's a way to analyze all the queries to figure out which ones are called by what other queries and/or if there's some tool out there that could automatically un-nest them or if I just have to trace through them all manually.

Nathan Tuggy · Accepted Answer · 2015-01-06T00:07:02.227

3

One thing that will help you a good deal is the Object Dependencies pane, which is built into Access. Note that you'll need to turn on Name AutoCorrect while checking that, even if you have it off the rest of the time, as is usually best. Also, it won't display VBA code references to queries, so you'll have to check those yourself by searching.

edited Jan 06 '15 at 00:07

answered Jan 05 '15 at 20:19

Nathan Tuggy

2,237
27
30
38

This is pretty close to what I was hoping existed I'm really glad it's built right into access. Thanks! – Shaun Poore Jan 06 '15 at 15:27

score 0 · Answer 2 · answered Jan 05 '15 at 21:16

Yes, there's a way to determine if a query is used by another query. I created a form to do exactly this so that I could select multiple database objects and delete them simultaneously because I hated how Access only lets you natively delete 1 database object (i.e. form, table, etc...) at a time, and I wanted to make sure that the database objects I wanted to delete weren't referenced elsewhere.

Unfortunately, I can't upload the form anywhere from my work computer, they block that stuff. However, I can tell you that what you have to do is search through the QueryDef.SQL of each QueryDef in your database.

You're going to have to pick this apart a little, but this is the VBA I wrote to do it.

Private Sub ListObjects_Click()
' Search all queries for SQL containing the specified string.
Screen.MousePointer = 11
On Error GoTo Err_SearchQueries

Dim db As DAO.Database
Dim qdf As QueryDef

Dim varTest As Variant
Dim lngSearchCount As Long
Dim lngFoundCount As Long
Set db = CurrentDb

lngFoundCount = 0
lngSearchCount = 0

Me.txtTblSearch = "*** Beginning search for " & Me.ListObjects.Column(0) & "..." & vbCrLf

'Get a count of the database objects that will be searched
For Each qdf In db.QueryDefs
    With qdf
        If Left(qdf.Name, 3) = "~sq" Then
          lngSearchCount = lngSearchCount + 1
        End If
    End With
Next qdf

For Each qd In db.QueryDefs
    If InStr(1, qd.SQL, Me.ListObjects.Column(0)) > 0 Then
      If Left(qd.Name, 4) = "~sq_" Then
        If Mid(qd.Name, 5, 1) = "f" Then
          Me.txtTblSearch = txtTblSearch & "found in Form " & Right(qd.Name, Len(qd.Name) - 5) & vbCrLf
          lngFoundCount = lngFoundCount + 1
        ElseIf Mid(qd.Name, 5, 1) = "r" Then
          Me.txtTblSearch = txtTblSearch & "found in Report " & Right(qd.Name, Len(qd.Name) - 5) & vbCrLf
          lngFoundCount = lngFoundCount + 1
        ElseIf Mid(qd.Name, 5, 1) = "d" Then
          Me.txtTblSearch = txtTblSearch & "found in Report " & Right(qd.Name, Len(qd.Name) - 5) & vbCrLf
          lngFoundCount = lngFoundCount + 1
        ElseIf Mid(qd.Name, 5, 1) = "c" Then
          Me.txtTblSearch = txtTblSearch & "found in a control in Form " & Right(qd.Name, Len(qd.Name) - 5) & vbCrLf
          lngFoundCount = lngFoundCount + 1
        End If
      Else
        Me.txtTblSearch = txtTblSearch & "found in Query " & qd.Name & vbCrLf
        lngFoundCount = lngFoundCount + 1
      End If
    End If
Next qd
Set qd = Nothing
Set db = Nothing



Exit_SearchQueries:
Set qdf = Nothing
Set db = Nothing
Me.txtTblSearch = Me.txtTblSearch & vbCrLf
Me.txtTblSearch = Me.txtTblSearch & "*** Searched " & lngSearchCount & _
" objects, found " & lngFoundCount & " occurrences."
Screen.MousePointer = 0
Exit Sub

'If an error is thrown, alert the user as to which object caused it
Err_SearchQueries:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
If IsNull(qd.Name) Then
Else
  MsgBox "Possible issue with query: " & qd.Name
End If
Screen.MousePointer = 0
Resume Exit_SearchQueries


End Sub

Here's a pic of the form in action, to give you an idea:

enter image description here

Un-nesting nested MS Access sql queries

2 Answers2