Fix 'Expected end of statement'

Question

I'm really new to Excel macros and VBA. What I'm try to do is to get the data from MySQL database using macro. I search for code to connect the Excel to MySQL and i got this one

Sub connect()
    Dim Password As String
    Dim SQLStr As String
    Dim Server_Name As String
    Dim User_ID As String
    Dim Database_Name As String
    Dim table_name As String

    Set rs = CreateObject("ADODB.Recordset")
    Server_Name = ""
    Database_Name = ""
    User_ID = ""
    Password = ""
    table_name = ""

    Set cn = CreateObject("ADODB.Connection")
    cn.Open "Driver={MySQL ODBC 5.1 Unicode Driver};Server=" & _
            Server_Name & ";Database=" & Database_Name & _
            ";Uid=" & User_ID & ";Pwd=" & Password & ";"

    SQLStr = ""

    rs.Open SQLStr, cn, adopenstatic

    ThisWorkbook.Sheets(1).Range("A1").CopyFromRecordset rs
    cn.Close
    MsgBox "DONE!"
End Sub

I change some of code to match my system

Sub connect()
    Dim Password As String
    Dim SQLStr As String
    Dim Server_Name As String
    Dim User_ID As String
    Dim Database_Name As String
    Dim table_name As String

    Set rs = CreateObject("ADODB.Recordset")
    Server_Name = "localhost"
    Database_Name = "sakila"
    User_ID = "root"
    Password = "Password"
    table_name = ""

    Set cn = CreateObject("ADODB.Connection")
    cn.Open "Driver={MySQL ODBC 8.0 Unicode Driver};Server=" & _
            Server_Name & ";Database=" & Database_Name & _
            ";Uid=" & User_ID & ";Pwd=" & Password & ";"

    SQLStr = "select * from film_actor"

    rs.Open SQLStr, cn, adopenstatic

    ThisWorkbook.Sheets(1).Range("A2").CopyFromRecordset rs
    cn.Close
    MsgBox "DONE!"
End Sub

It works just fine with query like that, but when I try to change the query to

    SQLStr = "select a.title, concat(b.first_name, Space(1),b.last_name), c.name, a.description, a.rating from film_actor z"
    left join film a on a.film_id = z.film_id
            left join actor b on b.actor_id = z.actor_id
    left join film_category y on y.film_id = z.film_id
    left join category c on c.category_id = y.category_id
    order by a.title asc"

It always show 'Expected: end of statement' and highlighting the 'film' word in 1st left join and there's a yellow arrow also highlighting 'Sub connect()' in 1st row. I already remove the quotation mark after 'film_actor z' but its keep coming back. Is there anything wrong with my query / code?

Maybe see [this question](https://stackoverflow.com/questions/16624550/how-to-break-long-string-to-multiple-lines). — BigBen, Jul 16 '19 at 03:09

score 1 · Accepted Answer · edited Jul 16 '19 at 05:39

1

Try something like this:

SQLStr = "select a.title, concat(b.first_name, Space(1),b.last_name), c.name, a.description, a.rating from film_actor z" & _
  "  left join film a on a.film_id = z.film_id" & _
  "  left join actor b on b.actor_id = z.actor_id" & _
  "  left join film_category y on y.film_id = z.film_id" & _
  "  left join category c on c.category_id = y.category_id" & _
  "order by a.title asc"

edited Jul 16 '19 at 05:39

Pᴇʜ

56,719
10
49
73

answered Jul 16 '19 at 03:30

SamP

176
1
10

It works, but please explain it to me so I can use it on different cases – H. D. U. Jul 16 '19 at 03:39
1

You cannot just write VBA code on multiple lines, you need to let it know you want to do so. To do that you just add the underscore at the end. However, if you are inside a string the underscore won't be recognised, so you need to use a quote to get out of the string, an ampersand to connect to the next part of the string, and another quote to go back into building your query. The underscore can then go anywhere between the closing and re-opening quotes, so it is just up to taste really whether the ampersand is at the end of the line or the start of the next line. I prefer it at the end. – SamP Jul 16 '19 at 05:27

Fix 'Expected end of statement'

1 Answers1