SQL update statement in excel vba

[Rachel]

I am pulling my hair out with this one, any help would be great!

I have an excel spreadsheet that contains a user form, which updates a
sql server table. The sql server table has 4
fields that are the tables primary key (don't ask - my boss insists on
this much to my annoyance) The userform has 2 pages,
one for inserting records which works fine and the other page that
updates these records which again works most
of the time. My problem is when the user does not use the correct
primary key the sql code still runs but does not
say that the record has not been updated. How can I capture this in my
vba code? My excel vba update code is below:

Sub UpdateSQLDatabase()

Dim MyConnection As ADODB.Connection
Dim MySQL As String

Sheets("Upload Appointments").Visible = True
Sheets("Upload Appointments").Select

Set MyConnection = New ADODB.Connection

EmptyRows

MyConnection.Open "Provider=sqloledb;Data Source=test;Initial
Catalog=test;Integrated Security=SSPI;"

For i = 2 To Range("a400").End(xlUp).Row
MySQL = "UPDATE tbl_SourceData " & _
"SET AppDate ='" & Range("C" & i).Value & "', Outcome = '" &
Range("D" & i).Value & "', AppUpdated = '" & Range("Date").Value _
& "' WHERE Patient_ID =" & "'" & Range("A" & i).Value &
"'" _
& " AND Clinical_GP =" & "'" & Range("ClinGp").Value & "'"
_
& " AND Location = " & "'" & Range("Loc").Value & "'" _
& " AND RefDate = " & "'" & Range("B" & i).Value & "'"

MyConnection.Execute MySQL
Next i

MyConnection.Close

Set MyConnection = Nothing

End Sub


The 4 pk's are patient_ID, Clinical_Gp, Location and RefDate, the
problem arises when the user inputs an incorrect
refdate and I have no way of capturing that no rows were affected.

Any help would be greatly appreciated

Thank you
Rachel

 

[Bob Phillips]

Do a query against the table on those four values. If it returns a result,
go ahead and update, if not, throw back a warning.

 

[RB Smissaert]

I don't think it is necessary to do a select query first as the Execute
method has
a second argument, RecordsAffected that will be set by the Execute, so if it
is zero
then you know that no records were updated. So, you would do:

lUpdated as long

MyConnection.Execute MySQL, lUpdated, adExecuteNoRecords

and the value of lUpdated will tell if it was successful or not.
Note that the third argument here, adExecuteNoRecords will I think speed the
SQL up.

RBS

 

[RB Smissaert]

One further suggestion.
It may not be relevant as you are dealing with a small number of sheet rows,
but a way to speed it up would be
to take the SQL out of the loop, replace the variable values with question
marks and use the ADODB.Command
object with a parameter array. Your code would then be something like this
(not tested):

Sub UpdateSQLDatabase()

Dim i As Long
Dim MyConnection As ADODB.Connection
Dim cmdADO As ADODB.Command
Dim MySQL As String
Dim LR As Long
Dim arrRange
Dim lUpdated As Long

Sheets("Upload Appointments").Visible = True
Sheets("Upload Appointments").Select

Set MyConnection = New ADODB.Connection
Set cmdADO = New ADODB.Command

EmptyRows

MyConnection.Open "Provider=sqloledb;" & _
"Data Source=test;" & _
"InitialCatalog=test;" & _
"Integrated Security=SSPI;"

LR = Cells(400, 1).End(xlUp).Row
arrRange = Range(Cells(2, 1), Cells(LR, 4))

MySQL = "UPDATE tbl_SourceData " & _
"SET AppDate = ? , " & _
"Outcome = ?, " & _
"AppUpdated = ? " & _
"WHERE " & _
"Patient_ID = ? AND " & _
"Clinical_GP = '" & Range("ClinGp").Value & "' AND " & _
"Location = '" & Range("Loc").Value & "' AND " & _
"RefDate = ?"

With cmdADO
Set .ActiveConnection = MyConnection
.CommandType = adCmdText
.CommandText = MySQL
End With

For i = 1 To UBound(arrRange)
cmdADO.Execute lUpdated, _
Array(arrRange(i, 3), _
arrRange(i, 4), _
arrRange(i, 1), _
arrRange(i, 2)), _
adExecuteNoRecords
If lUpdated = 0 Then
'handle your UPDATE failure here
End If
Next i

MyConnection.Close

Set MyConnection = Nothing

End Sub


RBS

 

[Rachel]

One further suggestion.
It may not be relevant as you are dealing with a small number of sheet rows,
but a way to speed it up would be
to take the SQL out of the loop, replace the variable values with question
marks and use the ADODB.Command
object with a parameter array. Your code would then be something like this
(not tested):

Sub UpdateSQLDatabase()

  Dim i As Long
  Dim MyConnection As ADODB.Connection
  Dim cmdADO As ADODB.Command
  Dim MySQL As String
  Dim LR As Long
  Dim arrRange
  Dim lUpdated As Long

  Sheets("Upload Appointments").Visible = True
  Sheets("Upload Appointments").Select

  Set MyConnection = New ADODB.Connection
  Set cmdADO = New ADODB.Command

  EmptyRows

  MyConnection.Open "Provider=sqloledb;" & _
                    "Data Source=test;" & _
                    "InitialCatalog=test;" & _
                    "Integrated Security=SSPI;"

  LR = Cells(400, 1).End(xlUp).Row
  arrRange = Range(Cells(2, 1), Cells(LR, 4))

  MySQL = "UPDATE tbl_SourceData " & _
          "SET AppDate = ? , " & _
          "Outcome = ?, " & _
          "AppUpdated = ? " & _
          "WHERE " & _
          "Patient_ID = ? AND " & _
          "Clinical_GP = '" & Range("ClinGp").Value & "' AND " & _
          "Location = '" & Range("Loc").Value & "' AND " & _
          "RefDate = ?"

  With cmdADO
    Set .ActiveConnection = MyConnection
    .CommandType = adCmdText
    .CommandText = MySQL
  End With

  For i = 1 To UBound(arrRange)
    cmdADO.Execute lUpdated, _
                   Array(arrRange(i, 3), _
                         arrRange(i, 4), _
                         arrRange(i, 1), _
                         arrRange(i, 2)), _
                         adExecuteNoRecords
    If lUpdated = 0 Then
      'handle your UPDATE failure here
    End If
  Next i

  MyConnection.Close

  Set MyConnection = Nothing

End Sub

RBS

















- Show quoted text -

Thank you for this, it worked great