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
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 ".\"?
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.


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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

808 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