SQL statement no generating errors...

[Brad Pears]

I have some code that updates a record in a table. I am using an "on error"
clause expecting that if there is no row to update, it would go through the
"on error" routine and the code there then "inserts" the correct record into
the table. However, obviously, updating a row that does not yet exist in the
table does not return any SQL errors. Should it be?? If not, is there a way
to determine if the number of rows being affected by the update is >0
without first having to do a "select" on the table to see if the row I want
to update is there? (which would be the resolution to the problem unless
there is an easier way...)

Thanks,

Brad

 

[Dirk Goldgar]

I have some code that updates a record in a table. I am using an "on
error" clause expecting that if there is no row to update, it would
go through the "on error" routine and the code there then "inserts"
the correct record into the table. However, obviously, updating a row
that does not yet exist in the table does not return any SQL errors.
Should it be??

No. Having no records that meet the query's criteria is not considered
to be an error.

If not, is there a way to determine if the number of
rows being affected by the update is >0 without first having to do a
"select" on the table to see if the row I want to update is there?
(which would be the resolution to the problem unless there is an
easier way...)

Yes. Check the RecordsAffected property of the Database or Workspace
object:

Dim db As DAO.Database

Set db = CurrentDb
With db

.Execute "UPDATE MyTable WHERE MyField='foo'", _
dbFailOnError

If .RecordsAffected = 0 Then
MsgBox "No records were harmed in " & _
"the making of this example."
End If

End With

 

[Gijs Beukenoot]

Brad Pears drukte met precisie uit :

I have some code that updates a record in a table. I am using an "on error"
clause expecting that if there is no row to update, it would go through the
"on error" routine and the code there then "inserts" the correct record into
the table. However, obviously, updating a row that does not yet exist in the
table does not return any SQL errors. Should it be?? If not, is there a way
to determine if the number of rows being affected by the update is >0 without
first having to do a "select" on the table to see if the row I want to update
is there? (which would be the resolution to the problem unless there is an
easier way...)

Thanks,

Brad

By use of what code are you inserting? If you're using a
dbobject.execute statement, you can check the dbobject.recordsaffected
count.

 

[Brad Pears]

Thanls for that!! Much appreciated.

No. Having no records that meet the query's criteria is not considered
to be an error.


Yes. Check the RecordsAffected property of the Database or Workspace
object:

Dim db As DAO.Database

Set db = CurrentDb
With db

.Execute "UPDATE MyTable WHERE MyField='foo'", _
dbFailOnError

If .RecordsAffected = 0 Then
MsgBox "No records were harmed in " & _
"the making of this example."
End If

End With

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Brad Pears]

Perfect. Thanks for your response!

Brad Pears drukte met precisie uit :

By use of what code are you inserting? If you're using a dbobject.execute
statement, you can check the dbobject.recordsaffected count.