Replace part of a record

[cydewaze]

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?

[r937]

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)

[wwb_99]

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

[cydewaze]

@r937

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)

That sounds uber complicated.

@wwb_99

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

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

[cydewaze]

@r937

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)

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.