ADODB Insert Query - Modify to Update instead ?

JumboCactuar

Well-known Member
Joined
Nov 16, 2016
Messages
661
Hi,
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


Code:
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
    
    cnn.Close
    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
Joined
Mar 18, 2002
Messages
6,867
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.
 

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
3,882
  • 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.

Code:
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"
Else
    Jumbo = "found"
End If
mrs.Close
Conn.Close
End Function
 

JumboCactuar

Well-known Member
Joined
Nov 16, 2016
Messages
661
@Tom Schreiner
@Worf

Thankyou
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:

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
3,882
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

Threads
1,090,150
Messages
5,412,752
Members
403,443
Latest member
Haves1979

This Week's Hot Topics

Top