Update Query to Change Hyperlink Location

[Leesha]

Hi,

I have hyperlinks to files stored on an external hard drive. I will need to be able to give the end user the ability to change the location of the hyperlinks without having to manually go in a change each one. Is there way to do this with an update query? An example is below:

Original Link
E:\Essential Oils\Education\Essential Oils User Guide.pdf#E:\Essential Oils\Education\Essential Oils User Guide.pdf#
New Link
\..\..\Essential Oils\Education\Essential Oils User Guide.pdf#E:\Essential Oils\Education\Essential Oils User Guide.pdf#

Note, I would need to be able to do multiple at one time. All files reside in the mail folder "Essential Oils".

Thanks,
Leesha

[HansV]

Do you really want to change only the display text, and not the target address?

[Leesha]

I'm not sure I understand or explained it correctly. I need to change the target address IE from the E drive to the C drive. The folder that all of the files and subfolders resided in will always be the "Essential Oils". My goal is to give the user the ability to enter the Drive letter in a text box on a form and then run an update query to change the drive letter in all of the hyperlinks

[HansV]

In the Original Link

E:\Essential Oils\Education\Essential Oils User Guide.pdf#E:\Essential Oils\Education\Essential Oils User Guide.pdf#

the first part (before the first #) is the display text, and the second part (between the #s) is the target of the hyperlink. In your New Link

\..\..\Essential Oils\Education\Essential Oils User Guide.pdf#E:\Essential Oils\Education\Essential Oils User Guide.pdf#

only the first part, i.e. the display text, has been changed, but the target is exactly the same as in the Original Link...

If you simply want to change the drive letter from for example E: to G:, create a query based on the table.
Add the hyperlink field to the query grid.
Select Update in the Query Type group of the Design tab of the ribbon.
Enter the following in the Update to line:

Replace([HyperlinkField], "E:\", "G:\")

where HyperlinkField is the name of your field.
Click Run to execute the update query.

[Leesha]

Thanks Hans!
That worked perfectly. I've not used "Replace" in an update query before. Will add that one to my "Han's bag of tricks"
Leesha

[Leesha]

HI,
I need to give the user the ability to change the drive letters on their own. I've been working at this all afternoon but am conceeding defeat. The end result is always that the data in the hyperlink field [link] is deleted. This is what I've done so far:

1. Created a form that stores the new and old Drive letter. Info is entered via a form
2. Setup the update query to pull the info that is being changed from the table where the data in #1 Is stored.
3. The update query code is:
UPDATE tblLinks, qryUpdateOriginalDriveLetter SET tblLinks.Link = Replace([Link],[storedOldDrive],[Updatenewdrive]);
4. When I run the query I get an error that says that nothing was updated due to violations. If I say yes to continue all hyperlinks are deleted.

What am I doing wrong??
Thanks,
Leesha

[HansV]

Why do you have qryUpdateOriginalDriveLetter in the SQL? Try the following:

UPDATE tblLinks SET tblLinks.Link = Replace([Link],[Forms]![NameOfForm]![storedOldDrive],[Forms]![NameOfForm]![Updatenewdrive]);

where NameOfForm is the name of the form, and storedOldDrive and Updatenewdrive are the names of the text boxes on the form in which the user enters the old and new drive.

[Leesha]

I had tried something like this but got the same violation error. This is what I have:

Replace([Link],[Forms]![frmChangeLinkDrive]![txtUpdateOriginalDrive],[Forms]![frmChangeLinkDrive]![txtUpdateLink])

[Leesha]

Sorry Han's it is in fact working! I thought there were only 7 rows of test data and there are 8. The one row with a link did in fact change.
Thanks!
Leesha

[HansV]

Good to hear that!