Stuart McCall's Microsoft Access Pages - Queries

Home

Determine if Query exists in current Database

Note that this code requires a reference to "Microsoft DAO 3.x", where x is the highest value on your system. 
Public Function IsQuery(QueryName As String) As Boolean
    Dim qdf As DAO.QueryDef
    
    For Each qdf In DBEngine(0)(0).QueryDefs
        If qdf.Name = QueryName Then
            IsQuery = True
            Exit For
        End If
    Next
End Function
Paste this Code into a standard module.

Usage:

Debug.Print IsQuery("MyQuery")
Home Contents

Obtain a named section from an Sql SELECT statement

Public Enum SelectPart
    spSelect = 1
    spFrom = 2
    spWhere = 3
    spOrderBy = 4
End Enum

Paste these Declarations into the top of a standard module (ie before any procedures). 
Public Function SqlSelectPart(ByVal sql$, Item As SelectPart, Optional StripKeyWords As Boolean) As String
    Dim s$, i&, j&
    
    If sql = "" Then Exit Function
    s = RTrim$(Replace(sql, vbCrLf, " "))     'In case sql is from a QueryDef
    If Right$(s, 1) <> ";" Then s = s & ";"   'Ensure sql is terminated
    '
    Find$ = Choose(Item, "SELECT ", "FROM ", "WHERE ", "ORDER BY ")
    i = InStr(1, s, Find$)
    If i = 0 Then Exit Function
    '
    Select Case Item
        Case spSelect
            j = InStr(1, s, "FROM ") - 1
            If StripKeyWords Then i = i + 7
        Case spFrom
            j = InStr(1, s, "WHERE ") - 1
            If j < 0 Then
                j = InStr(1, s, "ORDER BY ") - 1
            End If
            If StripKeyWords Then i = i + 5
        Case spWhere
            j = InStr(1, s, "ORDER BY ") - 1
            If StripKeyWords Then i = i + 6
        Case spOrderBy
            j = Len(s)
            If StripKeyWords Then i = i + 9
    End Select
    If j < 0 Then j = Len(s)
    '
    SqlSelectPart = LTrim$(Mid$(s, i, j - i))
End Function
Paste this Code into the same module.
Home Contents