Replace part of a record

We have a database that contains web paths (ex: /folder/subfolder/file.cfm) and we occasionally need to edit parts of those paths (ex change from /folder1/subfolder/file.cfm to /folder2/subfolder/file.cfm)

I tried to do this using the following query:

UPDATE table
SET page_path = REPLACE(page_path,'/folder1/','/folder2/')

However, the REPLACE function doesn’t seem to work in MS Access.

Does anyone know any way to beat Access into submission?

you will have to use substrings, and this means you will need a different update statement depending on the starting position and length of the substring you don’t want

SET page_path = ‘/folder2’ & substr(page_path,9,99)

This is a time to use one of the few advantages in ACCESS – you can call VBA functions directly from the data engine.

That sounds uber complicated.

Any tips (or links) to point me in the right direction? I have no idea what I’m doing when it comes to that.

I’m making progress on this method btw. I’ve managed to out the substring I want. Now I just have to put together something for the replacement.