VBA + MySQL: Checking For Duplicate Key Before Data Insertion

This article has been long overdue; so I apologize to newbie who orignally asked how to do duplicate key checking before inserting Excel data into a MySQL database.

I got a few minutes of free time today, so I decided to draft a sample workbook that will demonstrate this process. You can download the sample workbook here. Standard disclaimer: This file has been found to be virus-free when uploaded. However, you are highly recommended to scan the file again with an up to date anti-virus software before opening it.

Also, please make sure you’ve got MySQL Connector/ODBC 5.1 installed on your computer.

Once you’ve downloaded the workbook, start the MySQL service and log on. Select a database where you have priviledges to create tables and insert data. Next, run the following SQL statement inside the selected database to create the table we will use in this exercise:

CREATE TABLE `maillist` (
	`did` INT NOT NULL ,
	`firstname` VARCHAR( 255 ) NOT NULL ,
	`surname` VARCHAR( 255 ) NOT NULL ,
	`email` VARCHAR( 255 ) NOT NULL ,
	UNIQUE (
		`did`
	)
) ENGINE = InnoDB

Once you’ve done that, extract the zip I linked earlier. It should contain an Excel workbook called MySQL-Key-Check.xls. Extract it somewhere easily accessable. Before opening the workbook, please make sure that Excel’s macro security setting is set to Medium. Already there? Good! Now open the file. At the security warning window, click on the Enable Macros button and you should be ready to go!

The first sheet you’ll see is named Menu and will look as follows:

Menu

The second sheet will contain a list of data categorized into did (data id), firstname, surname and email. This is the data that we will insert into MySQL.

Now fill in the Database Settings data and click on the Test Database button.

If an error pops up, verify that:

  • The MySQL database service is indeed running
  • You’ve entered the correct hostname (in most cases this should be localhost), username, password and database info

If you receive a Database Connection Test Successful! message; you’re good to go, and the Insert Contacts Data button should now be enabled. Go ahead and click it!

After a while, you’ll be shown the following prompt:

Prompt

You’ll also see that the Data sheet is now selected and the duplicate IDs have all been highlighted in red.

To see how the magic is done, fire up the Visual Basic Editor by pressing Alt-F11 and select the shtMenu (Menu) object in Project Explorer. In its code window, scroll down until you see the cmdInsertData_Click() sub.

You’ll see that the first SQL statement performs the key check. If the key is already in use, it highlights the did column red and goes to the next line. If the key is not in use, an SQL statement to perform the data insertion is constructed and executed.

This process is repeated until an empty did column is found. When this stage is reached, a prompt reports the number of data rows inserted along with the ones skipped due to the did key being already used.

With some minor modifications you can even do simple validations before inserting the data.

If you have questions regarding this mini-tutorial, please let me know by commenting below.

29 responses to “VBA + MySQL: Checking For Duplicate Key Before Data Insertion”.

  1. newbie Says:

    Awesome~! Thanks alot for the code…its really good!!

  2. Site Admin Azmeen Says:

    Glad the article helped you 🙂

    If you have any other questions just let me know.

  3. newbie Says:

    May I ask how about update for the data?can it check the duplicate as well when the data is updated?

  4. Site Admin Azmeen Says:

    It’s the same method, just use an UPDATE SQL statement instead of INSERT.

  5. newbie Says:

    I have try the update as well..but it seems that the code rejected the whole data as long as the unique did is duplicated…can we just update the data for the firstname, surname and email?in other words it will reject only if we make changes to the primary key…really appreciate your help here

  6. Site Admin Azmeen Says:

    That’s the whole point of unique IDs; so of course it cannot be duplicated!

    When updating, you use the ID as the search term you want to match; eg. UPDATE table SET something='Some Value' WHERE id='theID'.

    You rarely ever need to change the value of a unique ID. But if you do, you must make sure it isn’t already in use.

  7. newbie Says:

    Thanks 🙂

  8. march8 Says:

    I saw this great post of yours!its good! may i ask u sum questions regarding excel? i hav an excel that have a few columns namely name n email…do u know how to do a vba that grab the email from outlook address book when the name is inserted?n thus show the name n email?ur opinion is highly value!

  9. Site Admin Azmeen Says:

    Hi march8,

    I’m unclear on what you actually want to achieve.

    You mentioned that the Excel sheet already has name and email data; yet you want to grab the email from Outlook’s address book.

    If you already have the email address on the sheet, why would you want to grab it again from the address book?

  10. march8 Says:

    Sorry….I just bump into a website showing this.. mayb you can get a clear understanding (www.ozgrid.com/forum/showthread.php?t=94516)…but the thing is i encounter an error here – “/ou…/cn…/recipients… ” how do i fix this error?and can i change the coding so that when full name is type in it will get the email(for here when the display name is match then the email address will only pop out)..thanks for you valuable opinion!

  11. Site Admin Azmeen Says:

    That involves playing with Outlook’s Object Model. I’m afraid I can’t be of any help in this area.

    I don’t even use Outlook 😛

  12. shaw Says:

    i have a code that extract data from the database and list it on the excel sheet but how do i highlight in red for this SQL Statement – If data4=data5 then highlight this row red? Means the excel sheet will list out all the datas and those with data4=data5 will be highlighted in red. The code is as shown below. Really appreciate your help here!

    Dim SQLStr As String
    Dim Cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    Set Cn = New ADODB.Connection
    Cn.Open “DRIVER={MySQL ODBC 5.1 Driver};” & _
    “SERVER=server;” & _
    “DATABASE=database;” & _
    “USER=user;” & _
    “PASSWORD=password;” & _
    “Option=3”

    Range(“a6:bb600”).ClearContents

    SQLStr1 = “SELECT data1, data2, data3, data4, data5 from table”

    rs.Open SQLStr1, Cn, adOpenStatic
    ‘rs.Open SQLStr, oConn, adOpenDynamic, adLockOptimistic

    Dim myArray()

    myArray = rs.GetRows()

    Column = UBound(myArray, 1)
    rader = UBound(myArray, 2)

    For K = 0 To Column

    Range(“A6”).Offset(0, K).Value = rs.Fields(K).Name
    For r = 0 To rader
    Range(“A6”).Offset(r + 1, K).Value = myArray(K, r)
    Next
    Next

    rs.Close
    Set rs = Nothing
    Cn.Close
    Set Cn = Nothing

  13. shaw Says:

    Hi Site admin any response on this issues!? Thanks again for the help!

  14. Site Admin Azmeen Says:

    hi shaw,

    Sorry for the late reply. Basically you just need to adjust your for loop to the following:

    For K = 0 To Column
    Range("A6").Offset(0, K).Value = rs.Fields(K).Name
    For r = 0 To rader
    Range("A6").Offset(r + 1, K).Value = myArray(K, r)
    If K = 4 Then
    If Range("A6").Offset(r + 1, 3).Value = Range("A6").Offset(r + 1, 4).Value Then
    Range(Range("A6").Offset(r + 1, 0), Range("A6").Offset(r + 1, 4)).Interior.Color = RGB(255, 0, 0)
    End If
    End If
    Next
    Next

  15. shaw Says:

    GREAT!!thanks alot for the help!But i have one more question that i really cant figure out and need some help…i have a speadsheet that is connected to the database and i have a column( from table 1)let’s say its known as A and in the table1 the value of A is 3…so in my excel i fill in the ID number(primary key) for A = 2…how do i make the value in the column A so that it show value 5 (which is 2 + 3 =5)…I used update statement “UPDATE table1 SET A= ‘” & (Trim(.Cells(rowctr, 2).Value)) & “‘ WHERE ID_NUMBER = ‘” & (Trim(.Cells(rowctr, 1).Value)) & “‘ ” …but i know this just update and change the value from 3 to 2…but i want to add up the value…is there anyway i can do that!? Your help is really really much appreciated!! Thanks alot in advance!

  16. Site Admin Azmeen Says:

    Try the following:

    strSQL = "UPDATE table1 SET A= '" & Trim(Str(Int(Trim(.Cells(rowctr, 2).Value)) + Int(Trim(.Cells(rowctr, 1).Value)))) & "' WHERE ID_NUMBER = '" & (Trim(.Cells(rowctr, 1).Value)) & "'"

    Of course the above will only work assuming that the value in columns A and B are integers.

  17. shaw Says:

    I just try out but it prompt error said “type mismatch” …the value for column A in database in an integer then the value that is being fielded in the excel is an integer too…i jus wanted to add in the value with the one that is in database…

  18. Site Admin Azmeen Says:

    Without actually seeing the data on your spreadsheet, it’s hard for me to construct the proper SQL for you.

    Try this:
    strSQL = "UPDATE table1 SET A=" & Trim(Str(Trim(.Cells(rowctr, 2).Value) + Trim(.Cells(rowctr, 1).Value))) & " WHERE ID_NUMBER=" & Trim(Str(Trim(.Cells(rowctr, 1).Value)))

  19. shaw Says:

    i am sorry site admin:- below is the database for table1

    ID number A
    1155 3

    Here is the spreadsheet data :-

    ID number A (button to click update)
    1155 2

    After i clicked update i just want the database to show this:-

    ID number A
    1155 5 (where 3+2)

  20. Site Admin Azmeen Says:

    OK, that’s better… try this:

    strSQL = "UPDATE table1 SET A=A + " & Trim(Str(Trim(.Cells(rowctr, 2).Value))) & " WHERE ID_NUMBER=" & Trim(Str(Trim(.Cells(rowctr, 1).Value)))

  21. shaw Says:

    Excellent! THANKSSSS!!! I used the one u give me and i took of the “Str” >>> UPDATE table1 SET A= A + ‘” & (Trim(.Cells(rowctr, 2).Value)) & “‘ WHERE ID_NUMBER = ‘” & (Trim(.Cells(rowctr, 1).Value)) & “‘ and it WORKS WONDERS! really thanks alot for your time and effort! appreciate it!

  22. Site Admin Azmeen Says:

    You’re welcome. Glad I could help 🙂

  23. shaw Says:

    Hi Site Admin i am sorry to bother you again but i have a question how do i prompt an error message when i get a negative value? i got a query below and i want it to prompt an error message when data1 is negative is it possible?

    strsql = “UPDATE table1 SET data1 = data2 – data3 WHERE ID_Number = ‘” & (Trim(.Cells(rowctr, 1).Value)) & “‘ ”

    Thanks in advance!

  24. Site Admin Azmeen Says:

    You should do the checking of data1 value before even constructing the SQL UPDATE statement.

    Just use a MsgBox to prompt your error message.

  25. shaw Says:

    Thanks but there is a problem…i forgotten to include something in the sql..the sql should look like this “UPDATE table1 SET data3 = data3 + ‘” & (Trim(.Cells(rowctr, 2).Value)) & “‘ , data1 = data2 – data3 WHERE ID_NUMBER = ‘” & (Trim(.Cells(rowctr, 1).Value)) & “‘ ” … so the value of data1 whether it is negative or not depended on the value of data3…in short when data3 is updated data1 will do the calculations based on the updated data3…anyway we can do that!?thanks!

  26. Site Admin Azmeen Says:

    The answer is still the same. Whatever your calculations, just perform the necessary checking.

    Do a SELECT on the particular row first. Then add or minus the user input and see the results.

    Just a simple IF… THEN… ELSE would solve the problem.

  27. shaw Says:

    thanks for the suggestion!

  28. Swift Says:

    Hi Azmeen,

    What to do if you want to update a row, only wenn it is a duplicate one?
    for example:
    I have a tabel with collums a, b, c and d. a is unique.
    There is already a row where a=1, b=2010, c=1 and d=0.
    If I want to insert a row with a=1, b=2010, c=0 and d=1, it should update only collum d in the existing row.
    But! If I have a = 1, b = 2010, c = 1, d = 0 it should update collum c in the existing row so we get a=1, b=2010, c=2, d=0.
    Can this be done? Perhaps with on duplicate key update or so?

    Greetings,
    Swift

  29. Viskey Says:

    Hello,

    I am trying to use your code but with no success. Instead of multiple rows in my data sheet. I have just one row which updates periodically. It is then written into MySQL. I just want to make sure updated row is not written if its a duplicate. I have one column in my dbase which is unique.

    I am not an expert Vb coder. So,any help would be great.

    Thanks a lott