Change a Path via SQL

[sacramento]

Hi to all:

I would like to chance a path in my database via SQL, but sincerelly i don't know the way to do this:

Example:

I have in my database this:
E:\WP\image.bmp
E:\WP\fotosindividuais\IMG_001.jpg

I would like to chance the path to:
C:\WBP\ .....keeping the rest of the path

Any help
Thanks

[jmcilhinney]

In SQL Server, something like this should do the trick:

UPDATE MyTable SET MyColumn = REPLACE(MyColumn, 'E:\WP\', 'C:\WBP\')

Other databases would likely support something similar.

[sacramento]

Hi,

Thanks for the reply...
I had forgot one think....The problem it just keeping the final part of the path

Any way to exclude all the path to \image.bmp or \fotosindividuais\IMG_001.jpg and then insert all the path again? like:

C:\WBP\image.bmp
or C:\WBP\fotosindividuais\IMG_001.jpg

Thanks


Thanks

[sacramento]

Hi,

i had write this code:

anocriacao= "2019"
s1 = pathfoto
Var_Path_diretoria_Nova = "E:\WBP\"
Var_Path_diretoria_Antiga = "C:\WBP"

SQL = "update " & anocriacao & "  set " & s1 & " = '" & Var_Path_diretoria_Nova & "' where " & s1 & " = '" & Var_Path_diretoria_Antiga & "'"
db.Execute SQL

In my database i have records like:
C:\WBP\image.bmp
C:\WBP\fotosindividuais\image.bmp


Like you see in my SQL, he can't update because i don't have mentioned all the path, but just part of the path.

Wich way i can just put on query parte of the path? like "E:\WBP" and replace it?

THanks

[sacramento]

Your sugestion is:

anocriacao= "2019"
s1 = pathfoto
Var_Path_diretoria_Nova = "E:\WBP\"
Var_Path_diretoria_Antiga = "C:\WBP"

SQL = "update " & anocriacao & "  set " & s1 & " = replace (" & s1 & ", '" & Var_Path_diretoria_Antiga & "', '" & Var_Path_diretoria_Nova & "')"

db.Execute SQL

but sort an error:
Undefined function "Replace" in expression

[jmcilhinney]

What database are you using?

[sacramento]

Database Access

[jmcilhinney]

Go back and READ post #2 instead of just copying and pasting the code.

[sacramento]

This:

Other databases would likely support something similar.

And can you tell me what is the Function that is similar to Replace for Access Database?

Thanks

[jmcilhinney]

Are you arms painted on? Look it up.

[sacramento]

I searched, but not find any function similar to "Replace" for MSAccess...better all the informations converge to Replace

[jmcilhinney]

When I took the time to do the research that you should have, I found that the Access REPLACE function is VBA, like many functions apparently, and thus not available via Jet or presumably ACE. I saw mention of some dodgy workarounds but if you're interested in one then I'll leave it to you to find and test something appropriate. Just another reason to use a proper database I suppose. SQLite is the recommended option for file-based databases and it has a similar REPLACE function.

[sacramento]

Hi,
Maybe in the future i can use SQLite, but for now i need to arrange a way to resolve this problem in access

Thanks

[Zvoni]

Hi,
Maybe in the future i can use SQLite, but for now i need to arrange a way to resolve this problem in access

Thanks

And your problem is what?
Then just do a SELECT in your Frontend-Code to retrieve your old path AND your PrimaryKey
Change Old path to new Path
UPDATE to new Path using your PrimaryKey to point it to the correct record.
maybe 4-5 lines of code

[Prahlad]

Hello, @sacramento

Follow this step,To Change a Path via SQL

Step 1:-Right Click on Server and Select "Properties".
Step 2:-in the "Server Properties" dialog box, navigate to "Database Settings" tab and data/log files location under "Database default locations" group. ...
Step 3:-Click on "OK" to apply changes.

I hope this information will be usefull for you.
Thank you.

[jmcilhinney]

Hello, @sacramento

Follow this step,To Change a Path via SQL

Step 1:-Right Click on Server and Select "Properties".
Step 2:-in the "Server Properties" dialog box, navigate to "Database Settings" tab and data/log files location under "Database default locations" group. ...
Step 3:-Click on "OK" to apply changes.

I hope this information will be usefull for you.
Thank you.

I don't think that you're answering the question that was asked.

[TysonLPrice]

Are you arms painted on?

Never heard that one

[Zvoni]

I don't think that you're answering the question that was asked.

Because he doesn't read the whole thread, or he would have seen, that it's Access.
He already got a downvote from me for that since it's not his first time