update table access

Question

I have a table in which i want to update a multiple rows. the row data is like this:

Cars/Audi/Norway/model1/sports.Jpeg
Cars/Audi/Norway/model1/classic.Jpeg
Cars/Audi/Norway/model1/v8.Jpeg
Cars/Audi/Norway/model1/v6.Jpeg

now i want only to update the model1, let say i want to make it model two. so how can i do it in just one query? i am using this query:

string path="some string";
string name = "some string";

("select replace(filepath,'" + path + "','" + name + "')

so it seem like

    Cars/Audi/Norway/model10/sports.Jpeg
    Cars/Audi/Norway/model10/classic.Jpeg
    Cars/Audi/Norway/model10/v8.Jpeg
    Cars/Audi/Norway/model10/v6.Jpeg

any idea?

score 2 · Answer 1 · answered Mar 01 '11 at 15:47

2

UPDATE CarImages
SET Path=Replace(Path,'/Model1/','/Model10/');

Assuming that the table is called 'CarImages' and the field you want to update is called Path.

answered Mar 01 '11 at 15:47

Duncanmhor

33
8

There's some info [here](http://www.techonthenet.com/access/functions/string/replace.php) about the Replace function in Access – Duncanmhor Mar 02 '11 at 10:41
Replace will only work running inside Access, it is a VBA function, not a Jet/ACE function. It will not work with C#, because that is using Jet/ACE. I am afraid this answer is not correct. – Fionnuala Mar 03 '11 at 07:48
@duncanmhor this isnt working, because you can read i am using c# and access so this doesnt work. – safi Mar 03 '11 at 10:16

score 0 · Answer 2 · answered Mar 02 '11 at 22:23

0

replace is only available when working within Access itself, it is a VBA function, not Jet/ACE. You will need something like:

SELECT Left([field1],InStr([field1],"/model1")-1) 
    & "/model2" 
    & Mid([field1],InStr([field1],"/model1")+Len("/model1")) AS result
FROM Table1;

answered Mar 02 '11 at 22:23

Fionnuala

90,370
7
114
152

You can use it for both, but the exact example Remou posted will only select (note the keyword "SELECT"). – Vincent Vancalbergh Mar 04 '11 at 14:14

update table access

2 Answers2

Linked