By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,849 Members | 2,361 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,849 IT Pros & Developers. It's quick & easy.

Query SQL code

P: 48
Is there a way (function) to extract dynamically the SQL-code out of a query ?

I have to write some very complex queries, what I would like to do in the query-designer, but run them afterwards embedded in VBA.

I thought it should be possible, at runtime with a function to
- get the SQL-code out of the query
- clean it up / format it
- runsql it

Thanks
Jan 29 '08 #1
Share this Question
Share on Google+
11 Replies


JustJim
Expert 100+
P: 407
Is there a way (function) to extract dynamically the SQL-code out of a query ?

I have to write some very complex queries, what I would like to do in the query-designer, but run them afterwards embedded in VBA.

I thought it should be possible, at runtime with a function to
- get the SQL-code out of the query
- clean it up / format it
- runsql it

Thanks
Hi,

Go ahead and design your query in query-designer, fiddle with it to get it exactly right, save it to get it optimized (if you wish), re-open it, go to SQL View, Copy the SQL and paste it into your VBA as a string and runqsl the string!

Jim
Jan 30 '08 #2

P: 48
Hi,

Go ahead and design your query in query-designer, fiddle with it to get it exactly right, save it to get it optimized (if you wish), re-open it, go to SQL View, Copy the SQL and paste it into your VBA as a string and runqsl the string!

Jim
Hi, I know that, but thought that it might be possible to get the SQL out of the query at runtime. Especially because I understood that the SQL-code has to be cleaned (removing Carriage-return a.s.o). Isn't that the case ?
Can it been run as-is ?

Thanks
Jan 30 '08 #3

JustJim
Expert 100+
P: 407
Hi, I know that, but thought that it might be possible to get the SQL out of the query at runtime. Especially because I understood that the SQL-code has to be cleaned (removing Carriage-return a.s.o). Isn't that the case ?
Can it been run as-is ?

Thanks
Very probably, since the query is an Object, but I'm sorry I don't know how.

Jim
Jan 31 '08 #4

100+
P: 365
When i create sql i use the builder and as you probly know you get it (in sql view) as

SELECT ...........
FROM .............
WHERE............
etc

and if you where to put that in a string you would do it like this i spose

mysql = "SELECT....." & _
"FROM............." & _
"WHERE..........."

i usually just put it all in one lineand dont even bother giving it a string...

docmd.runsql "SELECT......... FROM .......... WHERE............"

Does that answer you question!?
Jan 31 '08 #5

NeoPa
Expert Mod 15k+
P: 31,489
Hi, I know that, but thought that it might be possible to get the SQL out of the query at runtime. Especially because I understood that the SQL-code has to be cleaned (removing Carriage-return a.s.o). Isn't that the case ?
Can it been run as-is ?

Thanks
The QueryDef has a .SQL property which is the SQL string.
Is that what you're after?
Feb 2 '08 #6

NeoPa
Expert Mod 15k+
P: 31,489
Sometimes (as in the explanation part of this procedure) Access (the older versions at least, pre-2003) Access can create incorrect SQL which even it can't process properly. The following little routine will get the SQL out of a QueryDef for you and fix any Access errors it finds. You can simply cut and paste the code into your project if you like.
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
Feb 2 '08 #7

JustJim
Expert 100+
P: 407
Sometimes (as in the explanation part of this procedure) Access (the older versions at least, pre-2003) Access can create incorrect SQL which even it can't process properly. The following little routine will get the SQL out of a QueryDef for you and fix any Access errors it finds. You can simply cut and paste the code into your project if you like.
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
Now that's pretty darn clever!

Jim
Feb 3 '08 #8

NeoPa
Expert Mod 15k+
P: 31,489
Thanks Jim.
I had to develop it at work as the Access 2000 SQL which was created for any QueryDef with sub-queries in always failed when I tried to execute it :(

It's probably less clever than it looks. The VBA libraries don't provide anything for finding matching brackets (or quotes or anything really) so I just had to code it in. That's where all the complicated looking code is.

Clever would be to code all that in a couple of lines. Unfortunately I don't think VBA lends itself well to that. It doesn't have the right building blocks (At least not that I found).
Feb 3 '08 #9

JustJim
Expert 100+
P: 407
Thanks Jim.
I had to develop it at work as the Access 2000 SQL which was created for any QueryDef with sub-queries in always failed when I tried to execute it :(

It's probably less clever than it looks. The VBA libraries don't provide anything for finding matching brackets (or quotes or anything really) so I just had to code it in. That's where all the complicated looking code is.

Clever would be to code all that in a couple of lines. Unfortunately I don't think VBA lends itself well to that. It doesn't have the right building blocks (At least not that I found).
I didn't think it was complicated, but it is clever, and also (my best compliment), elegant.

You will go go Heaven but see PM for details!
Feb 7 '08 #10

NeoPa
Expert Mod 15k+
P: 31,489
High praise indeed. I've seen PM, but the compliment leaves me smiling.
Feb 8 '08 #11

P: 48
Sometimes (as in the explanation part of this procedure) Access (the older versions at least, pre-2003) Access can create incorrect SQL which even it can't process properly. The following little routine will get the SQL out of a QueryDef for you and fix any Access errors it finds. You can simply cut and paste the code into your project if you like.
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

First .... I wanna say sorry ! I didn't get notifications about new replies in this post .... and to be honest forgot a liitle bit :-). I didn't want to be rude !

Secondly .... Thanks, this seems exactly what I'm looking after. I'll give it a try asap

Thanks a lot for the great work over here
Feb 20 '08 #12

Post your reply

Sign in to post your reply or Sign up for a free account.