On Dec 3, 5:23*pm, "RB Smissaert"
wrote:
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" wrote in message

...



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- Hide quoted text -

- Show quoted text -

Thank you for this, it worked great