Inserting Data Into MySQL From Excel Using VBA

seun asked in an old post of mine, Interfacing with MySQL via Excel; whether it’s possible to submit data from Excel into MySQL.

Yes, it is very much possible to do this. In fact, I’ve written a tutorial on how to do this with the help of VBA.

First, we create the table that we will use to store the data. The SQL statement below will create our sample table called tutorial. Just copy and paste it into your favourite MySQL IDE or phpMyAdmin.

CREATE TABLE IF NOT EXISTS `tutorial` (
	`id` int(11) NOT NULL auto_increment,
	`title` varchar(255) NOT NULL,
	`author` varchar(255) NOT NULL,
	`price` float(4,2) NOT NULL,
	PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Now that we’ve created the table, it’s time to store some data in it. The data must come from a source. In this example, I’ve created a list of book titles, along with the author and price of each book. Just copy the table below and paste it into an Excel worksheet.

Title Author Price
How to Win Friends and Influence People Dale Carnegie 7.99
The Magic of Thinking Big David Schwartz 10.17
The Richest Man in Babylon George S. Clason 6.99
As a Man Thinketh James Allen 9.95
The Power of Your Subconcious Mind Dr. Joseph Murphy 7.49
The Magic of Believing Claude M. Bristol 6.99
It Works R.H. Jarrett 3.00
Write It Down, Make It Happen: Knowing What You Want and Getting It Henriette Anne Klauser 10.52
The Attractor Factor: 5 Easy Steps For Creating Wealth (Or Anything Else) From the Inside Out Joe Vitale 11.53
The Science of Getting Rich Wallace D. Wattles 11.20

Now rename that sheet as Books. If you haven’t displayed your Visual Basic and Control Toolbox toolbars yet, now is a good time to do so. Go to the View top menu and navigate to Toolbars. Make sure Visual Basic and Control Toolbox are checked.

I prefer to place the toolbars at the top panel area so they’re out of the way of my Excel spreadsheet. You might want to do the same.

Next, click on the Properties icon of the Control Box toolbar and set the name of the Books worksheet as wsBooks. You’ll get something like the screenshot below:

Sheet Properties

Now it’s on to some programming. But before we even start typing a single line of code, we need to understand what we’re trying to achieve with our code:

  1. Connect to the local MySQL database server
  2. Use the demo database
  3. Insert each line of the Books table into the tutorial database we created earlier

Connecting to the Database Server and Selecting the Correct Database

As we will be using VBA to perform our data insertion, we need a method to connect to the database server and ensure we’ve selected the correct database where the data will be inserted into.

There are three components necessary for us to perform MySQL database manipulation using VBA:

  1. MySQL Connector/ODBC
  2. Microsoft ActiveX Data Objects (ADO) Library
  3. The correct connection string to access the MySQL database

For this tutorial, I’m using MySQL Connector/ODBC 5.1. You can download it here. For Windows users, grab the MSI Installer version and just double click the file and go through the installation screen.

You’ll also need to reference to the Microsoft ActiveX Data Objects Library in your Excel Workbook. Fire up the Visual Basic Editor using Alt-F11. Go to the Tools top menu item of the Visual Basic Editor and choose References.

In the list of references, check the Microsoft ActiveX Data Objects 2.8 Library. Refer to the following screenshot:

Referencing Microsoft ADO Library

Next, we will then start coding. The first thing we should do is to write a Sub procedure to connect to the database. This will be done using an ADODB connection.

First we need to declare the variable for the ADODB connection. Make sure you’ve double-clicked the wsBooks worksheet in the Project Explorer (if you can’t see it, press Ctrl-R). It should bring out a code window for that sheet.

In that sheet, enter the following code:

Dim oConn As ADODB.Connection
Private Sub ConnectDB()
	Set oConn = New ADODB.Connection
	oConn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & _
		"SERVER=localhost;" & _
		"DATABASE=yourdatabase;" & _
		"USER=yourdbusername;" & _
		"PASSWORD=yourdbpassword;" & _
		"Option=3"
End Sub

You should replace yourdatabase, yourdbusername and yourdbpassword with your database name, your database username and the database password accordingly.

Test your ConnectDB Sub procedure by putting the cursor anywhere between the Sub statement and press F5 (this runs the Sub procedure). If an error popup appears, check that your MySQL service is running and that you’ve referenced to the Microsoft ActiveX Data Objects 2.8 Library.

Once the ConnectDB Sub procedure is working, we will code the Sub procedure to perform data insertion. Before that, we need to create a data sanitizing function to escape single quotes before inserting them into the database. This is necessary because improper quote escaping will cause data insertion to fail.

Here are the codes for the esc function that will escape single quotes from data to be inserted:

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

And now for the Sub procedure to perform data insertion:

Dim rs As ADODB.Recordset
Private Sub InsertData()
	Set rs = New ADODB.Recordset
	ConnectDB
	With wsBooks
		For rowCursor = 2 To 11
			strSQL = "INSERT INTO tutorial (author, title, price) " & _
				"VALUES ('" & esc(.Cells(rowCursor, 1)) & "', " & _
				"'" & esc(.Cells(rowCursor, 2)) & "', " & _
				esc(.Cells(rowCursor, 3)) & ")"
			rs.Open strSQL, oConn, adOpenDynamic, adLockOptimistic
		Next
	End With
End Sub

Now run the InsertData Sub procedure and you will see that it will insert the data from line 2 to line 11 of the Books worksheet into the tutorial table.

Hope this tutorial has given you a basic understanding on how to insert data into a MySQL database from Microsoft Excel using VBA. If you have any questions, just drop a comment below.

61 responses to “Inserting Data Into MySQL From Excel Using VBA”.

  1. Dabido Says:

    Aww man! I paid too much for my books!!!! 🙂

  2. Site Admin Azmeen Says:

    Heh Dabido, I doubt that this mini tutorial can compete with a professionally written book 😛

  3. Mikael Nyberg Says:

    Is this actually viable for everyday modification of a table?

  4. Peter Wright Says:

    Great work! Very useful and easy.

  5. Site Admin Azmeen Says:

    Mikael Nyberg,

    From my tests, I’d say it is viable depending on the amount of data you wanted to insert. I’ve tested the insertion of thousands of lines of data and it works as intended.

    The only time I ran into problems was when the data contained foreign language characters. This is because it mismatches the collation of my database table.

    In conclusion, my response is: “It depends.”

    Peter Wright,

    Thank you 🙂

  6. Mark Ruiter Says:

    The connection works fine but the data is not added to the database. Can you explain the locations of the code a bit more.
    I have all 3 codes in the same page right now. Is that correct?

  7. Site Admin Azmeen Says:

    Mark Ruiter,

    Yes, everything should be on one page.

    To troubleshoot data insertion failure, you could try to MsgBox the SQL statement before it performs the actual insertion.

    This will show if there’s a problem with the SQL statement itself.

    Just stick the line:

    MsgBox strSQL

    …just before this:

    rs.Open strSQL, oConn, adOpenDynamic, adLockOptimistic

  8. Sarah Says:

    I am getting a compilation error at this statement:
    ‘” & esc(.Cells(rowCursor, 1)) & “‘

    The message says: “Invalid or unqualified reference”.

    then it hightlights the .Cells part. All I am trying to do is copy values from a cell that is length 4 with contents “1234” and put it into a sql server.
    Please help.

  9. sarah Says:

    this is a continuation of the previous comment:

    the code that I have now is:
    For Row = 2 To 14 Step 1

    connection.Execute “INSERT INTO Customer(Company, Contact,” & _
    “Address1, Address2, City, State, Country, Zip, Zip4, ” & _
    “Email, Phone, Status)VALUES(‘” & esc(.Cells(Row, 2)) & “‘,” & _
    “‘” & esc(.Cells(Row, 3)) & “‘, ‘” & esc(.Cells(Row, 4)) & “‘, ” & _
    “‘” & esc(.Cells(Row, 5)) & “‘,'” & esc(.Cells(Row, 6)) & “‘, ” & _
    “‘” & esc(.Cells(Row, 7)) & “‘,'” & esc(.Cells(Row, 8)) & “‘,” & _
    “‘” & esc(.Cells(Row, 9)) & “‘,'” & esc(.Cells(Row, 10)) & “‘,” & _
    “‘” & esc(.Cells(Row, 11)) & “‘,'” & esc(.Cells(Row, 12)) & “‘,” & _
    “‘” & esc(.Cells(Row, 1)) & “‘)”

    Next Row

    and it only takes the pre-assigned legth-1. So if I populate a cell to it max capacity it will tell me that the string will get truncated. Ex: cell length=5. I populate with “Hello” which is length 5 but it can only import a length of 4 into the server. If I modify it to “Hell” then it works.

    Please help.
    Thanks!
    Pleas helo

  10. Site Admin Azmeen Says:

    sarah,

    My suggestion is to use the VBA function Left to only pick the first four characters of the column. For example:

    INSERT INTO table (fourcharacteronly) VALUES ('" & esc(Left(.Cells(Row, Column), 4)) & "')

    What this does is store only the first four characters of the cell if it’s longer than four.

  11. sarah Says:

    Thank you for your response but I have a table with multiple columns with different lengths and the code I have is taking the max length-1. So when it comes to the smallest length 4, its only taking 3 characters but the field needs to be populated with 4. This will tell me that it will truncate the value. Here is my original code:

    Sub dbInsert()

    ‘ dbInsert Macro
    ‘ Macro recorded 3/20/2007 by steve-dalessandro

    ‘ Keyboard Shortcut: Ctrl+m

    ‘declare connection
    Dim connection As ADODB.connection
    Set connection = New ADODB.connection

    ‘create database connection
    Dim connectionString As String

    connectionString = “Provider = SQLOLEDB; Data Source = SAINTDEVSQL1; Initial Catalog = dbTraining; User Id = bouska1992; Password = 12floz/355ml;”
    connection.connectionString = connectionString
    connection.Open

    ‘insert data into database
    Dim numRows As Integer

    RowsCount = ActiveSheet.UsedRange.Rows.Count

    For Row = 2 To 14 Step 1

    connection.Execute “INSERT INTO Customer(Company, Contact,” & _
    “Address1, Address2, City, State, Country, Zip, Zip4, ” & _
    “Email, Phone, Status)VALUES(‘” & Cells(Row, 2) & ” ‘,” & _
    “‘” & Cells(Row, 3) & ” ‘, ‘” & Cells(Row, 4) & ” ‘, ” & _
    “‘” & Cells(Row, 5) & ” ‘,’ ” & Cells(Row, 6) & ” ‘, ” & _
    “‘ ” & Cells(Row, 7) & ” ‘,’ ” & Cells(Row, 8) & ” ‘,” & _
    “‘ ” & Cells(Row, 9) & ” ‘,’ ” & Cells(Row, 10) & ” ‘,” & _
    “‘ ” & Cells(Row, 11) & ” ‘,’ ” & Cells(Row, 12) & ” ‘,” & _
    “‘ ” & Cells(Row, 13) & “‘)”

    Next Row

    ‘close database connection
    connection.Close
    Set connection = Nothing

    End Sub

    please help.

  12. Site Admin Azmeen Says:

    Hi Sarah,

    I think the problem is with your table design then. Take a look at your Customer table using phpMyAdmin or whatever MySQL database management tool you’re using.

    See the properties of the column that is giving you problems. Chances are it would be a VARCHAR typed column.

    Set the column size to 4 and it should now accept data that is four characters long or less.

  13. Jerad Says:

    Here’s my problem. I am creating the Insert statement in one field. So when I’m calling the above code, it works with just saying

    strSQL = ActiveCell.Value
    and then executing the statement with the above. My only problem is this. If my statement has more than 3 columns that I’m trying to insert statement into, then it bombs and either tells me the syntax is wrong or that the ODBC driver doesn’t support the property. I know the syntax is correct,and I know the field type is correct. Has anyone experienced this problem.

    The code would work all day long if I was only inserting a max of three values but once I try to add the fourth, it errors.

    THANKS!!
    -Jerad

  14. Site Admin Azmeen Says:

    Jerad: It would help if we can see a sample of the SQL statement.

  15. mr excel Says:

    I been searching the web for easy code examples toooooo long. I found a site where you can download ready files. Not so good descriptions but the files worked.

    http://vbaexcel.eu/vba-macro-code/extract-get-data-from-mysql-php

    http://vbaexcel.eu/vba-macro-code/update-mysql-database-php

    http://vbaexcel.eu/vba-macro-code/write-to-mysql-database-php

  16. mustang Says:

    i found out that the column inside excel with date cannot be updated to the database or is there any other way to do it?

  17. Site Admin Azmeen Says:

    mustang,

    Assuming the date value is in cell A1 on Sheet1; you can grab the individual values of the year, month and date as follows:

    yearval = Str(Year(Sheet1.Cells(1, 1).Value))
    monthval = Str(Month(Sheet1.Cells(1, 1).Value))
    dayval = Str(Day(Sheet1.Cells(1, 1).Value))

    If Left(monthval, 1) = " " Then
    monthval = "0" + Right(monthval, 1)
    End If

    If Left(dayval, 1) = " " Then
    dayval = "0" + Right(dayval, 1)
    End If

    mysqldate = yearval + "-" + monthval + "-" + dayval

    You can then use the value in mysqldate to insert into your database. The format will be in yyyy-mm-dd.

  18. mustang Says:

    Thanks alot for the quick response!!!

  19. Site Admin Azmeen Says:

    Glad to help 🙂

  20. mustang Says:

    Let’s say we have two/three tables can we insert the data into all three tables using just one excel sheet?Thanks for the help again!

  21. Site Admin Azmeen Says:

    mustang,

    Of course; just generate different SQL statements to insert data in the different tables and execute them. eg:

    strSQL1 = "INSERT INTO tutorial (author, title, price) " & _
    "VALUES ('" & esc(.Cells(rowCursor, 1)) & "', " & _
    "'" & esc(.Cells(rowCursor, 2)) & "', " & _
    esc(.Cells(rowCursor, 3)) & ")"
    rs.Open strSQL1, oConn, adOpenDynamic, adLockOptimistic

    strSQL2 = "INSERT INTO tutorialbackup (author, title, price) " & _
    "VALUES ('" & esc(.Cells(rowCursor, 1)) & "', " & _
    "'" & esc(.Cells(rowCursor, 2)) & "', " & _
    esc(.Cells(rowCursor, 3)) & ")"
    rs.Open strSQL2, oConn, adOpenDynamic, adLockOptimistic

  22. mustang Says:

    oh sorry what i meant is…there are four colums in the excel sheet and the first and second column belongs to table A then third and fourth column belongs to table B…how do we split it to enter their respective tables?is it the same code use?thanks alot again!

  23. Site Admin Azmeen Says:

    mustang,

    Similar code; you just need to plug in your values according to which table you want them inserted in. eg:

    strSQL = "INSERT INTO table1 (val1, val2) " & _
    "VALUES ('" & esc(.Cells(rowCursor, 1)) & "', " & _
    "'" & esc(.Cells(rowCursor, 2)) & ")"
    rs.Open strSQL, oConn, adOpenDynamic, adLockOptimistic

    strSQL = "INSERT INTO table2 (val3, val4) " & _
    "VALUES ('" & esc(.Cells(rowCursor, 3)) & "', " & _
    "'" & esc(.Cells(rowCursor, 4)) & ")"
    rs.Open strSQL, oConn, adOpenDynamic, adLockOptimistic

  24. mustang Says:

    Thanks!!!!!

  25. newbie Says:

    Let’s say the file have 13 line of data so we need to change the rowcursor = 2 to 14…can we automate the rowcursor so that whenever we insert a new data to the excel file it will automatically load the data to the database when we run the code?lets say we insert 23 line of data then it automatically insert the 23 data without changing the rowcursor = 2 to 24?

  26. newbie Says:

    another question is there a way to show the error msg box if the data inserted is a duplicate primary key instead of showing the runtime error?thank you for your help.

  27. Johan Says:

    Here is a link to a program for updating a mysql database, good when updating alot of data to cms systems etc!

    http://vbaexcel.eu/vba-macro-code/update-mysql-database-php

  28. newbie Says:

    thanks johan for the link but i wanted to insert data to different table and i hope the rowcursor can be automated…thanks for the help!

  29. Site Admin Azmeen Says:

    newbie,

    Check out this post which also has a pre-built Excel file with the codes.

  30. Jnaneshwar Says:

    Hi.We use Open Office.Calc please any buddy help on the same.

  31. Leon Says:

    Thanks for the useful stuff.

  32. Ivanildo Says:

    Here’s my problem:
    I’m trying to insert data that containe foreign language characters. It must be some collation problem of my database table. Can anybody help me on how to solve this, im using UTF-8 for my table collation

  33. guna Says:

    I tried this way, but I am getting “Run Time Error 28” Object required.

    Can you help me out?

    Thanks

  34. Crisanto Estipular Says:

    Hi!.

    i Would Like To ask if You have Sample Project that would insert data into mysql using excel. thanks..

  35. Baz Fitzpatrick Says:

    I’ve followed your setup instructions to the letter but when I run the “ConnectDB()” subroutine I get the following error:

    “Run Time Error ‘-2147467259 (80004005)’:

    Automation Error
    Unspecified Error”

    Can you advise a Google search throws up lots of simialr probelms.

  36. Site Admin Azmeen Says:

    Baz Fitzpatrick,

    It’s most likely that you didn’t install MySQL Connector/ODBC.

  37. gspaull2009 Says:

    I have used your code and am getting an error that says

    Run-time error ‘-2147217887 (90040e21)

    ODBC driver does not support the requested properties

    then when you debug the line of code it points to is

    rs.Open strSQL, oConn, adOpenDynamic, adLockOptimistic

    Can anyone help???

  38. newtoDB Says:

    Hi, I have an excel sheet named “marine” and I am trying to insert the excel data to the mysql DB. I get

    #1046 – No database selected
    CREATE TABLE `economics` (
    `id` int( 11 ) NOT NULL AUTO_INCREMENT ,
    `Type of ecosystem` varchar( 255 ) NOT NULL ,
    `service` varchar( 255 ) NOT NULL ,
    `value` float( 4, 2 ) NOT NULL ,
    PRIMARY KEY ( `id` )
    ) ENGINE = InnoDB DEFAULT CHARSET = utf8 AUTO_INCREMENT =1

  39. new2DB Says:

    I get an error
    #1046 – No database selected
    CREATE TABLE `economics` (
    `id` int( 11 ) NOT NULL AUTO_INCREMENT ,
    `Type of ecosystem` varchar( 255 ) NOT NULL ,
    `service` varchar( 255 ) NOT NULL ,
    `value` float( 4, 2 ) NOT NULL ,
    PRIMARY KEY ( `id` )
    ) ENGINE = InnoDB DEFAULT CHARSET = utf8 AUTO_INCREMENT =1
    The name of the excel sheet which I am trying to copy to DB is ‘marine’

  40. new2DB Says:

    sorry its now working.thanks

  41. Steve Says:

    Hi Azmeen,

    Well written and I appreciate the step-by-step examples.

    Question: instead of talking to a LOCAL database, can I talk to a remote database on my web server by changing the connection string from “localhost” to “my_hostname.here.com”?

    FYI, my MySQL database is on a shared server hosted by GoDaddy.

    Thanks!

  42. Site Admin Azmeen Says:

    Hi Steve,

    Yes you can connect to a remote database using its hostname (or IP address).

    The MySQL installation on the server must also be configured to allow remote MySQL access.

  43. Stephen Says:

    Hi Azmeen,

    I was wondering how I could change this code a little bit so that it searches a column (column 1, for example) in the excel, and updates the database table with columns 2-10 from the excel for the rows in the database table with a matching field from column 1.

    I’m not sure if I explained that well enough, but let me know if I haven’t. This tutorial is great!

    Stephen

  44. Steve Says:

    To Steve:

    I also use GoDaddy MySQL and want to access my database remotely and found this article on the GoDaddy support pages:

    “Connecting Remotely to Shared Hosting Databases” http://community.godaddy.com/help/4978

    A key point in the article is, “To connect remotely to a database, you must create a database and enable direct database access. If a database was created without this option selected, you will not be able to connect remotely.”

  45. kart Says:

    hey,,im glad dat ive got the right solution…..thank u dude…..

  46. mathu Says:

    thank you for this site.

  47. Excel-MySql Says:

    […] Excel-MySql tal vez esto te sea de utilidad… Inserting Data Into MySQL From Excel Using VBA – HTNet Suerte! Responder Con […]

  48. Steve Says:

    I’m kind of following the same logic. the issue I have is that I’m trying to insert over 100 rows to MySQL and MySQL defaults at 99 connections. It’s like my code does not “disconnect” properly after doing an insert. I’ve tried to delete the querytables after each iteration, but then I get an error saying that the background query is still refreshing. I think it’s got to be something easy, but I’m at a loss. Any help would be much appreciated!

    Sub Refresh_Bill_Entry()

    If (Range(“A65536”).End(xlUp).Row = Range(“B65536”).End(xlUp).Row) And (Range(“B65536”).End(xlUp).Row = Range(“C65536”).End(xlUp).Row) Then

    ActiveWindow.SmallScroll Down:=-6
    Range(“J7:Z7”).Select
    Selection.AutoFill Destination:=Range(“J7:” & “Z” & Range(“A65536”).End(xlUp).Row), Type:=xlFillDefault

    fakerow = “A” & ((Range(“A65536”).End(xlUp).Row) + 1)

    Range(“A” & Range(“A65536”).End(xlUp).Row).Select

    begrow = 7
    endrow = Range(“A65536”).End(xlUp).Row

    I = 7

    XVAL = “”

    n = 0

    Do While I <= endrow

    XVAL = XVAL + " " + Range("P" & I)

    With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=FamilyDB;", _
    Destination:=Range(fakerow))
    .Name = "INSERT"
    .CommandText = Array(Range("P" & I))
    .FieldNames = False
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = False
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlOverwriteCells
    .SavePassword = False
    .SaveData = False
    .AdjustColumnWidth = False
    .RefreshPeriod = 9
    .PreserveColumnInfo = False
    .Refresh BackgroundQuery:=True
    End With

    I = I + 1

    delete_named_range ("Steve_s Bill Log")

    Loop

    Range(fakerow).EntireRow.Select
    Range(fakerow).EntireRow.Delete

    Else
    MsgBox ("Partial Data Error, Check Row# " & Range("A65536").End(xlUp).Row)
    End If

    Range("A" & Range("A65536").End(xlUp).Row).Select

    ActiveWindow.SmallScroll Down:=-6
    Range("J7:Z7").Select
    Selection.AutoFill Destination:=Range("J7:" & "Z" & Range("A65536").End(xlUp).Row), Type:=xlFillDefault

    Range("A" & Range("A65536").End(xlUp).Row).Select
    End Sub

  49. VB Newbie Says:

    I am getting the Runtime-Error 424 Object Required with the following code. The connection to the database works fine with no errors. The table is called “test”. Any ideas? I have been scratching at this for hours…

    Private Sub InsertData()

    Dim strSQL As String
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    ConnectDB
    With wsBooks
    For rowCursor = 2 To 11
    strSQL = “INSERT INTO test (AMAnum, cont1, cont2) ” & _
    “VALUES (‘” & (.Cells(rowCursor, 1)) & “‘, ” & _
    “‘” & (.Cells(rowCursor, 2)) & “‘, ” & _
    (.Cells(rowCursor, 3)) & “)”
    MsgBox strSQL
    rs.Open strSQL, oconn, adOpenDynamic, adLockOptimistic
    Next
    End With
    End Sub

  50. VB Newbie Says:

    Duh! O.K. i just fixed that error by changing the wsBooks to the correct worksheet name. But now I am getting the Run-Time error -2147217887 Automation Error.

  51. Sid Khullar Says:

    Worked like a charm. Thank you! My purpose was a little different though. Parsing consolidated recipe containers into individual recipes and uploading them into MySQL.

  52. Paolo Says:

    Sorry, I’m a newbye… I write all the code in the same module, but it doesn’t work because an error message say me that after “End” can be only a comment. May I write each Function in a module? Or a script? I can’t understand :/
    PAOLO

    Dim oConn As ADODB.Connection
    Private Sub ConnectDB()
    … … …
    End Sub

    Function esc(txt As String)
    esc = Trim(Replace(txt, “‘”, “\'”))
    End Function

    Dim rs As ADODB.Recordset
    Private Sub InsertData()
    … … …
    End Sub

  53. Steve Says:

    This code is a great example!

    Is there a way to update the existing information in the sql? I’ve noticed that when i run the script a second time, it only adds to the existing table.

    Thanks,
    -Steve

  54. swati Says:

    Sorry, I’m a newbye… I write all the code in the same module, but it doesn’t work because an error message say me that after “End” can be only a comment. May I write each Function in a module? Or a script? I can’t understand :/
    PAOLO

    Dim oConn As ADODB.Connection
    Private Sub ConnectDB()
    … … …
    End Sub

    Function esc(txt As String)
    esc = Trim(Replace(txt, “‘”, “\’”))
    End Function

    Dim rs As ADODB.Recordset
    Private Sub InsertData()
    … … …
    End Sub

  55. Dirk Says:

    Hi,

    I was searching a long time for a clear howto for using MySQL and Excel / vba. Here I found it.
    Inserting things works just great! But now I wanted to update ( or perhaps even delete ) rows in my table.
    Searching a lot already, but it will not work. I know I have to use the syntax “update table set collum1=x, collum2=y where collum3=z and collum4=q” somewhere, but can’t translate it in a working vba – language.
    Do you also have tutorials where those statements are described?

    Kind regards,
    Dirk

  56. purushottam Says:

    Thanks for help,
    I am getting error from here.

    Function esc(txt As String)
    esc = Trim(Replace(txt, “‘”, “\'”))
    End Function
    And now for the Sub procedure to perform data insertion:

    Dim rs As ADODB.Recordset
    Private Sub InsertData()
    Set rs = New ADODB.Recordset
    ConnectDB
    With wsBooks
    For rowCursor = 2 To 11
    strSQL = “INSERT INTO tutorial (author, title, price) ” & _
    “VALUES (‘” & esc(.Cells(rowCursor, 1)) & “‘, ” & _
    “‘” & esc(.Cells(rowCursor, 2)) & “‘, ” & _
    esc(.Cells(rowCursor, 3)) & “)”
    rs.Open strSQL, oConn, adOpenDynamic, adLockOptimistic
    Next
    End With
    End Sub

    Please Help me

  57. Paddy Says:

    I run your code but the line
    rs.Open strSQL, oConn, adOpenDynamic, adLockOptimistic
    doesnt seem to work
    Please Help

  58. Lightray Says:

    I’d echo the same error that others have reported. Namely:
    run-time error ‘-2147217887 (80040e21)’:
    ODBC driver does not support the requested properties.

    When clicking “Debug” from the dialog box, the following line in the code is highlighted:
    rs.Open strSQL, oConn, adOpenDynamic, adLockOptimistic

    I’m running mysql 5.5 on windows xp

  59. Moustafa Says:

    Thanks a lot a lot a lot, you saved my life 🙂

  60. agung Says:

    Very nice, thanks a lot…

  61. Nithin Says:

    Hi i am getting syntax error when i tried to run

    Dim oConn As ADODB.Connection
    Private Sub ConnectDB()
    Set oConn = New ADODB.Connection
    oConn.Open “DRIVER={MySQL ODBC 5.1 Driver};” & _
    “SERVER=localhost;” & _
    “DATABASE=Bugs;” & _
    “USER=root;” & _
    “PASSWORD=rootindia;” & _
    “Option=3”
    End Sub

    Function esc(txt As String)
    esc = Trim(Replace(txt, “‘”, “\'”))
    End Function
    And now for the Sub procedure to perform data insertion:

    Dim rs As ADODB.Recordset
    Private Sub InsertData()
    Set rs = New ADODB.Recordset
    ConnectDB
    With wsbooks
    For rowCursor = 2 To 11
    strSQL = “INSERT INTO tutorial (author, title, price) ” & _
    “VALUES (‘” & esc(.Cells(rowCursor, 1)) & “‘, ” & _
    “‘” & esc(.Cells(rowCursor, 2)) & “‘, ” & _
    esc(.Cells(rowCursor, 3)) & “)”
    rs.Open strSQL, oConn, adOpenDynamic, adLockOptimistic
    Next
    End With
    End Sub