Pass Defined Variable from VBA to SQL

Question

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 & ")

One line is not enough code - post the rest and don't ask us to guess... — Tim Williams
– Tim Williams, Commented Jan 28, 2017 at 3:44

ThunderFrame · Accepted Answer · 2017-01-28 00:37:12Z

1

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.

answered Jan 28, 2017 at 0:37

ThunderFrame

9,5162 gold badges31 silver badges64 bronze badges

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

Collectives™ on Stack Overflow

Pass Defined Variable from VBA to SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related