How to tell if an update query actually changed anything

  • Thread starter Thread starter Southern at Heart
  • Start date Start date
S

Southern at Heart

Below is a sample I use to run an update query. Is there a way to tell if
any of the fields were actaully changed? In this case, a carriage return was
removed from the end of the string. ...is there a way to tell if this changed
any fields? That way, if if did, I could run it again until no change to the
[Phone] field was made...
thanks,
Southern@Heart

strSql = "UPDATE Name SET Name.Phone = Left(Phone,Len(Phone)-2) WHERE
(((Name.Phone) Like ""*"" & Chr(13) & Chr(10)));"
DoCmd.RunSQL strSql
 
Below is a sample I use to run an update query. Is there a way to tell if
any of the fields were actaully changed? In this case, a carriage return was
removed from the end of the string. ...is there a way to tell if this changed
any fields? That way, if if did, I could run it again until no change to the
[Phone] field was made...
thanks,
Southern@Heart

strSql = "UPDATE Name SET Name.Phone = Left(Phone,Len(Phone)-2) WHERE
(((Name.Phone) Like ""*"" & Chr(13) & Chr(10)));"
DoCmd.RunSQL strSql
Click to expand...

Not with RunSQL as far as I know, but an alternative way of executing the
query will do it:

Dim strSQL As String
Dim db As DAO.Database
Dim qd As DAO.Querydef
Set db = CurrentDb
strSql = "UPDATE Name SET Name.Phone = Left(Phone,Len(Phone)-2) WHERE" _
& " (((Name.Phone) Like ""*"" & Chr(13) & Chr(10)));"
Set qd = db.CreateQuerydef("", strSQL) ' create an unnamed, unsaved query
qd.Execute, dbFailOnError
Debug.Print qd.RecordsAffected
 
Thanks. I had to take out the dbFailonError. I don't know what it does
exactly, but it gave me an error something like (wrong number of arguments)
or something.
Now I should be able to loop until no more trailing returns are found with:
If qd.RecordsAffected<>0 then goto StartOver
thanks.


John W. Vinson said:
Below is a sample I use to run an update query. Is there a way to tell if
any of the fields were actaully changed? In this case, a carriage return was
removed from the end of the string. ...is there a way to tell if this changed
any fields? That way, if if did, I could run it again until no change to the
[Phone] field was made...
thanks,
Southern@Heart

strSql = "UPDATE Name SET Name.Phone = Left(Phone,Len(Phone)-2) WHERE
(((Name.Phone) Like ""*"" & Chr(13) & Chr(10)));"
DoCmd.RunSQL strSql
Click to expand...

Not with RunSQL as far as I know, but an alternative way of executing the
query will do it:

Dim strSQL As String
Dim db As DAO.Database
Dim qd As DAO.Querydef
Set db = CurrentDb
strSql = "UPDATE Name SET Name.Phone = Left(Phone,Len(Phone)-2) WHERE" _
& " (((Name.Phone) Like ""*"" & Chr(13) & Chr(10)));"
Set qd = db.CreateQuerydef("", strSQL) ' create an unnamed, unsaved query
qd.Execute, dbFailOnError
Debug.Print qd.RecordsAffected
Click to expand...
 
The following should also work thus not requiring a QueryDef.

Dim strSQL As String
Dim db As DAO.Database
Set db = CurrentDb
strSql = "UPDATE Name SET Name.Phone = Left(Phone,Len(Phone)-2) WHERE"
_
& " (((Name.Phone) Like ""*"" & Chr(13) & Chr(10)));"
db.execute(strSQL, dbfailonerror)
Debug.Print db.RecordsAffected

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
The following should also work thus not requiring a QueryDef.
Click to expand...

<do'h!>

Thanks Tony. I guess I'd seen that and not noticed it - didn't realize that
the database object has an execute method!
 
Back
Top