Relative Hyperlinks

[Melissa]

Help! I created a database with several hyperlink fields in multiple tables.
I didn't realize until after trying to split my database (FE & BE) that the
hyperlinks would no longer work. I put the FE on the hard drive and the BE
on the network server (which is actually where I had the database already).
So now my hyperlinks only show the following:
DBArchives/ImportQuoteSheets/123456.xls.
In order to work I would think it would need to be the following:
D:/Sync/Public/DBArchives/ImportQuoteSheets/123456.xls

Is there anyway to quickly update these hyperlinks to add the prefix
"D:/Sync/Public/"?

Thanks.

 

[Ken Snell [MVP]]

Likely you can run an update query on the table's field to add that
additional information. Is the field a Hyperlink field? Or a Text field?
Tell us about the field's data type, and copy / paste here an example of the
data that are in that field (from the table itself).

 

[Melissa]

The data type for the fields is Hyperlink.

When I copy and paste it, all that shows up is the "Text to Display" for the
Hyperlink. When I convert the hyperlink to text, I get the following:
SH0902195#DBArchives\ProFormaInvoices\SH0902195.xls#

I thought maybe there would be an easy fix. Is there a way to prevent this
from happening in the future?

 

[Ken Snell [MVP]]

Run an update query on the table (make a copy of the database first!):

UPDATE NameOfTableGoesHere
SET NameOfHyperlinkFieldGoesHere =
Replace(NameOfHyperlinkFieldGoesHere,
"#DBArchives\", "#D:\Sync\Public\DBArchives\");


Or you can change the "Hyperlink Base" for your database. Go to File |
Database Properties | Summary. Enter the "base directory" (in your case,
D:\Sync\Public\" into the "Hyperlink base" textbox.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Melissa]

I'm using Access 2007. I couldn't find how to change the Hyperlink Base
following your instructions.
Thanks.

 

[Melissa]

Sorry, I figured it out. Thanks so much for the advice. It worked like a
charm!!

 

[Ken Snell [MVP]]

As you probably surmised, I'm not using ACCESS 2007 < grin >. I am sticking
with ACCESS 2003 for a while longer.....

Glad you found the new "magic button" at top left of the ACCESS window in
A2007!

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/