Hi folks, I tried to write a VBA code to pass data from excel to MYSQL. Each time the code runs to rs.Open strSQL , conn, I get a run-time error 3001 , Application-defined or object-defined error. I cannot figure out where exactly the problem lies. Would appreciate if you can advise. Here's the code
Code
Private Sub connect()
Dim rs As ADODB.RecordsetDim conn As New ADODB.Connection
Dim server_name As String
Dim database_name As String
Dim user_id As String
Dim password As String
server_name = "localhost"
database_name = "bloomberg"
user_id = "root"
password = "mypassword"
Set conn = New ADODB.Connection
conn.Open "Driver={MySQL ODBC 5.1 Driver}" _
& ";SERVER=" & server_name _
& ";DATABASE=" & database_name _
& ";UID=" & user_id _
& ";PWD=" & password _
& ";OPTION=16427"
End Sub
Private Sub InsertData()
Set rs = New ADODB.Recordset
connect
With Worksheets("bloom")
For rowCursor = 2 To 5
strSQL = "INSERT INTO daily_quote2 " & _
"VALUES (" & "'" & .Cells(rowCursor, 1) & "'," & _
.Cells(rowCursor, 2) & ")"
rs.Open strSQL, conn
Next
End With
End Sub