CODEWITHSUNDEEP
sqlvbajoinexcel
user1783504
user1783504

Reputation: 453

Joining Tables with MYSQL queries in VBA (excel)

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)

Upvotes: 1

Views: 2951

Answers (1)

John Woo
John Woo

Reputation: 263723

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';"

Upvotes: 2

Related Questions