vba code import table excel to mysql

That requires some other work to make that connection.

When I get time, I will run a test on my localhost MySQL at home. My work day will be long tomorrow so it may be two days before I get back to this. I work with 64 bit at home so I may have to play with the connection string.

When learning a method, try to keep it simple. Try working with the Sakila database and the Actor table. Make a short 2 or 3 rows in Excel to insert into Actor.

If you installed the workbench, maybe you installed MySQL for Excel? It is in the Data ribbon when installed. Manually using it appends Excel data to your MySQL quickly.
http://dev.mysql.com/doc/mysql-for-excel/en/

:banghead::banghead::banghead:

:cry:

Please help me

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.

Code:

---

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

---

Error

Attachment 10401

It "errors" because the table Actor does not exist. When you manually look at the table Actor in Workbench, is it there?
Obviously, you are not running the code from a locally installed server.

I tried to setup a web MySQL database at the link db4free.net but was never sent an email detailing that it was setup. I may try again.

Post connection details to your web test Sakila database or PM details to me.

I am running mysqld version 5.6.13.

Thank you so much Kenneth Hobs
Now working code
Attachment 10402

Attachment 10403

But problem repeating rows when update sheet
Attachment 10404

Please help me i need update rows every time run the macro not the write new rows

Attachment 10407

Please mark this thread solved.

I am not sure how you added duplicate records. Actor_id is a primary key for that reason. It should have errored.

Copy the link to this thread and post a new one if you need help using Update in ADO. Most any SQL forum can provide that help. There are several Excel forum posts about ADO with Update that should suffice.

Quote:

---

Originally Posted by etheer

Attachment 10407

---

The problem is repeating rows ever time run the macro

How insert column A To column AJ
cell 1 to 164

Code:

---

'insert data into SQL table
  With Worksheets("M1")
    For rowtable = 2 To height
        strSQL = "INSERT INTO M1 (A, B, C) " & _
          "VALUES ('" & esc(Trim(.Cells(rowtable, 1).Value)) & "', '" & _
          esc(Trim(.Cells(rowtable, 2).Value)) & "', '" & _
          esc(Trim(.Cells(rowtable, 3).Value)) & "')"

---

Add a loop within a loop. The example only has 3 fieldnames/columnnames. You can reference a fieldname by index number to match a column number but I don't recommend that. You can put all fieldnames into an array and iterate that in a loop. I do recommend that method. The index of those array elements should be set the same as the column numbers.

Try this at your own risk (untested).

Code:

---

Sub InsertIntoMySQL2()
  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
  Dim i As Integer, s as string
 
  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=etheer;" & _
    "USER=root;" & _
    "PASSWORD=1234;" & _
    "Option=3"

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

'insert data into SQL table
  With Worksheets("M1")
    For rowtable = 2 To height
        s = "INSERT INTO M1 " & _
          "VALUES "
        For i = 1 To 35
          strSQL = strSQL & "('" & esc(Trim(.Cells(rowtable, i).Value)) & "',"
        Next i
        strSQL = s & strSQL & "('" & esc(Trim(.Cells(rowtable, 36).Value)) & "')"
        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

---

Error message

Compile error
Sup or Function not defined

To use the first SQL Insert method, you need to know the fieldnames. That string can be large so it may not be that efficient.

I don't have time to finish this 2nd method right now. It needs the loop to add the Excel rows from Height as records. In the loop, use rs.AddNew, iterate through each fieldname in the recordset, and add field values. I added a loop and debug.print to show you how to do these things. After adding the field values for a new record in the range loop, use rs.Update to update the record set. I will finish this when I get time. This method is similar to what I posted in a recent thread. That user was able to gleam what he needed.

Code:

---

' http://www.utteraccess.com/forum/lofiversion/index.php/t1947720.html

Sub InsertIntoMySQL()
  Dim height As Long
  Dim rowtable As Long, row 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
  Dim col As Integer, wsName As String, dbName As String
  Dim a As Variant
 
  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"

  'number of rows with records
  height = Worksheets(wsName).UsedRange.Rows.Count
  'height = 3
   
' Create RecordSet
  Set rs = New ADODB.RecordSet
' Record locking  ' http://www.utteraccess.com/wiki/index.php?title=Beginners_Guide_To_ODBC
  rs.CursorLocation = adUseClient
  rs.CursorType = adOpenStatic
  rs.LockType = adLockOptimistic
   
  With rs
    strSQL = "SELECT * FROM " & dbName
    rs.Open Source:=strSQL, ActiveConnection:=oConn
  '      Write the field names
    For col = 0 To .Fields.Count - 1
      'Debug.Print rs.Fields(col).Name
    Next col
    MsgBox rs.RecordCount
       
'  Write the recordset
    'Range("A1").Offset(1, 0).CopyFromRecordset rs
    .MoveFirst
    a = rs.GetRows
    '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
    .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
  End With
 
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

---

I inserted the first 3 records from sakila.film database in, https://app.box.com/s/dzbthvp7sn50q9eaijaj. I just changed the film_id autoincrement values. If those 3 records can be updated, you will be set. Notice in the example that you can add field values by using the field's name or the 0-based index for the field.

This will then give you two ways to Insert records once I finish the range loop.

Attachment 10429

Records 0

Attachment 10430

when run macro reading database but does not writing

Error message

either bof or eof is true or the current record has been deleted

Quote:

---

It needs the loop to add the Excel rows from Height as records.

---

I do not get any error on multiple runs of Sub InsertIntoMySQL. The other Sub in the other Module was the code to the link that I referenced way back in this thread. The MsgBox shows 1000 records in sakila.film. The Immediate window shows the first records values for each field. From that, you should be able to gleam how to iterate records and fields names and update from there. As I said, when I get time, I will add those few lines of code to do it.

Code:

---

Row: 0        Field: 0      film_id      1
Row: 0        Field: 1      title        ACADEMY DINOSAUR
Row: 0        Field: 2      description  A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies
Row: 0        Field: 3      release_year  2006
Row: 0        Field: 4      language_id    1
Row: 0        Field: 5      original_language_id        Null
Row: 0        Field: 6      rental_duration              6
Row: 0        Field: 7      rental_rate    0.99
Row: 0        Field: 8      length        86
Row: 0        Field: 9      replacement_cost            20.99
Row: 0        Field: 10    rating        PG
Row: 0        Field: 11    special_features            Deleted Scenes,Behind the Scenes
Row: 0        Field: 12    last_update  2/15/2006 5:03:42 AM

---