CODEWITHSUNDEEP
sql-serverexcelvbainsertkey
user6089076
user6089076

Reputation:

INSERT or UPDATE a record in SQL from VBA

I have put together some code in VBA which exports data from an excel sheet to a Data Base, this code (below) currently just inserts the data, but I would like to insert it based on a key (the date). So if the date already exists then in the DB the record associated with that date is replaced, if the date does not already exist, then it is inserted as usual.

Current code:

Sub SendData(AB As String, CD As String, EF As String, GH As String, IJ As String, KL As Double, MN As Double, PQ As Double, RS As Double)

Dim Date As String, Entity As String, area As String, unit As String, name As String, surname As String
Dim day As Double, weekly As Double, month As Double, year As Double

Dim objConn As ADODB.Connection
Set objConn = New ADODB.Connection
objConn.ConnectionString = "Provider=SQLOLEDB;Data Source=source;Initial Catalog=Title;Integrated Security=SSPI"
objConn.Open
Set objRec = New ADODB.Recordset


Date = Format(Range("date").Value, "YYYY-MM-DD")
Entity = AB
area = CD
unit = EF
name = GH
surname = IJ
daily = KL
weekly = MN
month = PQ
year = RS
    StrSQL = "insert into table_name values ('" & Date & "', '" &  Entity & _
              "','" & area & "','" & unit & "','" & name & "','" & surname & "'," & daily & "," & weekly & "," & month & "," & year & ")"

Set objRec = objConn.Execute(StrSQL)

objConn.Close
Set objConn = Nothing
end sub

I am a bit confused where to place the key... Thanks

Upvotes: 0

Views: 2788

Answers (3)

ASH
ASH

Reputation: 20342

For one thing, I would change that 'Date' to something like 'TheDate', or some other descriptive, but non-reserved kind of word. Also, insert is insert and update is update. The Update (not insert) should be something like this:

StrSQL = "UPDATE table_name SET values ('" & Date & "', '" &  Entity & _
              "','" & area & "','" & unit & "','" & name & "','" & surname & "'," & daily & "," & weekly & "," & month & "," & year & ")
WHERE " & sWhere

Or, consider using the MERGE clause.

Upvotes: 0

brainac
brainac

Reputation: 410

You can do something like

StrSQL = "SELECT * FROM table_name"
objRec.Open StrSQL, objConn, , adLockOptimistic
objRec.Find "[date_value] = #" & Date & "#",,,adBookmarkFirst
If objRec.EOF Then
    objRec.AddNew "[date_value] = #" & Date & "#"
End If
objRec![Entity] = Entity
'repeat for all required fields
objRec.Update
objRec.Close

I'm not 100% sure there is no minor error, as I "translated" it from my own working code, but general idea and instructions should be correct. This way you leverage recordset mechanics. I'm not sure about performance, maybe somebody more advanced will criticize it for being slow, but it works for me.

Upvotes: 1

Nathan_Sav
Nathan_Sav

Reputation: 8531

Look at sql joins you can do a join for the update and then another for the insert, if you are doing an entire range, if not use something like select count(ID) from [table_name] where [date_value]='your date' and use an if statement based on the resultant recordsets recordcount or do a select [date_value] from [table_name] of the destination table at the start of the proc in a separate rst and use .find to see if it's there

Upvotes: 0

Related Questions