connecting to mysql from excel: ODBC driver does not support the requested properties
i am trying to add data to mysql from excel. i am getting the above error on this line: rs.Open strSQL, oConn, adOpenDynamic, adLockOptimistic
here is my code:
Dim oConn As ADODB.Connection
Private Sub ConnectDB()
Set oConn = New ADODB.Connection
oConn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & _
"SERVER=localhost;" & _
"DATABASE=employees;" & _
"USER=root;" & _
"PASSWORD=some_pass;" & _
"Option=3"
End Sub
Function esc(txt As String)
esc = Trim(Replace(txt, "'", "\'"))
End Function
Private Sub InsertData()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
ConnectDB
With wsBooks
For rowCursor = 2 To 11
strSQL = "INSERT INTO tutorial (author, title, price) " & _
"VALUES ('" & esc(.Cells(rowCursor, 1)) & "', " & _
"'" & esc(.Cells(rowCursor, 2)) & "', " & _
esc(.Cells(rowCursor, 3)) & ")"
rs.Open strSQL, oConn, adOp开发者_开发百科enDynamic, adLockOptimistic
Next
End With
End Sub
whats wrong with rs.Open strSQL, oConn, adOpenDynamic, adLockOptimistic ? why am i getting the odbc error?
INSERT does not return a recordset. Use oConn.Execute to do this kind of insert.
Another option might be the this particular version of mysql driver does not support adOpenDynamic (are you sure you need it?) or adLockOptimistic (same).
But anyway, this code is an sql injection hole. You should consider using parametrised queries:
dim cm as adodb.command
set cm=new adodb.command
set cm.activeconnection = oConn
cm.commandtype = adcmdtext
cm.commandtext = "insert tutorial (author, title, price) values (?,?,?)"
cm.parameters.add cm.createparameter(,adVarChar,adParamInput,50,esc(.Cells(rowCursor, 1)))
cm.parameters.add cm.createparameter(,adVarChar,adParamInput,50,esc(.Cells(rowCursor, 2)))
cm.parameters.add cm.createparameter(,adDouble,adParamInput,,esc(.Cells(rowCursor, 3)))
cm.execute ,, adExecuteNoRecords
精彩评论