CODEWITHSUNDEEP
sqlexcelvba
ERKSMTY
ERKSMTY

Reputation: 135

Pass Defined Variable from VBA to SQL

I've defined AllParts as a variable in VBA. As formatted in SQL (see below), I'm getting an error. New to this and can't seem to tweak appropriately. Any suggestions? Thanks!

and   msikp.concatenated_segments in (" & AllParts & ")

Upvotes: 0

Views: 360

Answers (1)

ThunderFrame
ThunderFrame

Reputation: 9461

Presumably your full VBA line looks something like this?

The actual quotation marks used for SQL strings will vary by the driver/DBMS. For SQL server, single-quotes should do:

Dim AllParts As String
Dim SQL As String

AllParts = "'Foo', 'Bar'"
SQL = "SELECT * FROM segments AS msikp " & _
      "WHERE msikp.id > 0 " & _
      "and   msikp.concatenated_segments in (" & AllParts & ")"

For Access, escaped double-quotes work better:

Dim AllParts As String
Dim SQL As String

AllParts = """Foo"", ""Bar"""
SQL = "SELECT * FROM segments AS msikp " & _
      "WHERE msikp.id > 0 " & _
      "and   msikp.concatenated_segments in (" & AllParts & ")"

If it does, then it should work, but beware that building SQL with concatenation of inputs can expose you to SQL Injection attacks.

Upvotes: 1

Related Questions