Query SQL code

[wquatan]

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

[JustJim]

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

[wquatan]

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

[JustJim]

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

[Dan2kx]

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!?

[NeoPa]

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?

[NeoPa]

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.

'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

[JustJim]

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.

'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

Now that's pretty darn clever!

Jim

[NeoPa]

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).

[JustJim]

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!

[NeoPa]

High praise indeed. I've seen PM, but the compliment leaves me smiling.

[wquatan]

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.

'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

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