Update Network Path for Pics

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

doodle - 26 Feb 2007 17:02 GMT

greetings. access 97, sql 2005

One section of my database allows users to browse for a picture, then
it stores the network path. the problem is that they are using their
server mapping to browse, which stores the path as:

U:\Spindle\Spindle_DataBase\Pictures\Linked Pictures
\Marvin_030705_1.JPG

instead of:

\\Servicerv1\Optimum\Spindle\Spindle_DataBase\Pictures\Linked Pictures
\Marvin_030705_1.JPG

this is causing issues with users that do not have the server mapped
to U:

i need to write an update query to change the paths. i suppose i will
need to run it daily going forward. i am writing it in visual studio.
i know how to write it to update the entire field, but how can i write
it to just replace a partial of the field? For instance, I want to
replace U:\ with \\Servicerv1\Optimum\

This is what I have to start off with, but of course it is not going
to find anything, I just wrote it so you could see the syntax:

UPDATE tblTD_Pics
SET ImagePath_1 = '\\Servicesrv1\Optimum\'
WHERE ImagePath_1 = 'U:\'

-doodle

Reply to this Message

storrboy - 26 Feb 2007 17:33 GMT

> greetings. access 97, sql 2005
>
[quoted text clipped - 27 lines]
>
> -doodle

Check on the useages of the Left, Right and Mid functions. I would
apply the replace function you are doing at the point that the user
selects the file (I assume you are using a FileOpenSave dialog that
returns a file path). Before updating the tables with the chosen path,
do the replacement. This would prevent having to do it daily with a
query.

Reply to this Message

doodle - 26 Feb 2007 22:19 GMT

I tried this, it said incorrect syntax:

REPLACE ((LEFT(ImagePath_1, 2)), 'U:', '\\Servicesrv1\Optimum\')

ideas?

-doodle

Reply to this Message

storrboy - 26 Feb 2007 23:51 GMT

> I tried this, it said incorrect syntax:
>
[quoted text clipped - 3 lines]
>
> -doodle

I can't recall the Replace syntax and I don't have a new enough
version nearby to check.
I'll look a little later.

Reply to this Message

storrboy - 27 Feb 2007 02:35 GMT

> > I tried this, it said incorrect syntax:
>
[quoted text clipped - 7 lines]
> version nearby to check.
> I'll look a little later.

If using the Replace function I think it should be as follows

Dim strRet As String 'Or whatever variable you want
strRet = Replace(ImagePath_1, "U:\", "\\Servicesrv1\Optimum\", 1, 1,
vbTextCompare)

Using the Left function is not requried when using the Replace
function.

Reply to this Message