Adding quotes into a sql query with Vba code

Question

I can recover data from my database using vba, but I have to add quotes on the result of column ("value1", "Value2", "Value3" etc) But I do not know how to add quotes inside the SQL query, when using " it's wrong the request. I am used to do this kind of concat in SQL but in VBA I am not able to do it. Thanks for your help.

Sub CopyDataFromDB()
Dim DKCon As ADODB.Connection
Dim DKData As ADODB.Recordset
Dim iCols As Integer
Set DKCon = New ADODB.Connection
Set DKData = New ADODB.Recordset


DKCon.ConnectionString = SqlProvider
DKCon.Open

With DKData
        .ActiveConnection = DKCon
        .Source = "select sitsmhlccd from sitsmemoh"
        .LockType = adLockReadOnly
        .CursorType = adOpenForwardOnly
        .Open
End With

Worksheets.Add
ActiveSheet.Range("A2").CopyFromRecordset DKData
DKData.Close
DKCon.Close

What SQL technology?

Brad
– Brad

2019-04-19 12:14:41 +00:00
Commented Apr 19, 2019 at 12:14 — Brad
– Brad, Commented Apr 19, 2019 at 12:14

Vasya · Accepted Answer · 2019-04-19 12:49:37Z

1

When you use VBA, you need to double double-quote if you want to use it inside a string:

"select '""'+ sitsmhlccd +'""' from sitsmemoh"

edited Apr 19, 2019 at 12:49

answered Apr 19, 2019 at 12:19

Vasya

4844 silver badges8 bronze badges

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

4 Comments

YouFec Over a year ago

I tried this solution but it dosen't work. I have an ugly run-time error: Invalid column name '+sitsmhlccd +'

Vasya Over a year ago

@YouFec: what is the back-end database?

Vasya Over a year ago

@Louis: I am not concatenating strings, "+" symbol is inside of a string

Vasya Over a year ago

@Louis: to concatenate double quotes with the result that's coming from the database, that's what OP is asking. The "+" sign will be part of the database query

Collectives™ on Stack Overflow

Adding quotes into a sql query with Vba code

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related