CODEWITHSUNDEEP
mysqlexcelvbaodbcdriver
Böhmeli
Böhmeli

Reputation: 3

Connecting to MySQL server from excel with VBA code

I'm running a MySQL server on my old computer and trying to connect it from my current computer. I would like to access to the server from Excel but I keep getting the error:

'[Microsoft] [ODBC Driver Manager] Data source name not found and no default driver specified'

I've downloaded a 'MySQL connector ODBC 8.0.20 win 32' which should match my computer requirements. At this point I'm quite clueless why I keep getting the error. Please find my VBA code below.

Private Sub Data_to_SQL_Click()

Dim con As ADODB.Connection
Set con = New ADODB.Connection

Dim strConn As String

strConn = "Driver={MySQL ODCB 8.0 Unicode Driver};SERVER=(my old computer ip address);DATABASE=Database;USER=root;PASSWORD=(password)"

con.Open strConn

Dim rng As Range
Set rng = Application.Range("A2:E14")
Dim row As Range
For Each row In rng.Rowss
    Dim Sql As String
    Sql = "insert into kavijamaarat (pvm, ravintola, ruoka, hinta, kpl) values ('" & row.Cells(1).Value & "', '" & row.Cells(2).Value & "', '" & row.Cells(3).Value & "', '" & row.Cells(4).Value & "', '" & row.Cells(5).Value & "')"
    con.Execute Sql
Next row

con.Close

MsgBox "Done"

End Sub

Upvotes: 0

Views: 3026

Answers (1)

PaichengWu
PaichengWu

Reputation: 2689

Rewrite

strConn = "Driver={MySQL ODCB 8.0 Unicode Driver};SERVER=(my old computer ip address);DATABASE=Database;USER=root;PASSWORD=(password)"

to 

strConn = "Driver={MySQL ODBC 8.0 Unicode Driver};SERVER=(my old computer ip address);DATABASE=Database;USER=root;PASSWORD=(password)"

Upvotes: 1

Related Questions