Update SQL problem

[Guest]

I have the following SQL statement that is supposed to replace a path in a
table. I have been trying to get it for some time now and thought it about
time to post it. It appears that the vbTextCompare portion of the strPos is
causing the problem. (Maybe something else also, I don' t know) Thank you for
your help.

Dim strSQLUpdateIt As String
Dim strStarting As String, strOldComa As String, strNewComa As
String
Dim strPos As String, strComa As String, strWhere As String
'get length of old string
Dim intLength As Integer
intLength = Len(strOld)

strComa = ","
strStarting = "Update tblPlants Set PicPath = Replace(PicPath,"
strOldComa = "'" & strOld & "'" & strComa
strNewComa = "'" & strNew & "'" & strComa
strPos = "1," & intLength & strComa & vbTextCompare & ")"
strWhere = "WHERE PicPath Is Not Null"
strSQLUpdateIt = strStarting & strOldComa & strNewComa & strPos
& strWhere
DoCmd.RunSQL strSQLUpdateIt

This is the text I get when I view the SQL statment in the intermediate
window:
Update tblPlants Set PicPath =
Replace(PicPath,'R:\Graphics\JPEG's\','E:\Graphics\JPEG's\',1,19,1)WHERE
PicPath Is Not Null

 

[Gary Walter]

I have the following SQL statement that is supposed to replace a path in a
table. I have been trying to get it for some time now and thought it about
time to post it. It appears that the vbTextCompare portion of the strPos
is
causing the problem. (Maybe something else also, I don' t know) Thank you
for
your help.

Dim strSQLUpdateIt As String
Dim strStarting As String, strOldComa As String, strNewComa As
String
Dim strPos As String, strComa As String, strWhere As String
'get length of old string
Dim intLength As Integer
intLength = Len(strOld)

strComa = ","
strStarting = "Update tblPlants Set PicPath = Replace(PicPath,"
strOldComa = "'" & strOld & "'" & strComa
strNewComa = "'" & strNew & "'" & strComa
strPos = "1," & intLength & strComa & vbTextCompare & ")"
strWhere = "WHERE PicPath Is Not Null"
strSQLUpdateIt = strStarting & strOldComa & strNewComa & strPos
& strWhere
DoCmd.RunSQL strSQLUpdateIt

This is the text I get when I view the SQL statment in the intermediate
window:
Update tblPlants Set PicPath =
Replace(PicPath,'R:\Graphics\JPEG's\','E:\Graphics\JPEG's\',1,19,1)WHERE
PicPath Is Not Null

Hi Billy,

You may have other problems, but I suspect
the real problem is the single quote in the paths, i.e.,

"...\JPEG's\"

try (on backup copy first)...

Dim strSQL As String

'cover behind if single quotes in old/new paths
'i.e., replace single quote w/ 2 single quotes
strNew = Replace(strNew, "'", "''", 1, -1, 1)
strOld = Replace(strOld, "'", "''", 1, -1, 1)

'Debug.Print "new: " & strNew
'Debug.Print "old: " & strOld

strSQL = "UPDATE tblPlants SET " _
& "PicPath = Replace(PicPath, '" _
& strOld & "', '" & strNew & "',1,-1,1) " _
& "WHERE PicPath IS NOT NULL"

'Debug.Print strSQL

'(make sure reference set for DAO)
CurrentDb.Execute strSQL, dbFailOnError


the "19" was saying "replace only 19 occurences
of strOld in PicPath" which did not make sense to me?

you don't say what version of Access, but if 2000,
you cannot use Replace function in query -- you have
to create a wrapper function to use in query.

good luck,

gary

 

[Guest]

Gary,

I am using 2000 and ADO. My understanding was the 19 in the statement was to
replace the first 19 characters of the old path starting at position 1. Am I
wrong?
Also, what do you mean as a wrapper and and example if possible. Thank you.

 

[Gary Walter]

Hi Billy,

What is "Count"
----------------

Replace Function (adapted from Access Help)

Syntax

Replace(expression, find, replace, start, count, compare)

The Replace function syntax has these named arguments:

Part Description
---------- ------------------------
expression Required. String expression
containing substring to replace.
Find Required. Substring being searched for.
Replace Required. Replacement substring.
Start Optional. Position within expression
where substring search is to begin.
If omitted, 1 is assumed.
Count Optional.
Number of substring substitutions to perform.
If omitted, the default value is -1,
which means make all possible substitutions.
Compare Optional. Numeric value indicating
the kind of comparison to use
when evaluating substrings.

{as I understand it...}

If you used "Count" = 1, then it will replace only the *first* "Find"
it found in "expression" with "Replace"
If you used "Count" = 2, then it will replace only the *first two* "Find"
it found in "expression" with "Replace"

If you used "Count" = -1, then it will replace *all* "Find"
it found in "expression" with "Replace"

////////////////////////////
2000 (may) need wrapper function for Replace()
-----------------------------

{If Replace() function works in your query, ignore following...
originally, Replace() function would not work in A2K queries,
but maybe it was corrected in updates}

Start a new module (or open an existing one).

Copy following code into module

'** start code **
'one possible wrapper function for function Replace
'to be used in queries for Access 2000
Public Function fReplace(pExp As String, _
pFind As String, _
pstrReplace As String) As String
fReplace = Replace(pExp, pFind, pReplace,1,-1,1)
End Function
'** end code **

Save module, run Debug/Compile from top menu
to verify copy and paste worked.
(if new module, do *not* name new module "fReplace"
....i.e., name module different than function name)

then, in queries, use fReplace() instead of Replace().

for example...

strSQL = "UPDATE tblPlants SET " _
& "PicPath = fReplace(PicPath, '" _
& strOld & "', '" & strNew & "') " _
& "WHERE PicPath IS NOT NULL"

//////////////////////////////////
ADO
------

the code

CurrentDb.Execute strSQL, dbFailOnError

requires DAO reference. I find the above code
to best meet my needs when running an action
or data-definition query.

You can use ADO for all other things, but still set
reference for DAO for just this code....

or continue to use DoCmd.RunSQL .. w/o DAO...

your choice....

good luck,

gary

 

[Tony Toews [MVP]]

DoCmd.RunSQL strSQLUpdateIt

I prefer, if DAO, to use Currentdb.Execute strSQL,dbfailonerror
command instead of docmd.runsql. For ADO use
CurrentProject.Connection.Execute strCommand, lngRecordsAffected,
adCmdText

If you're going to use docmd.setwarnings make very sure you put the
True statement in any error handling code as well. Otherwise weird
things may happen later on especially while you are working on the
app. For example you will no longer get the "Do you wish to save your
changes" message if you close an object. This may mean that unwanted
changes, deletions or additions will be saved to your MDB.

Also performance can be significantly different between the two
methods. One posting stated currentdb.execute took two seconds while
docmd.runsql took eight seconds. As always YMMV.

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/