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?

M463 · Accepted Answer · 2017-01-05 12:23:41Z

2

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:

edited Jan 5, 2017 at 12:23

answered Jan 5, 2017 at 11:49

M463

2,2133 gold badges28 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Rick Over a year ago

Wow thank you very much, this one is even better than the answer provided by A.S.H. as it makes the code even more clear. Thanks!

M463 Over a year ago

You're welcome. :-) Please note that you probably should not show an exception message box with the query-text to the user for security reasons. But it makes debugging a lot easier and when your code is correct, the MsgBox shouldn't appear at all so this is unlikely to be an issue in a real world situation.

Rick Over a year ago

Ok, thanks for letting me know. I got one more problem with your code: when I run it, I get the following error: Can't find the project or library And it got something to do with this piece of code: Dim paramCount As Integer maxParamIndex = UBound(paramValues)

M463 Over a year ago

Oops, that's a typo. I've corrected it above, of course it should be Dim maxParamIndex As Integer!

Rick Over a year ago

I found out mscomct2.ocx was missing in C:\Windows\System32. Everything is working perfectly now! Thanks for your support!

|

A.S.H · Accepted Answer · 2017-01-05 11:10:35Z

1

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

answered Jan 5, 2017 at 11:10

A.S.H

29.4k6 gold badges25 silver badges50 bronze badges

1 Comment

Rick Over a year ago

Thanks, I'll definitely try this out!

Collectives™ on Stack Overflow

Easier way to use declared Strings in Query in VBA

2 Answers 2

7 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related