How to pass VBA string into SQL Server ADODB query?

Question

VBA string to SQL works for 'WHERE from other non-date column'

I've tried several attempts: CAST in query, converting both to integers, etc.

Query returns a 0 or first few columns sometimes are the date range for all rows when running in VBA, but when pasting the VBA SQL string into SQL, it works.

    'datecol is type Date in SQL

    Dim conn as ADODB.Connection
    Dim rs as ADODB.Recordset
    Dim strdate, sqldate as String

    strdate = "11/10/20"
    sqldate = "SELECT * FROM dbTable1 WHERE datecol = '" & strdate & "'"; 

    Set rs = conn.Execute(sqldate)
    arrayRows = rs.GetRows
    rs.Close
    arrayRowsTranspose = Application.Transpose(arrayRows)

Use ADDB.Parameter to pass variables to a query, see for example stackoverflow.com/a/60640185/7599798 . Just be aware that a Date may contain a Time-part. — FunThomas
– FunThomas, Commented Nov 19, 2020 at 16:14
Thank you for the idea! I'm not sure if I'm converting w the ADDB.Parameter correctly. Do you mind posting how the code would change using the ADDB.Parameter method? Thanks! — itgonerain
– itgonerain, Commented Nov 19, 2020 at 17:17
More or less Cut&Paste the code from the link. Be aware that you need to create a ADODB.command object. And you need to know if your dates in the database have a Time-Part or not. If not, it is sufficient to create only one parameter — FunThomas
– FunThomas, Commented Nov 19, 2020 at 17:21

Dmitry Reutov · Accepted Answer · 2020-11-19 20:38:52Z

0

Probably date format is not correct, try it this way

strdate = "2020-11-10"

edited Nov 19, 2020 at 20:38

Dmitry Reutov

3,0321 gold badge9 silver badges22 bronze badges

answered Nov 19, 2020 at 20:11

user8221156

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

Collectives™ on Stack Overflow

How to pass VBA string into SQL Server ADODB query?

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related