Adding data from Excel to Mysql via VBA

Question

UPDATED QUESTION...SEE BELOW

I have an excel sheet which accesses a MySQL db as a backend....

I insert new records into MySql the following way, but I am not sure if this is the best way.

For rowline = 1 To 50
   strSQL = myquerystring (INSERT 5 columns per excel row)                    
   rs.Open strSQL, oConn, adOpenDynamic, adLockOptimistic
Next rowline

Basically the query string goes through each row in excel sheet (from 1 to 50) and the data on certain cells are added to the sql query and then inserted with rs.Open .... (each row has some 5 columns which are inserted as a record)

Everything runs well, however I just want to know if there is a faster way (just one INSERT query), inserting all 50 (and 5 columns on each), from row 1 to 50, all at once.

At the moment it is doing 50 individual INSERT queries, so I am trying to reduce that to 1 but I don't know if it is possible.

NEW INFORMATION:

Hi, following your advice and links (thanks!) and some Googling, I ended up with the following code... It works fine, HOWEVER to INSERT 100 rows it takes approx 15 seconds....this is far too much. I am hoping I can get some code/ideas on how to Execute the query once, (inserting all 100 rows in one hit). Please note that I am a beginner on this so if you can stir me into some samples on how it should be done it will be much appreciated.

Public Sub INSERT_to_MySQL()

   Dim conn As ADODB.Connection
   Dim cmd As ADODB.Command
   Dim strSQL As String

   app_enable_false

   On Error GoTo no_DB_connection_error
resume_after_connecting:

   Set cmd = New ADODB.Command
   cmd.ActiveConnection = oConn

   ' LOOP and INSERT the data
   ' 100 rows take approx 15 seconds to INSERT
   For rowcursor= 1 To 100
                the_table = "`global`.`filesaved` "
                strSQL = "INSERT INTO " & the_table & " (Client_Name, OriginCity, DestinationCity, ValidFrom, ValidTo, Quote_Number, Cost1, Cost2, Cost3) "
                strSQL = strSQL & " VALUES ('" & esc(Range("BB" & rowcursor)) & "','" & esc(Range("BC" & rowcursor)) & "','" & esc(Range("BD" & rowcursor)) & "','" & Format(Range("BE" & rowcursor), "yyyy-mm-dd") & "','" & Format(Range("BF" & rowcursor), "yyyy-mm-dd")
                strSQL = strSQL & "','" & esc(Range("BH" & rowcursor)) & "','" & esc(Range("BJ" & rowcursor)) & "','" & esc(Range("BK" & rowcursor)) & "','" & esc(Range("BJ" & rowcursor)) & "')"
                cmd.CommandText = strSQL
                cmd.Execute
   Next rowcursor

   app_enable_true

   Exit Sub

no_DB_connection_error:

   ConnectDB
   GoTo resume_after_connecting

End Sub

Answer 1

If I run a statement and do not expect a reply back, I usually go for the Command object in VBA.

Answer 2

I would move the open command outside the loop, then use the Execute method to do the inserts in the loop. You don't want to do an open on the database every time.

Here's a good page of ADO methods.

Here's a question on Batch Inserting (and Updating).

EDIT: Looking at Remou's link in his comment, I realized that you probably don't even need the open. Just use the Execute method for the Inserts.

Answer 3

Hi

Find below a process which works, in case someone else has in future same problem. Might not be the best solution but 100 records are saved all at once in less than a second, which is what I was after. Also there is only one INSERT query done (instead of 100 different ones for each row.

Thanks to all for your guidance.

   Dim conn As ADODB.Connection
   Dim cmd As ADODB.Command
   Dim rst_recordset As ADODB.Recordset
   Dim strSQL As String
   Dim strSQL2  as string


   On Error GoTo no_DB_connection_error
resume_after_connecting:

   Set cmd = New ADODB.Command
   cmd.ActiveConnection = oConn

   ' LOOP and INSERT the data
   lastrow = Range("BB65536").End(xlUp).Row



                the_table = "`global`.`filesaved` "
                strSQL = "INSERT INTO `global`.`filesaved` " & " (Client_Name, OriginCity, DestCity, ValidFrom, ValidTo, Quote_Number, Cost1, Cost2, Cost3) VALUES "
                strSQL2 = ""
                For excel_row = 1 To lastrow 
                    strSQL2 = strSQL2 & " ('" & cells(excel_row,1) & "','" & cells(excel_row,2) & "','" & cells(excel_row,3) & "','" & cells(excel_row,4) & "','" & cells(excel_row,5) & "','" & cells(excel_row,6) & "','" & cells(excel_row,7) & "','" & cells(excel_row,8) & "','" & cells(excel_row,9) & "') ,"  
                next excel_row

                strSQL = strSQL & strSQL2
                Mid(strSQL, Len(strSQL), 1) = ";" ' gets rid of the last comma

                cmd.CommandText = strSQL
                cmd.Execute