INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Invoking SQL from VBA

Functions to convert SQL into VBA strings and back by ferndoc
Posted: 19 Nov 05

I used to hate ""'"", "Dufo" & varname & "his friends" and & vbcrlf & _ continuation constructs with a passion. But now I freely use nested subqueries peppered with variables and generate them on the fly. New drugs, you ask? Nay, sez I! I have paused along the way to slay the beast...

Hence, I offer three related Functions to directly convert SQL derived from Query design mode to vba strings with coded apostrophes, quotes and CRLFs (invisible to vba) and variable substitution. This package should simplify your life and give you more time to spend with the kids (or whatever...)

Brevity was the aim (believe it or not!) beautify with error routines as it pleases you. If this is old news please be kind; I certainly have not recognized any such code in my wanderings.

The functions:
stdStr builds strings without your having to use equal signs, ampersands or continuation characters; has predefined substitutions for quote, apostrophe and vbcrlf. Additional substitutions let you painlessly slip variable values into SQL. Use convertSQLToStdStr output as a model for invoking. And stop the madness!

ssArrays sets up twin arrays (sorry I didn't think to multi dimension) in advance of invoking stdStr. Strings in first array will be replaced by strings in second during stdStr string construction. This allows you to insert a token into the SQL string and have it replaced with a variable value within the SSQL string. Invoke:
  ssArrays "",""   'reset
  ssArrays "@tblName", strTablename 'reps token with variable value
or
z =  ssArrays("@tblName", strTablename) 'optional format returns index (base 0) to array slot used.

convertSQLToStdStr converts the SQL in a saved query to stdStr code lines, complete with hidden apostrophes, quotes and vbcrlfs. statements printed in the immediate window can be cut and pasted into code.

Notes
  • You can insert the token into the stdStr lines of code (derived from your tested SQL) by using the editor's Replace dialog. Set the arrays in code just ahead of SQL generation.
  • You can always debug.print SQL and paste it back into the SQL window of query design for enhanced troubleshooting.
  • Copy and paste the declarations snippet, then do the same with the functions (copy/paste all at once, vba will arrange). Do not place these in a forms module.


  • Create a new tools module and copy/paste the code. Don't try to read it here. I've tested everything I can think of including cut and paste code restoration. Here's hoping I don't awaken to the dreaded bonehead typo.

    CODE

    'Add to Declarations:
    Public gpaSStoks()     'An array of substitution tokens  
    Public gpaSSputs()     'A matching array of replacements

    CODE

    Public Function stdStr(varNumOrString) As String
    'see ssArrays

    'trying to make string construction less than totally awkward
    'pass a string to append to static string (direct abutment -- no space)
    'pass any number (e.g. False) to reset the string
    'Always returns existing string whatever the state

    'anything in global array gpaSStoks will be replaced by the respective gpaSSputs content
    'character "~" is always replaced by vbcrlf
    'see ssArrays to set substitution arrays
    'remember not to transfer control while string is under construction

    Static theString As String
    If VarType(varNumOrString) <> vbString Then
        stdStr = theString
        theString = ""
    Else
        On Error Resume Next
        X = Replace(varNumOrString, "~", vbCrLf)    'return substitute
        'these from convertSQLToStdStr
        X = Replace(X, Chr(168), Chr(34))        'quote substitute
        X = Replace(X, "`", "'")                 'apostrophe substitute
        On Error Resume Next
        For i = 0 To UBound(gpaSStoks)
            If Err <> 0 Then Exit For
            If Len(gpaSStoks(i)) > 0 Then
                X = Replace(X, gpaSStoks(i), gpaSSputs(i))
            End If
        Next i
        theString = theString & X
        stdStr = theString
    End If
    End Function

    Public Function ssArrays(findIt, Putit) As Integer

    'set values into public arrays for stdStr substitution
    'see stdStr or this won't make sense
    'reset the arrays by passing both findit and putit as zero length ( "" )

        Dim i As Integer
        Dim done As Boolean
        Static once
        'On Error Resume Next
        'if ubound(gpaSStoks) then goto
        If Not once Then    '
            ReDim gpaSStoks(0)
            ReDim gpaSSputs(0)
        Else    'look to replace current findit with putit
            For i = 0 To UBound(gpaSStoks)
                If gpaSStoks(i) = findIt Then
                    gpaSSputs(i) = Putit
                    done = True
                    Exit For
                End If
            Next i
        End If
        If Not done Then    'didn't happen, insert both
            If once Then
                ReDim Preserve gpaSStoks(i)
                ReDim Preserve gpaSSputs(i)
            End If
            gpaSStoks(i) = CStr(findIt)
            gpaSSputs(i) = CStr(Putit)
        End If
        If (Len(findIt) = 0) And (Len(Putit) = 0) Then
            'erase 'command' none of the above mattered...
            ReDim gpaSStoks(0)
            ReDim gpaSSputs(0)
            once = False    'reset to reuse a(0) next time
        Else
            once = True
        End If
        ssArrays = UBound(gpaSStoks)
    End Function

    Public Function convertSQLToStdStr(Optional qdefName)
    'Convert the SQL from qdefName (default TEMP)
    'to stdSTR function calls.
    'Allows SQL to be fully tested and then literally
    'cut and pasted into code from the immediate window
    'See also functions stdStr and SSarrays

    Dim qD As QueryDef
    Dim dBs As Database
    Dim pH As String, qDn As String, qdSQL As String, sniP As String
    Dim pT As Integer, epT As Integer

    If IsMissing(qdefName) Then
        qDn = "temp"
    Else
        qDn = qdefName
    End If
    Set dBs = CurrentDb()
    Set qD = dBs.QueryDefs(qDn)
    qdSQL = Replace(qD.sql, vbCrLf, "~")
    Debug.Print "stdStr false   'reset"
    Do While epT < Len(qdSQL)
        pT = epT + 1
        epT = InStr(pT, qdSQL, "~")
        If epT = 0 Then
            epT = Len(qdSQL)
        End If
        sniP = pH & Mid(qdSQL, pT, epT - pT)    'next 2 will be restored by stdSTR
        sniP = Replace(sniP, Chr(34), Chr(168)) 'Hide the Quote character from VBA
        sniP = Replace(sniP, "'", "`")          'Hide the apostrophe from vba
        pH = "~"
        Debug.Print "stdStr " & Chr(34) & sniP & Chr(34)
    Loop
    Debug.Print "SQL = stdStr(False)   'fetch and reset"
    End Function

    Back to Microsoft: Access Modules (VBA Coding) FAQ Index
    Back to Microsoft: Access Modules (VBA Coding) Forum

    My Archive

    Resources

    Close Box

    Join Tek-Tips® Today!

    Join your peers on the Internet's largest technical computer professional community.
    It's easy to join and it's free.

    Here's Why Members Love Tek-Tips Forums:

    Register now while it's still free!

    Already a member? Close this window and log in.

    Join Us             Close