title: Adding data from Excel to Mysql via VBA
body: <p><strong>UPDATED QUESTION...SEE BELOW</strong></p>
  
  <p>I have an excel sheet which accesses a MySQL db as a backend....</p>
  
  <p>I insert new records into MySql the following way, but I am not sure if this is the best way.</p>
  
  <pre><code>For rowline = 1 To 50
     strSQL = myquerystring (INSERT 5 columns per excel row)                    
     rs.Open strSQL, oConn, adOpenDynamic, adLockOptimistic
  Next rowline
  </code></pre>
  
  <p>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 <strong>rs.Open</strong> ....
  (each row has some 5 columns which are inserted as a record)</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p><strong>NEW INFORMATION:</strong></p>
  
  <p>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.</p>
  
  <pre><code>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 " &amp; the_table &amp; " (Client_Name, OriginCity, DestinationCity, ValidFrom, ValidTo, Quote_Number, Cost1, Cost2, Cost3) "
                  strSQL = strSQL &amp; " VALUES ('" &amp; esc(Range("BB" &amp; rowcursor)) &amp; "','" &amp; esc(Range("BC" &amp; rowcursor)) &amp; "','" &amp; esc(Range("BD" &amp; rowcursor)) &amp; "','" &amp; Format(Range("BE" &amp; rowcursor), "yyyy-mm-dd") &amp; "','" &amp; Format(Range("BF" &amp; rowcursor), "yyyy-mm-dd")
                  strSQL = strSQL &amp; "','" &amp; esc(Range("BH" &amp; rowcursor)) &amp; "','" &amp; esc(Range("BJ" &amp; rowcursor)) &amp; "','" &amp; esc(Range("BK" &amp; rowcursor)) &amp; "','" &amp; esc(Range("BJ" &amp; rowcursor)) &amp; "')"
                  cmd.CommandText = strSQL
                  cmd.Execute
     Next rowcursor
  
     app_enable_true
  
     Exit Sub
  
  no_DB_connection_error:
  
     ConnectDB
     GoTo resume_after_connecting
  
  End Sub
  </code></pre>