By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,223 Members | 1,392 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Access QueryDefs Mis-save Subquery SQL

NeoPa
Expert Mod 15k+
P: 31,767
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT subA.*
  2. FROM (SELECT [fA], [fB]
  3.       FROM [MyTable]) AS subA
becomes (after saving as a querydef in Access) :
Expand|Select|Wrap|Line Numbers
  1. SELECT subA.*
  2. FROM [SELECT [fA], [fB]
  3.       FROM [MyTable]]. 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.
Expand|Select|Wrap|Line Numbers
  1. 'GetSQL gets the SQL component from a named query OR a SQL string.
  2. 'When subqueries are specified in MS Access they are changed internally
  3. 'from   "FROM (SELECT blah blah blah) AS" to
  4. 'either "FROM [SELECT blah blah blah]. AS"
  5. 'or     "FROM [SELECT blah blah blah] AS" both of which are invalid SQL.
  6. 'This code assumes any effected subquery will start with " [SELECT ".
  7. 'This reverts SQL to correct format and loses ';' at end if requested.
  8. ' 11/11/2004    Updated to detect second problem and to use InStr
  9. Public Function GetSQL(strQuery As String, _
  10.                        Optional blnLoseSC As Boolean = True) As String
  11.     Dim intDepth As Integer
  12.     Dim lngLeft As Long, lngOpen As Long, lngRight As Long, lngAdjust As Long
  13.  
  14.     'If param passed is already SQL then leave as is - Otherwise get from query
  15.     If Left(strQuery, 1) = "(" Then
  16.         GetSQL = strQuery
  17.     Else
  18.         On Error Resume Next
  19.         GetSQL = CurrentDb.QueryDefs(strQuery).SQL
  20.     End If
  21.     lngLeft = -7
  22.     Do
  23.         'Check for corrupted subquery
  24.         lngLeft = InStr(lngLeft + 8, GetSQL, " [SELECT ", vbTextCompare)
  25.         If lngLeft = 0 Then Exit Do
  26.         'To find end correctly we must treat '[' & ']' as matched pairs
  27.         intDepth = 1
  28.         lngRight = lngLeft + 8
  29.         lngOpen = -lngRight
  30.         Do
  31.             'Find next ']'
  32.             lngRight = InStr(lngRight + 1, GetSQL, "]", vbBinaryCompare)
  33.             If lngRight = 0 Then
  34.                 GetSQL = ""
  35.                 Exit Function
  36.             End If
  37.             intDepth = intDepth - 1
  38.             Do
  39.                 'For lngOpen negative numbers mean that item has been counted
  40.                 'If already counted get next one - Otherwise drop through
  41.                 If lngOpen < 0 Then _
  42.                     lngOpen = InStr(-lngOpen + 1, GetSQL, "[", vbBinaryCompare)
  43.                 'we're only interested (now) if it found one BEFORE the ']'
  44.                 If lngOpen > lngRight Or lngOpen = 0 Then Exit Do
  45.                 intDepth = intDepth + 1
  46.                 lngOpen = -lngOpen
  47.             Loop
  48.         Loop While intDepth > 0
  49.         'If '].' found then be sure to drop the '.' too
  50.         lngAdjust = IIf(Mid(GetSQL, lngRight + 1, 1) = ".", 1, 0)
  51.         GetSQL = Left(GetSQL, lngLeft) & "(" & _
  52.                  Mid(GetSQL, lngLeft + 2, lngRight - lngLeft - 2) & ")" & _
  53.                  Right(GetSQL, Len(GetSQL) - (lngRight + lngAdjust))
  54.     Loop
  55.     'Lose ";" at end if requested and it exists
  56.     If blnLoseSC And Right(GetSQL, 3) = ";" & vbCrLf Then _
  57.         GetSQL = Left(GetSQL, Len(GetSQL) - 3)
  58. End Function
Ok. Maybe not too short.
Apr 18 '08 #1
Share this Article
Share on Google+
1 Comment


Expert 100+
P: 1,240
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
Aug 16 '14 #2