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

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.

--
__________________________________
HTH

Bob

"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

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



"Bob Phillips" wrote in message
...
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.

--
__________________________________
HTH

Bob

"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

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

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