Joining Tables with MYSQL queries in VBA (excel)

Question

I am completely new to Visual Basic. I am working with a MYSQL data base and I want to use VB in excel so I can work with more complex queries. For some reason, when I try to join tables in vb, I get an error message. Can somebody tell me what is wrong with my code.

strSql = "SELECT COUNT(*)FROM `order`" & _
                 "JOIN user ON user.id = order.destination_id" & _
                 "WHERE payment_status = 'pay';"

    rs.Open strSql, oConn, adOpenDynamic, adLockPessimistic

    res = rs.GetRows

    rs.Close

    Range("A1", "A6") = res(0, 0)

John Woo · Accepted Answer · 2012-10-29 16:52:36Z

2

your current query will produce this string,

SELECT COUNT(*)FROM `order`JOIN user ON user.id = order.destination_idWHERE payment_status = 'pay';
               ^           ^                                          ^

you lack space during your concatenation, in order to correct that, simply add space before double quote.

strSql = "SELECT COUNT(*) FROM `order`   " & _        
         "JOIN user ON user.id = order.destination_id   " & _
         "WHERE payment_status = 'pay';"

edited Oct 29, 2012 at 16:52

answered Oct 29, 2012 at 16:45

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

1 Comment

user1783504 Over a year ago

thanks a lot John! It worked. But what was wrong with my code. they are practically the same?

Collectives™ on Stack Overflow

Joining Tables with MYSQL queries in VBA (excel)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related