Access QueryDefs Mis-save Subquery SQL

[NeoPa]

Access QueryDefs Mis-save Subquery SQL

Access stores its SQL for Subqueries in a strange manner :s It seems to replace the parentheses "()"with square brackets "[]" and (often) add an extraneous "." after it.

For instance, a simple example illustrates how it works :

SELECT subA.*
FROM [B]([/B]SELECT [fA], [fB]
      FROM [MyTable][B])[/B] AS subA

becomes (after saving as a querydef in Access) :

SELECT subA.*
FROM [B][[/B]SELECT [fA], [fB]
      FROM [MyTable][B]].[/B] AS subA

I have no idea why Access does this, but it seems to be consistent. In some versions the resultant SQL is recognised by Access but in earlier versions it fails to recognize its own SQL.

There are fundamentally two ways to access the SQL from an Access QueryDef.

1. When designing the QueryDef, select View / SQL View. This shows (and allows editing of) the SQL behind the QueryDef.
2. Programatically , a QueryDef object has an updatable string property called .SQL. This accesses the same data.

Using the former method it's often necessary to update these brackets to parentheses if intending to update the SQL directly.

In code this is more of a problem. With this in mind I developed a short routine to read the SQL of a QueryDef safely.

'GetSQL gets the SQL component from a named query OR a SQL string.
'When subqueries are specified in MS Access they are changed internally
'from   "FROM (SELECT blah blah blah) AS" to
'either "FROM [SELECT blah blah blah]. AS"
'or     "FROM [SELECT blah blah blah] AS" both of which are invalid SQL.
'This code assumes any effected subquery will start with " [SELECT ".
'This reverts SQL to correct format and loses ';' at end if requested.
' 11/11/2004    Updated to detect second problem and to use InStr
Public Function GetSQL(strQuery As String, _
                       Optional blnLoseSC As Boolean = True) As String
    Dim intDepth As Integer
    Dim lngLeft As Long, lngOpen As Long, lngRight As Long, lngAdjust As Long

    'If param passed is already SQL then leave as is - Otherwise get from query
    If Left(strQuery, 1) = "(" Then
        GetSQL = strQuery
    Else
        On Error Resume Next
        GetSQL = CurrentDb.QueryDefs(strQuery).SQL
    End If
    lngLeft = -7
    Do
        'Check for corrupted subquery
        lngLeft = InStr(lngLeft + 8, GetSQL, " [SELECT ", vbTextCompare)
        If lngLeft = 0 Then Exit Do
        'To find end correctly we must treat '[' & ']' as matched pairs
        intDepth = 1
        lngRight = lngLeft + 8
        lngOpen = -lngRight
        Do
            'Find next ']'
            lngRight = InStr(lngRight + 1, GetSQL, "]", vbBinaryCompare)
            If lngRight = 0 Then
                GetSQL = ""
                Exit Function
            End If
            intDepth = intDepth - 1
            Do
                'For lngOpen negative numbers mean that item has been counted
                'If already counted get next one - Otherwise drop through
                If lngOpen < 0 Then _
                    lngOpen = InStr(-lngOpen + 1, GetSQL, "[", vbBinaryCompare)
                'we're only interested (now) if it found one BEFORE the ']'
                If lngOpen > lngRight Or lngOpen = 0 Then Exit Do
                intDepth = intDepth + 1
                lngOpen = -lngOpen
            Loop
        Loop While intDepth > 0
        'If '].' found then be sure to drop the '.' too
        lngAdjust = IIf(Mid(GetSQL, lngRight + 1, 1) = ".", 1, 0)
        GetSQL = Left(GetSQL, lngLeft) & "(" & _
                 Mid(GetSQL, lngLeft + 2, lngRight - lngLeft - 2) & ")" & _
                 Right(GetSQL, Len(GetSQL) - (lngRight + lngAdjust))
    Loop
    'Lose ";" at end if requested and it exists
    If blnLoseSC And Right(GetSQL, 3) = ";" & vbCrLf Then _
        GetSQL = Left(GetSQL, Len(GetSQL) - 3)
End Function

Ok. Maybe not too short.

[jimatqsi]

I have been driven to madness and foul language by this habit of Access to corrupt the SQL. But I always thought it was limited to the GUI display. I never encountered the problem when accessing the SQL via code. Maybe I've just been lucky. I'm going to add this to my stock code DB. Thank you!

Jim