• ** There has been a recent site upgrade. Please clear your browser cache to avoid issues. **
  • New forum feature - post voting and best solution

    Check out this thread for the details: https://www.access-programmers.co.uk/forums/threads/new-forum-feature-post-voting-and-best-answer.314134/

    This new feature looks great to me! :)

  • We now have 3 forum themes

    Go for the default (light) theme, Shades of Grey or Shades of Blue. I just added the Blue one.

    The thread about it is here: https://www.access-programmers.co.uk/forums/threads/new-forum-theme-shades-of-blue.314136/

VerifySQL or looking for parameterized queries. (1 Viewer)

Status
Not open for further replies.

Guus2005

AWF VIP
Local time
Today, 14:30
Joined
Jun 26, 2007
Messages
2,585
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