vba code import table excel to mysql

[Kenneth Hobs]

First off, you need to make sure that you have the MySQL ODBC driver installed. I have a macro that can show you but you can check it manually in the registry. Look for the name in the registry key, ODBC Drivers. On the computer that I tested this on, notice the driver name.

You need to install the Sakila database for this example. Change the password, ken, to yours. I did not try the Unicode ODBC driver. I just used ANSI for this example. Actors has 200 records. I changed the height to 3 so rows 2 to 3 or 2 rows/records were inserted into Sakila's table Actor. After running this, Actors will have 202 records.

I commented a link the shows a problem using Insert with Open. I just used Execute. The links shows another way to do it.

It took awhile to get MySQL installed and verify that the proper drivers were installed as well. Now you have a 100% tested solution as I would normally post.

Sub InsertIntoMySQL()
  Dim height As Long
  Dim rowtable As Long
  Dim strSQL As String
  ' How to add the ADO object:
  ' Tools > References > Microsoft ActiveX Data Objects 2.8 Library
  Dim oConn As ADODB.Connection
  Dim rs As ADODB.Recordset
  
  On Error GoTo ErrHandler
  Set rs = New ADODB.Recordset
  Set oConn = New ADODB.Connection
  ' http://www.connectionstrings.com/mysql-connector-odbc-5-1/
  ' ODBC Drivers: http://dev.mysql.com/downloads/connector/odbc/
  oConn.Open "DRIVER={MySQL ODBC 5.2 ANSI Driver};" & _
    "SERVER=localhost;" & _
    "DATABASE=Sakila;" & _
    "USER=root;" & _
    "PASSWORD=ken;" & _
    "Option=3"

  'number of rows with records
  height = Worksheets("Sheet1").UsedRange.Rows.Count
  height = 3

'insert data into SQL table
  With Worksheets("Sheet1")
    For rowtable = 2 To height
        strSQL = "INSERT INTO actor (actor_id, first_name, last_name) " & _
          "VALUES ('" & esc(Trim(.Cells(rowtable, 1).Value)) & "', '" & _
          esc(Trim(.Cells(rowtable, 2).Value)) & "', '" & _
          esc(Trim(.Cells(rowtable, 3).Value)) & "')"
          'rs.Open strSQL, oConn, adOpenDynamic, adLockOptimistic
          'http://stackoverflow.com/questions/2821917/connecting-to-mysql-from-excel-odbc-driver-does-not-support-the-requested-prope
          oConn.Execute strSQL
    Next rowtable
  End With
  MsgBox "Insert with success " & Trim(Str(rowtable - 2)) & " records", _
    vbInformation, "Verification Data Entry"
    
ErrHandler:
  If Err.Description <> "" And Err.Source <> "" Then
    MsgBox Err.Description, vbCritical, Err.Source
  End If
End Sub

'remove dangerous characters
Function esc(txt As String)
  esc = Trim(Replace(txt, "'", "\'"))
End Function