0

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

Improve this question
8
  • 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. Commented Feb 1, 2018 at 12:16
  • How is that possible from VBA though? Commented Feb 1, 2018 at 12:19
  • It's SQL so the same way you are doing. Do some research on .execute and .openrecordset and what you are actually doing in your code Commented Feb 1, 2018 at 12:21
  • SO add it in StrSQL? Commented Feb 1, 2018 at 12:28
  • If you're adding records one by one, consider using ADODB.Recordset. In recordset you could search for date key, then add new or update existing one, depending on the result of search. Commented Feb 1, 2018 at 13:46

3 Answers 3

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks brainac, that is really helpful I twill have a play with my code!
0

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

Improve this answer

3 Comments

SO I should write the code in StrSQL as I would in an SQL query?
MrNemo looks like @braniac has done it for you. :o)
indeed I am very grateful!
0

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.

Improve this answer

1 Comment

how would the Where clause work here? you need sWhere = where, so would that require sWhere = ('" & Date & "') for example?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.