VerifySQL or looking for parameterized queries. (1 Viewer)


Jun 26, 2007
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!

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

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom