ADODB Insert Query - Modify to Update instead ?


Well-known Member
Nov 16, 2016
i have the following which works fine:
but im wondering how i can modify this to UPDATE the existing record if the data2 value already exists in the table (Column2)? To prevent duplicate data.

Column1 in the table is Auto Incrementing primary key
Column2 - i want this to stay unique i.e if same value is attempted to be inserted - instead update previous

Sub insertSQL()

Application.ScreenUpdating = False

   Set cnn = CreateObject("ADODB.Connection")
    cnn.Open "Driver={MySQL ODBC 5.1 Driver};SERVER=xxx;DATABASE=xxx;UID=xxx;PWD=xxx;PORT=3306;"

    Rw = 2
    For i = 2 To Rw
    data2 = Sheet3.Cells(i, 2).Value
    data3 = Sheet3.Cells(i, 3).Value
    data4 = Sheet3.Cells(i, 4).Value
    data5 = Sheet3.Cells(i, 5).Value
    data6 = Sheet3.Cells(i, 6).Value
    data7 = Sheet3.Cells(i, 7).Value
    MYSQL = "INSERT INTO TABLE1 (Column2, Column3, Column4, Column5, Column6, Column7) VALUES ('" & data2 & "','" & data3 & "','" & data4 & "','" & data5 & "','" & data6 & "','" & data7 & "');"
    cnn.Execute MYSQL
    Next i
    Set cnn = Nothing

Application.ScreenUpdating = True

End Sub
appreciate any help

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Tom Schreiner

Well-known Member
Mar 18, 2002
Does this table contain many records? If not, you could create an ADODB.Recordset, loop through, checking for a match of data2 in any record. If there is a match, update the record. If there is not a match on any data2 field, you loop to EOF, AddNew. Make sense?

Else, you could query prior to your existing insert, checking for the existence of a record that matches data2. If there's a match, update the record, else INSERT.


Well-known Member
Oct 30, 2011
  • For starters we need to determine if the record exists; SQL has an EXISTS statement but it is not supported on all situations. See example below for an alternative method.
  • Next step will be the updating, logical candidate is the UPDATE clause.

Sub main()
MsgBox Jumbo("relation", "Sales", "30009")
End Sub

Public Function Jumbo$(sn$, fld$, t$)
Dim Conn As New ADODB.Connection, mrs As New ADODB.Recordset
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.FullName & _
";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
mrs.Open "SELECT * From [" & sn & "$] WHERE " & fld & " =" & t, Conn
If mrs.EOF Then
    Jumbo = "not found"
    Jumbo = "found"
End If
End Function


Well-known Member
Nov 16, 2016
@Tom Schreiner

Table only contains around 1000 records currently but could soon be 20000+

Is there any advantage to using ADODB.recordset instead of direct query like I've done in my post?
It's something I've never used
Last edited:


Well-known Member
Oct 30, 2011
From the Internet:

The Connection object is the top-level object in the ADO hierarchy. It represents the connection made to the data source through the OLE DB data provider, and handles all communication between your solution and a data source. Unlike the Connection object in DAO's ODBCDirect or in Remote Data Objects (RDO), you do not have to instantiate an ADO Connection object before you create other objects such as the Command or Recordset object. This lets you create temporary objects and associate them with a Connection object as needed.

The ADO Connection Object is used to create an open connection to a data source. Through this connection, you can access and manipulate a database.
If you want to access a database multiple times, you should establish a connection using the Connection object. You can also make a connection to a database by passing a connection string via a Command or Recordset object. However, this type of connection is only good for one specific, single query.

You open and close a Connection object by using the Open and Close methods. Additionally, Connection objects provide transaction support by using the BeginTrans, CommitTrans, and RollbackTrans methods. Use transactions when you want to save or cancel a series of changes made to the source data as a single unit. For example, if you are transferring money between two accounts, you want to make sure that both debiting and crediting operations occur. By making these changes within a transaction, you ensure that either all or none of the changes goes through.

The Recordset object represents a set of records returned from a query, and a cursor into those records.
You can open a Recordset object without explicitly opening a Connection object by passing a connection string to the Recordset object's Open method. However, if you create and open a Connection object, you can open multiple Recordset objects on the same connection. The Recordset object contains a Fields collection that contains Field objects, each of which represents a single column of data within a Recordset object.

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics