change all hyperlink values in table

N

NTC

have searched thru the hyperlink Q&As but not quite found this clearly
explained. In A2003 have table with one column is hyperlink property. via
Form user has populated records using the edit hyperlink method to put in
(actual path not just displayed)i.e.:

Z:/folder1/folder2/image1.jpg

now the systems people are rearranging servers and the new path is going to
be:

X:/folderX/folder2/image1.jpg

repeat for thousands of records where the Z:/folder1 is replaced by X:/folderX

so that is the task; for a one time mass change of a field/column's values
I would normally go in and do a one time update query. Is this feasible with
the hyperlink class property? Is so would appreciate a brief example.

If not, then I suppose one could implement a gradual change 'on the fly' via
vb code in the user's form that is triggered as they use the application. If
this is the only way to carry on would appreciate a brief example on this
approach.

thanks for advice - - I only deal with hyperlink once in a blue moon and so
each time I revisit it there is a bit of mystery about it still for me even
after reviewing the hyperlink class info in help....gracias.
 
D

Dirk Goldgar

NTC said:
have searched thru the hyperlink Q&As but not quite found this clearly
explained. In A2003 have table with one column is hyperlink property.
via
Form user has populated records using the edit hyperlink method to put in
(actual path not just displayed)i.e.:

Z:/folder1/folder2/image1.jpg

now the systems people are rearranging servers and the new path is going
to
be:

X:/folderX/folder2/image1.jpg

repeat for thousands of records where the Z:/folder1 is replaced by
X:/folderX

so that is the task; for a one time mass change of a field/column's
values
I would normally go in and do a one time update query. Is this feasible
with
the hyperlink class property? Is so would appreciate a brief example.

If not, then I suppose one could implement a gradual change 'on the fly'
via
vb code in the user's form that is triggered as they use the application.
If
this is the only way to carry on would appreciate a brief example on this
approach.

thanks for advice - - I only deal with hyperlink once in a blue moon and
so
each time I revisit it there is a bit of mystery about it still for me
even
after reviewing the hyperlink class info in help....gracias.


Sure, you can do it with an update query. A hyperlink field is really just
a specially formatted text field, with "#" characters marking out the parts
of the hyperlink. You could probably use an update query similar to this:

UPDATE YourTable
SET HyperlinkField =
Replace([HyperlinkField],"Z:/folder1/","X:/folderX/")
WHERE HyperlinkField Like "*Z:/folder1/*"

Shouldn't those slashes be back-slashes, by the way? As in:

UPDATE YourTable
SET HyperlinkField =
Replace([HyperlinkField],"Z:\folder1\","X:\folderX\")
WHERE HyperlinkField Like "*Z:\folder1\*"
 
N

NTC

thanks much; tried it and it works.

though I thought that one needed to do something more involved regarding
hyperlink part....this update query I thought would only change the visible
text and not necessarily change the actual address part of the link info....

less complicated than I imagined...thanks again....


Dirk Goldgar said:
NTC said:
have searched thru the hyperlink Q&As but not quite found this clearly
explained. In A2003 have table with one column is hyperlink property.
via
Form user has populated records using the edit hyperlink method to put in
(actual path not just displayed)i.e.:

Z:/folder1/folder2/image1.jpg

now the systems people are rearranging servers and the new path is going
to
be:

X:/folderX/folder2/image1.jpg

repeat for thousands of records where the Z:/folder1 is replaced by
X:/folderX

so that is the task; for a one time mass change of a field/column's
values
I would normally go in and do a one time update query. Is this feasible
with
the hyperlink class property? Is so would appreciate a brief example.

If not, then I suppose one could implement a gradual change 'on the fly'
via
vb code in the user's form that is triggered as they use the application.
If
this is the only way to carry on would appreciate a brief example on this
approach.

thanks for advice - - I only deal with hyperlink once in a blue moon and
so
each time I revisit it there is a bit of mystery about it still for me
even
after reviewing the hyperlink class info in help....gracias.


Sure, you can do it with an update query. A hyperlink field is really just
a specially formatted text field, with "#" characters marking out the parts
of the hyperlink. You could probably use an update query similar to this:

UPDATE YourTable
SET HyperlinkField =
Replace([HyperlinkField],"Z:/folder1/","X:/folderX/")
WHERE HyperlinkField Like "*Z:/folder1/*"

Shouldn't those slashes be back-slashes, by the way? As in:

UPDATE YourTable
SET HyperlinkField =
Replace([HyperlinkField],"Z:\folder1\","X:\folderX\")
WHERE HyperlinkField Like "*Z:\folder1\*"


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
 
D

Dirk Goldgar

NTC said:
thanks much; tried it and it works.

though I thought that one needed to do something more involved regarding
hyperlink part....this update query I thought would only change the
visible
text and not necessarily change the actual address part of the link
info....

less complicated than I imagined...thanks again....


The hyperlink address is stored as a string with the parts of the link
separated by '#' characters, like this:

displaytext#address#subaddress#screentip

When it is displayed, only the "displaytext" part is shown, unless that part
is not present. But what is displayed is not all of what's stored, as you
see.

The update query I gave you used the Replace function to replace the root
drive and first folder wherever it existed in the hyperlink field. Most
likely, that would be only in "address" part, but if that string happened
also to be in the other parts, it would have been replaced there, too. From
your description of the situation, I figured that was what you wanted.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top