Fully Qualify Hyperlink

Posted on 2004-03-29
Last Modified: 2008-03-04
I have a hyperlink field in a table displayed on a form.  The purpose of this is to drag a file in and drop it to create a link to that file, which will be manipulated in a later process.  When I drag and drop a file, it puts a relative address into the field.  IE:


Clicking this address returns an error that the file cannot be opened.  What I need is a fully qualified (absolute) address.  IE:


Is there an option anywhere I can set to do this?  Or perhaps intercept the drop and force it to accept a fully qualified address in code?  Or is this simply impossible without the user right-clicking and going through the hyperlink dialog (which I would really rather avoid)?  Thanks.

Question by:Malice-sama
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 50

Expert Comment

by:Steve Bink
ID: 10705251
Perhaps your manipulation in a later process can handle the conversion?  As with any other control on the form, you have events that can do the conversion for you.  You can use the BeforeUpdate event to edit the link text and put in whatever you want.  

Will it be the same absolute path each time?  Is the "..\" part of the relative path the same as the db path?

Author Comment

ID: 10705327
Yes, the "..\" in this specific instance is the equivalent of "\\Server\mapped_drive\Dir1\" in my previous example.  However, the absolute path will not always be the same.  I have no way of forseeing which folder the file will be stored in on the server.  But all people in the company have the Server and mapped_drive mapped as the same thing.  The network admin sets up every new computer to have the same shared drives off the main file server.  So I always know 'Server' and 'mapped_drive'.  But everything after "\\Server\mapped_drive\" could vary.  There isn't one specific folder set aside for these files.  That's why I want to let the users drag and drop the file into the hyperlink field.

LVL 50

Expert Comment

by:Steve Bink
ID: 10705381
ok, then what is the path it is returning relative TO?  

It's giving you the "..\" already.  What's the absolute path to ".\"?
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.


Author Comment

ID: 10705437
The path "..\" is relative to where the database file itself.  For some reason, though, if I click on this hyperlink, it returns an error.  And once the users copy the MDE to their hard drives, the relative address would no longer be accurate anyway.  For argument's sake, the current DB folder is "\\Server\mapped_drive\Dir1\Dir3\Database.mdb".  So "..\" is "\\Server\mapped_drive\Dir1\".
LVL 50

Accepted Solution

Steve Bink earned 125 total points
ID: 10705512
It shouldn't matter where the db is or who has it open.  "..\" means the parent directory.  Your code (BeforeUpdate event, I would think) should be able to check for and replace that text easily.

Next you'll have to copy your db to a client machine and see what IT puts in there.  

Expert Comment

ID: 10705528
I use Opera Browser and don't have such problems. It has a Copy Link Address menu item. May be change your browser? :)

Author Comment

ID: 10705774
To routinet, I see what you're saying.  It sounds a little complex to me, at least if I try to handle all cases from code, in the case that the address might change.  I won't have a chance to try to work on it again today, though.  I'll try it tomorrow and get back to you.

And to goliak, it doesn't have anything to do with a browser.  It's just a field labelled as a hyperlink in a table.  This launches images, PDFs, etc. in our database straight from Access.  We don't link to any websites, so the browser isn't really an issue.
LVL 50

Expert Comment

by:Steve Bink
ID: 10705809
Your best bet is to have at least a parent directory where ALL the files will go, albeit in a sub-folder.  Say, "\\Server\mapped_drive\AccessLinkFiles".  That way, you definitly know where the root of your hyperlinks is supposed to point to, and you can navigate down from there.

Let your users put files anywhere on the server, and you'll be causing yourself headaches not limited to the Access app...

Author Comment

ID: 10713008
Okay, I've taken your advice on devising a way to capture the information in the BeforeUpdate event.  However, it's giving me problems.  When I attempt to assign the altered (correct) link back to the link field, it gives me an error and says the following:

Run-time error '-2147352567 (80020009)':

The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing the database from saving the data in the field.

Is there any way to force it to accept this?  Thanks.

Author Comment

ID: 10714433
I got this to work.  I changed the code, however, from the BeforeUpdate event to the AfterUpdate event, and it works flawlessly.

We had planned to create a single folder for all the files beforehand, but at this stage, we don't have that kind of control, since this piece of the database is still in the development phase.  Once we get the single folder in place, though, it should work flawlessly.

Greatly appreciate the help!  Thanks a lot.

Expert Comment

ID: 10791886
Access generates hyperlinks relative to a "base address." If you don't specify one,  the database's location is used as a default.

If the database is moved to a different folder, the base address changes (if you're using the default).  All of your hyperlinks become invalid.

To change the default hyperlink base address:

On the File pulldown menu, select "Database Properties."
Select the Database Properties "Summary" Tab.
In the "Hyperlink base:" field, enter the partial address that is common to all users. (In your case, "\\Server\mapped_drive\"). All hyperlinks will then be relative to the Hyperlink Base you specify. They will remain valid, even if you move the database itself to a new location in your file structure.

Warning: When you change the default address, all existing hyperlinks will become invalid! They are relative to the old base address.

Expert Comment

ID: 11341209
Got some valuable info from your posts that helped me through some of my issues. Came across this a couple months off but incase there's any value here, thought I would comment. I found if you put:


in the HyperlinkBase field as suggested by gary0 you get an absolute path returned for a hyperlink.

You can set this by code too with a ridiculous reference:

CurrentDb.Containers("databases").Documents("summaryInfo").Properties("Hyperlink Base") = "file://"

Or return:

CurrentDb.Containers("databases").Documents("summaryInfo").Properties("Hyperlink Base").Value

Its kind of cool if you are using the Hyperlink datatype cause you can change the property on the fly to return relative or absolute links depending on what you need for that particular field if your db uses both.

Only trick is you have to enter something in the property manually through the File menu in order to set the property in code. Or else the property will be removed from the properties collection and will not be found if you try to read/write it's value in code. I'm sure there's a way to set property from code but I don't know.

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question