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 :
Expand|Select|Wrap|Line Numbers
- SELECT subA.*
- FROM (SELECT [fA], [fB]
- FROM [MyTable]) AS subA
Expand|Select|Wrap|Line Numbers
- SELECT subA.*
- FROM [SELECT [fA], [fB]
- FROM [MyTable]]. AS subA
There are fundamentally two ways to access the SQL from an Access QueryDef.
- When designing the QueryDef, select View / SQL View. This shows (and allows editing of) the SQL behind the QueryDef.
- Programatically, a QueryDef object has an updatable string property called .SQL. This accesses the same data.
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.
Expand|Select|Wrap|Line Numbers
- '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