VerifySQL or looking for parameterized queries. (1 Viewer)

Status
Not open for further replies.

Guus2005

AWF VIP
Local time
Today, 05:41
Joined
Jun 26, 2007
Messages
2,645
Scenario: You have changed your database and want to make sure you haven't forgotten any queries.
An SQL statement with missing fieldnames will ask for parameters. If this was not your intent, this little piece of code will detect parameterized queries, for you to fix.
It will skip queries behind forms.

Share & Enjoy!

Code:
Public Sub VerifySQL()
'A query with missing fields asks for parameters. If this was not your intent to create
'queries with parameters, this is a function to detect queries with broken SQL statements.

    Dim db     As Database
    Dim qdf    As QueryDef
    Dim strMes As String
    
    Set db = CurrentDb
    
    strMes = "Query" & vbTab & vbTab & "Params"
    For Each qdf In db.QueryDefs
        If Left$(qdf.Name, 1) <> "~" Then 'Skip queries with a ~ prefix.
            If qdf.Parameters.Count > 0 Then
                strMes = strMes & vbCrLf & qdf.Name & vbTab & qdf.Parameters.Count
            End If
        End If
    Next qdf

    MsgBox strMes
End Sub
 
Status
Not open for further replies.

Users who are viewing this thread

Top Bottom