vba code import table excel to mysql

[etheer]

Lets get some terminology straight.
Database=Excel:
Records=Rows
Fields=Columns
Fieldnames=Values in Columns for first row, usually.
Field Values=Column Values

The only way to really test for a Primary Key's field value being duplicated and causing an error, that I know of, would be to do another SQL and test for the value being duplicated to cause a recordcount of 0 (1 record) to be returned to the recordset.

Here is the final code using this 2nd method less the duplicate key check. Obviously, this fails on the 2nd run because the records were added in the first run. To update an existing record, requires more coding. You can learn alot from comments so I left those in. Delete those and you will see that there is not that much code there. It basically breaks down to (1) MySQL connection string and (2) adding rows as records.

Sub InsertIntoMySQL()
  ' 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
  Dim col As Integer
  Dim wsName As String, dbName As String, strSQL As String
  Dim a As Variant
  Dim c As Range, r As Range, row As Range
  
  wsName = "Film"
  dbName = "film"
  
  On Error GoTo ErrHandler
  Set rs = New ADODB.RecordSet
  Set oConn = New ADODB.Connection
  oConn.Open "DRIVER={MySQL ODBC 5.2 ANSI Driver};" & _
    "SERVER=localhost;" & _
    "DATABASE=Sakila;" & _
    "USER=root;" & _
    "PASSWORD=ken;" & _
    "Option=3"
   
' Create RecordSet
  Set rs = New ADODB.RecordSet
  With rs
    ' Record locking  ' http://www.utteraccess.com/wiki/index.php?title=Beginners_Guide_To_ODBC
    .CursorLocation = adUseClient
    .CursorType = adOpenStatic
    .LockType = adLockOptimistic
    strSQL = "SELECT * FROM " & dbName
    .Open Source:=strSQL, ActiveConnection:=oConn
  
  '  How to get the field names
    'For col = 0 To .Fields.Count - 1
       'Debug.Print .Fields(col).Name
    'Next col
    'MsgBox .RecordCount
        
    ' How to write the recordset to Excel method 1
    'Range("A1").Offset(1, 0).CopyFromRecordset rs
    
    ' How to write the recordset to an array
    '.MoveFirst
    'a = .GetRows
    ' Below needs tweaking
    'MsgBox "First Record's film_id: " & a(0, 0), , "Second Record's film_id: " & a(0, 1)
    'MsgBox "First Record's title: " & a(1, 0), , "Second Record's title: " & a(1, 1)

    If .RecordCount < 1 Then GoTo EndNow
    
    ' How to get records and field data values
    '.MoveFirst
    'For row = 0 To 0  '(.RecordCount - 1)
    '  For col = 0 To .Fields.Count - 1
    '    Debug.Print "Row: " & row, "Field: " & col, .Fields(col).Name, .Fields(col).Value
    '  Next col
    ' .MoveNext
    'Next row
    
    ' How to iterate each row in a range, add those as new records, _
    and add the field values from the column cells in the row
    
    ' How to add new records and field values from an Excel range
    Set r = Worksheets(wsName).Range("A2:M" & _
      Worksheets(wsName).Range("A" & Rows.Count).End(xlUp).row)
    For Each row In r.Rows
      .AddNew
      For Each c In row.Cells
      'Debug.Print row.row, c.Column - 1, .Fields(c.Column - 1).Name, c.Value
' IF() check to avoid error due to foreign key constraint
        If .Fields(c.Column - 1).Name <> "original_language_id" Then _
        .Fields(c.Column - 1).Value = c.Value 'Fieldnames are 0-index based
      Next c
    Next row
    .Update
  End With  'End rs object referencing
   
ErrHandler:
  If Err.Description <> "" And Err.Source <> "" Then
    MsgBox Err.Description, vbCritical, Err.Source
  End If
  
EndNow:
    Set rs = Nothing
    oConn.Close
    Set oConn = Nothing
End Sub

Thank you Kenneth Hops
This code working but the same problem sheet InsertIntoMySQL repeats rows every time run macro