Easier way to use declared Strings in Query in VBA

Question

I'm writing a macro which should run queries to transfer data from excel to a Access database, and everything is working fine, however, if I want to use a String in one of these queries, I've to type them like this:

'" & Lijn & "'

I know that the following code (which is written in VBA) is way easier to write in Javascript, by using question marks and setString:

VBA:

Dim ShiftsQ As String
ShiftsQ = "INSERT INTO Shifts(Lijn, Operator, Ploeg, Teamleider) VALUES ('" & Lijn & "', '" & Operator & "', '" & Ploeg & "', '" & Teamleider & "');"

Javascript:

var ShiftsQ = SQL.prepareStatement(INSERT INTO Shifts(Lijn, Operator, Ploeg, Teamleider) VALUES (?, ?, ?, ?);
ShiftsQ.setString(1, Lijn);
ShiftsQ.setString(2, Operator);
ShiftsQ.setString(3, Ploeg);
ShiftsQ.setString(4, Teamleider);

Is there anyway to write the VBA code like the Javascript one?


Show source
| excel-vba   | vba   | excel   | access-vba   | ms-access   2017-01-05 11:01 2 Answers

Answers to Easier way to use declared Strings in Query in VBA ( 2 )

  1. 2017-01-05 12:01

    If I face this problem I would simply solve it on my own (althoug there might be other "standard" solutions), by defining my own simple, global function (put in in any standard code module)

    Public Function S_(str as String) as String
        S_ = chr(39) & str & chr(39)
    End Function
    
    ShiftsQ = "INSERT INTO Shifts(Lijn, Operator, Ploeg, Teamleider) VALUES (" & S_(Lijn) & ", " & S_(Operator) & ", " & S_(Ploeg) & ", " & S_(Teamleider) & ");"
    

    This way, I will follow a simple and systematic rule in all my project, that is call S_(param) on any parameter of text type in my queries...

  2. 2017-01-05 12:01

    As far as I know, there is nothing like the .NET string.Format() method VBA. But you could write your own version of such a function that uses deputys and returns a formatted string.

    Private Sub Main()
        '   Your actual query
        '   The deputys are indexed in curley brackets (inspired from the .NET syntax of the equivalent function, making your code easy to read for .NET programmers)
        Dim qry As String
        qry = "SELECT {0}, {1} FROM {2} WHERE {3}"
    
        '   The values to set for the deputys in your query
        Dim parameters(3) As String
        parameters(0) = "firstname"
        parameters(1) = "lastname"
        parameters(2) = "users"
        parameters(3) = "userID = 'M463'"
    
        '   For demo purposes, this will display the query in a message box
        '   Instead of the MsgBox, you would use the formatted query to execute against the database
        MsgBox FormatString(qry, parameters)
    End Sub
    
    '   This is where the magic happens, the deputys in the given string will be replaced with the actual values from the provided array
    Private Function FormatString(strInput As String, paramValues() As String)
        '   This will be our return value
        Dim strOutput As String
        strOutput = strInput
    
        '   Verify that the given count of parameters matches the given count of deputys in the input string
        Dim maxParamIndex As Integer
        maxParamIndex = UBound(paramValues)
    
        Dim deputyCount As Integer
    
        For i = 1 To Len(strOutput) + 1 Step 1
            If Mid(strOutput, i, 3) = "{" & deputyCount & "}" Then
                deputyCount = deputyCount + 1
            End If
        Next
    
        '   If there is a mismatch between the count of parameters and the count of deputys, display exception message and exit the function
        '   Adding +1 to maxParamIndex is neccessary, as maxParamIndex refers to the maximum index (starting at 0, not 1) of the given array and deputyCount refers to the actual count of deputys (starting at 1)
        If maxParamIndex + 1 <> deputyCount Then
            MsgBox "Number of deputys has to match number of parameters for the given string:" & vbCrLf & strInput, vbCritical, "Exception in Function FormatString"
            FormatString = ""
        End If
    
        '   Iterate through the array and replace the deputys with the given values
        For i = 0 To maxParamIndex Step 1
            strOutput = Replace(strOutput, "{" & i & "}", paramValues(i))
        Next
    
        '   return the formatted string
        FormatString = strOutput
    End Function
    

    Result of example:

    enter image description here

Leave a reply to - Easier way to use declared Strings in Query in VBA

◀ Go back