Parse Queries

[Guus2005]

Hi,

After changing the database design i want to parse all queries to see if i have forgotten to update a query to the new design.
Is there a way to accomplish that using code?

Thx!
Guus

 

[Guus2005]

This is a quick and dirty method of accomplishing what i want:

Public Sub ParseQueries()
    On Error GoTo Err_ParseQueries
'  0 is select
' 16 is crosstab
' 48 is update
' 64 is append
' 80 is ddl (create table)
' 96 is ddl (drop table)
'128 is union

    Dim dbs As Database
    Dim qdf As QueryDef
    Dim rst As Recordset
    
    Set dbs = CurrentDb
    
    For Each qdf In dbs.QueryDefs
        If Left$(qdf.Name, 1) = "~" Then
        Else
            With qdf
    '            Debug.Print .Name & "; " & .Type
                Select Case .Type
                Case 0, 16, 128 ' Select, Crosstab, Union
                    Set rst = .OpenRecordset(dbOpenDynaset, dbSeeChanges)
                    Set rst = Nothing
                Case 32, 48, 64 ' dml (data manipulation)
    '                Debug.Print "Skip : " & qdf.Name
                Case 80, 96     ' ddl (data definition)
    '                Debug.Print "Skip : " & qdf.Name
                Case Else
                    Stop
                End Select
            End With
        End If
    Next qdf

Exit_ParseQueries:
    MsgBox "Done"
    On Error GoTo 0
    Exit Sub

Err_ParseQueries:
    Debug.Print "Error: " & qdf.Name & " code: " & Err & " Description: " & Err.Description
    Resume Next
Resume 'For debugging purposes
End Sub

I am using dbSeeChanges because the tables are SQL Server tables.
The only queries i can check are select queries and not any queries behind controls and forms. That's why i am skipping them. Their name start with a tilde ~.

If you have a better solution please let me know.

Thx!
Come to think of it, perhaps this question was a VBA question and not a query question. Sorry about that!

 

[isladogs]

Have a look at this utility that allows you to view all database objects.
For queries you can view the design or run them

https://www.access-programmers.co.uk/forums/showthread.php?t=295597

You will still need to view each but it might make the job faster

How did you hope to check them using code? By looking for Expr1 type fields?

 

[Guus2005]

Hi Ridders,

The code shown in my second post shows that you can check the execution of each select query. Very fast. Since i have well over a hundred queries it came up with about 20 queries which needed to be changed to accomodate the database change.
It is a lot faster than checking each sql statement visually.
What i was looking for was a parse functionality which not only checks the select query but *all* queries. Like a hidden feature.

There are no Expr1 type of fields, not if you didn't open and save them in the query design window.

But thanks for your input.

 

[isladogs]

I looked at that code but couldn't see how that helped.

It opens and then closes each select, crosstab, union query in turn and gives the query name if an error occurs. How will that catch errors where field names have changed as these would automatically just become expr1; expr2 etc

Also in case you aren't aware, if you have any hidden queries, these have different .type values. Just add 8 to the normal type value
So 8 for hidden select, 24 for hidden crosstab, 136 for hidden union etc

 

[Pat Hartman]

You can open the querydefs collection and search the SQL string.

 

[Guus2005]

Hi ridders,
as you can see i have put a stop command in the select case statement in case i forgot one. and i didn't. obviously i don't use hidden queries.

Thanks Pat for your advice.
Some time ago i wrote SearchInQueryDefs which i can use to search through all queries. I just have to search for the changed column names.
Why didn't i think of that?

Thanks both!

 

[isladogs]

Hi Guus

Reading your original code, I assumed you didn't know what might be broken.
In other words, I didn't suggest find & replace as it sounded like you weren't sure what to look for.

If you go down that route, you will of course also need to check the record source of all forms & reports together with all module code.
The free V-Tools add in works very well for this (except it doesn't do replace in macros).
I have my own home-grown search & replace tool which also handles macros and which at some point I will upload to the forum

Another method is to use the built in dependency checker - Database Tools ... Object Dependencies. However this doesn't check module code