compile error: argument not optional

[aysel1989]

Hi guys, i may need some help here. I'm new to VBA excel and want to save the data directly into MYSQL database. But i have encounter some compile error with argument not optional. Here's my below codes:

Sub dbConnect() <--- error from here


Dim conn As New ADODB.Connection
Dim iRow As Integer


With Sheets("sheet1")
conn.Open "DRIVER={MySQL ODBC 5.3 ANSI DRIVER};" & _
"SERVER=myserver;" & _
"DATABASE=mydb;" & _
"USER=myuser;" & _
"PASSWORD=mypassword;" & _
"OPTION=3"


iRow = 5
Do Until .Cells(iRow, 1) = ""
Number = .Cells(iRow, 1)
country = .Cells(iRow, 2)
company = .Cells(iRow, 3)
brand = .Cells(iRow, 4)
Period = .Cells(iRow, 5)
Month = .Cells(iRow, 6)
Year = .Cells(iRow, 7)
conn.Execute "insert into table_1(number,country,company,brand,period,month,year)values('" & Number & "','" & country & "','" & company & "','" & brand & "','" & Period & "','" & Month & "','" & Year & "')"
iRow = iRow + 1
Loop
MsgBox "successfully saved into database."
conn.Close
Set conn = Nothing
End With
End Sub


Anyone can tell me what's wrong with my codes? Appreciated, thanks!

 

[Norie]

Is that all the code?

 

[aysel1989]

Is that all the code?

Yes, this is all the codes i used to connect db but it returns error message.

 

[aysel1989]

anyone can help me?