How to change path name for field in multiple records?

[Don]

I had to move my photo database to a new folder.

The paths for the photos were all similar to:

\\Druma701va16100\forestry\Databases\NRMU Photo
Database\14704210_1SPre.jpg

Now there is a new folder called Tools that exists smack dab in the
middle of the file path.

How do I change all the file path names using a query to look like
this:

\\Druma701va16100\forestry\Tools\Databases\NRMU Photo
Database\14704210_1SPre.jpg

 

[Douglas J. Steele]

Run an Update query along the lines of:

UPDATE MyTable
SET MyField = Replace(MyField, "\\Druma701va16100\forestry\Databases\NRMU
Photo Database\", "\\Druma701va16100\forestry\Tools\Databases\NRMU Photo
Database\")

 

[Don]

I followed your advice. When I run the query, nothing changes. Here's
what I have:

UPDATE MainNRMUPhotoTable SET PreHarvest_Picture_Path =
Replace(PreHarvest_Picture_Path,"\\Druma701va16100\forestry\Databases\NRMU
Photo Database\","\\Druma701va16100\forestry\Tools\Databases\NRMU
Photo Database\");

 

[Douglas J. Steele]

It should work, assuming that you typed the old path correctly. What version
of Access are you using? There was a problem using the Replace function in
queries in early versions of Access 2000. The usual solution was to write a
"wrapper" function:


Function MyReplace( _
OriginalValue As String, _
FromPhrase As String, _
ToPhrase As String _
) As String

MyReplace = Replace(OriginalValue, FromPhrase, ToPhrase)

End Function

If it's not too late, you might consider redesigning your table to contain
folder and file as two separate fields. Then, you could concatenate the two
fields into a single field in a query, and use the query wherever you would
otherwise have used the table, thus eliminating the need for any program
changes.

 

[Don]

Thanks for your help. I decided to just go ahead and use MID to a new
field and then back again.