I’ve always avoided using Hyperlinks.  There are a number of reasons, but my main complaints are

• the UI is hidden!
• multiple extra clicks required!
• the UI simply isn’t user-friendly
• it is very easy for users to erroneously edit the wrong values
• the way it is managed causes extra headaches when trying to automate them
• Hyperlink can automatically get re-written using relative paths so you loose proper oversight, you can no longer properly report links or sort …
• Opening hyperlinks can trigger security notices which is not a good user experience

You can find posts and videos going into depth as to why Access Hyperlink fields are less than ideal.

Personally, just like attachments, most experienced developers prefer to simply store hyperlinks as plain text and automate them using simple VBA (Application.FollowHyperlink).

Okay, back to Hyperlinks.  Regardless of my personal views, the simple reality is that many people still use them and so I thought I’d touch upon them briefly today to hopefully clarify a couple things about them.

Creating a Hyperlink Field

Creating a Hyperlink field is extremely easy.  You simply add a field to a table and select Hyperlink as the Data Type!

Adding Hyperlinks to a Field

To add a new Hyperlink you must right-click on a field/control and select Hyperlink -> Edit Hyperlink…

Yes, it is counterintuitive, you select Edit when you are creating/adding a hyperlink to a new record.

Once you do that, the Hyperlink dialog will open allowing you to enter the various pieces of information to build and create your Hyperlink.

Understanding Hyperlink Parts

In Access, a hyperlink entry has multiple parts that define it.  As I understand it, it currently has 4 parts all separated by a # sign.  The basic format being:

DisplayValue#Address#SubAddress#Screentip

Knowing this, below is a complete example of an entry (for a Word Document):

The 2 elements to be aware of are:

• If you want to add a SubAddress, you need to add it manually yourself after the actual link, but don’t forget to add the # sign!
• To add a Screentip, you must press on the ScreenTip… button to open another pop-up dialog.

Editing an Existing Hyperlink

The basic steps remain the same: select Hyperlink -> Edit Hyperlink… 

However, one important thing to be aware of when working in a table or query is that I have found the Right-click to be very unreliable on a hyperlink if the field has the focus.

I often have to select another field and then try right-click on the hyperlink again to get the Hyperlink option to appear in the context menu.

How to View the Complete Hyperlink Entry?

When you create a hyperlink it only displays the DisplayValue part of the hyperlink.  Did you know that if you are on a Hyperlink field/control and press F2 Access will display the full entry with all the hyperlink parts.

Working With Hyperlinks in Queries and VBA Code

What you need to be aware of is that Access has a HyperlinkPart() method that will allow you to work with Hyperlinks properly in both queries and VBA code.

Hyperlinks In Queries

By default, when you run a query on a hyperlink field, it will display the DisplayValue.  This might be fine to look at and interact with in Access, but probably not desirable if you are exporting the data.  This is where HyperlinkPart() can assist you.  With HyperlinkPart(), you can specify which part of the hyperlink you want to retrieve, so instead of the DisplayValue, you can get the Address, SubAddress or FullAddress.  The annoying thing about queries however is that the Part input argument enum is not accessible, so you can’t use the constant like you can in VBA.  Instead, you need to know the values and what they represent.  Here’s a summary table:

Thus, we can create a query to return any aspect of a hyperlink we wish, for instance:

SELECT HyperlinkFieldName, HyperlinkPart([HyperlinkFieldName],0) AS DisplayedValue, HyperlinkPart([HyperlinkFieldName],1) AS DisplayText, HyperlinkPart([HyperlinkFieldName],2) AS Address, HyperlinkPart([HyperlinkFieldName],3) AS SubAddress, HyperlinkPart([HyperlinkFieldName],4) AS ScreenTip, HyperlinkPart([HyperlinkFieldName],5) AS FullAddress
FROM YourTableName;

To create a hyperlink via a query, you simply do something like:

INSERT INTO MyDocuments ( MyHyperlink ) 
Values( 'Sales Projections#C:\temp\MyDoc.docx#General Information#2020 Sales Projections For the Finance Dept')

Hyperlinks in Code

Similarly to queries, in VBA you can also use the HyperlinkPart method, but with the added advantage of intellisense autocomplete and the AcHyperlinkPart Enum.

Here’s an example of a recordset loop that iterates through a recordset reporting on a hyperlink field using the HyperlinkPart method.

    Dim db                    As DAO.Database
    Dim rs                    As DAO.Recordset

    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT * FROM MyDocuments")
    With rs        
        If .RecordCount <> 0 Then
            Do While Not .EOF
                Debug.Print "Hyperlink:", , Nz(![MyHyperlink])
                Debug.Print , "acDisplayedValue:", HyperlinkPart(Nz(![MyHyperlink]), acDisplayedValue)
                Debug.Print , "acDisplayText:", HyperlinkPart(Nz(![MyHyperlink]), acDisplayText)
                Debug.Print , "acAddress:", , HyperlinkPart(Nz(![MyHyperlink]), acAddress)
                Debug.Print , "acSubAddress:", , HyperlinkPart(Nz(![MyHyperlink]), acSubAddress)
                Debug.Print , "acScreenTip:", , HyperlinkPart(Nz(![MyHyperlink]), acScreenTip)
                Debug.Print , "acFullAddress:", HyperlinkPart(Nz(![MyHyperlink]), acFullAddress)
                .MoveNext
                Debug.Print
            Loop
        End If
    End With
    rs.Close
    Set rs = Nothing
    Set db = Nothing

If you wanted to create or edit a hyperlink, in that case you could do something like:

    Dim db                    As DAO.Database
    Dim rs                    As DAO.Recordset

    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT * FROM MyDocuments")
    With rs
        .AddNew 'or .Edit depending on what you need to do
        ![MyHyperlink] = "Sales Projections#C:\temp\MyDoc.docx#General Information#2020 Sales Projections For the Finance Dept"
        .Update
    End With
    rs.Close
    Set rs = Nothing
    Set db = Nothing

Or, you could also simply do something like:

    Dim sSQL                  As String

    sSQL = "INSERT INTO MyDocuments ( MyHyperlink ) " & _
           "Values( 'Sales Projections#C:\temp\MyDoc.docx#General Information#2020 Sales Projections For the Finance Dept')"
    CurrentDb.Execute sSQL, dbFailOnError

Parts Are Optional

Note that parts are optional.  You can technically insert a hyperlink with just a DisplayValue.  It would show up in the recordset just fine, but it would actually do anything when clicked on.  The same is true for all the parts.  So you control what you include, or don’t.

At a minimum, you should be supplying the DisplayValue and Address.  This guarantees you present the user with proper text and functional link.  Everything else is icing on the cake!

So a concrete example of a simple insertion of just a DisplayValue and Address would be:

INSERT INTO MyDocuments ( MyHyperlink ) 
Values( 'Sales Projections#C:\temp\MyDoc.docx#')

Or

    Dim sSQL                  As String

    sSQL = "INSERT INTO MyDocuments ( MyHyperlink ) " & _
           "Values( 'Sales Projections#C:\temp\MyDoc.docx#')"
    CurrentDb.Execute sSQL, dbFailOnError

Or in its simplest form, supplying just the Address, and nothing more. In such a case, the Address is also used as the DisplayValue.

INSERT INTO MyDocuments ( MyHyperlink ) 
Values( '#C:\temp\MyDoc.docx#')

Or

    Dim sSQL                  As String

    sSQL = "INSERT INTO MyDocuments ( MyHyperlink ) " & _
           "Values( '#C:\temp\MyDoc.docx#')"
    CurrentDb.Execute sSQL, dbFailOnError

So as you can see, you have total control over everything, it’s just a matter of supplying, or not, the various hyperlink parts!

The Marvelous SubAddress

I think one of the most misunderstood and overlooked aspects of hyperlinks in the SubAddress part.  It can actually be very powerful.  Let’s look at it in the context of different common file types.

Excel

With a SubAddress you can specify a specific sheet cell or even a range of cells to navigate to when the file is opened.  To do so you need only add a SubAddress like:

SheetName!Range

For example, here’s are 2 examples (as if you were entering it in the dialog Address textbox):

C:\Temp\EE_SampleData.xlsx#projects!A2

C:\Temp\EE_SampleData.xlsx#projects!A2:C7

PowerPoint (No longer works)

In PowerPoint, you are supposed to be able to be able to navigate to a specific slide in the presentation when it is opened by simply adding the slide number as the SubAddress Value.  Sadly, this no longer works anymore?!  I believe this stopped working around the release of Office 2007, but could be wrong.

I’ve tried:
Presentation#SlideNo
Presentation#SlideNoTitle
Presentation#SlideNo Title
Presentation#SlideNo.
Presentation#SlideNo.Title
Presentation#SlideNo. Title

Nothing works! (Note: This bug was reported to the Access Dev Team at the time of writing this article)

Word

As I already illustrated earlier in this article, you can use the SubAddress for Word documents to navigate to specific sections (header, bookmark, …).  You simply specific the name of the section as the SubAddress like (as if you were entering it in the dialog Address textbox):

C:\temp\MyDoc.docx#General Information

Access

This is an exception.You can use the SubAddress to specify an object to open when the database is opened.  The format being:

ObjectType ObjectName

Be careful to only include a single space between the 2 or else the command fails.  Also note, that this is case insensitive.

Here are a few examples applied to different object types (as if you were entering it in the dialog Address textbox):

C:\Temp\Erp.accdb#table contacts

C:\Temp\Erp.accdb#query active contacts

C:\Temp\Erp.accdb#form NewContact

C:\Temp\Erp.accdb#report Contacts Sheet

C:\Temp\Erp.accdb#macro ImportNewRecords

C:\Temp\Erp.accdb#module mod_HelperFunctions

The Local Access Database Instance

Did you know you could use hyperlinks to navigate within the current database?  Well you can, the trick though, is to use what we learnt above directly as the Address, rather than the SubAddress.  Here are the same examples but applied as if we were already working in the C:\Temp\Erp.accdb (as if you were entering it in the dialog Address textbox):

#table contacts

#query active contacts

#form NewContact

#report Contacts Sheet

#macro ImportNewRecords

#module mod_HelperFunctions

So you omit giving an Address value, and simply provide the SubAddress value.  Thus Access assumes you are referring to the currently open database and opens the object found there.

This can be useful if you want to create internal links.

Hyperlinks To A Folder

You don’t necessarily need to create a hyperlink to a specific file. You can just as easily create one to an entire folder. This doesn’t open each individual file, no, it open Windows Explorer to that specific folder. So you simply need to supply the path to the Address part and you’re done.

C:\Temp

and UNC paths work just fine, for instance:

\\Desktop-Eng27\Asc

E-mail Address Hyperlinks

Just like you can create hyperlinks to files, you can just as easily create a hyperlink to initiate an email.  The format is always the same:

DisplayValue#Address#SubAddress#Screentip

but it’s just a question as to what these do in the context of an e-mail address.

A Few Examples of E-mail Address Hyperlinks

Simple Example

The most straightforward usage would be a simple hyperlink displaying a person’s name, in such a case the syntax would be:

DisplayValue#Address#

Thus a concrete usage would be:

Someone#mailto:someone@somewhere.com#

Slightly More Complex

If we want to push things a little further and add a nice screentip with useful information, the syntax becomes

DisplayValue#Address##ScreenTip

and a concrete usage example would be

Someone#mailto:someone@somewhere.com##Project Manager For the 123AB Project

Multiple E-mail Addresses

Did you know you can even create a hyperlink that includes multiple e-mail addresses!  Well, you can and it’s is just a question of separating the e-mail addresses by your system list separator symbol (just like in your e-mail client).  So in my case, that would be a colon (;), and as such, the basic syntax is

DisplayValue#Address#

we only have 2 parts, it’s just a question of how we fill-in the Address part.  A concrete example would be

Someone#mailto:someone@somewhere.com;someoneelse@somewhereelse.ca#

and you can add as many e-mail addresses as you’d like

Adding a Subject Line

Once again, to add a subject line, it is just a question of modifying the Address part of the hyperlink by adding the subject parameter to it.

A concrete example would be:

Someone#mailto:someone@somewhere.com?subject=Meeting#

The critical thing to be aware of with the subject parameter is that it needs to be properly encoded.  If you use the dialog this is all taken care of for you, but otherwise you’ll need to encode it yourself.

An example of an encoded subject line:

Someone#mailto:someone@somewhere.com?subject=Meeting%20Request#

As you can see above, a space is encoded as %20.

Below is a function that can be used to encode a string properly.

'---------------------------------------------------------------------------------------
' Procedure : EncodeString
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Encode a string for html/e-mail
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Late Binding  -> none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sInput    : String to be encoded
'
' Usage:
' ~~~~~~
' EncodeString("Meeting Request")
'   Returns -> Meeting%20Request
' EncodeString("Examination du modèle reçu le 2022/02/05")
'   Returns -> Examination%20du%20mod%C3%A8le%20re%C3%A7u%20le%202022%2F02%2F05
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2022-02-27              Initial Public Release
'---------------------------------------------------------------------------------------
Function EncodeString(sInput As String) As String
On Error GoTo Error_Handler
    Dim ScriptEngine          As Object    'ScriptControl
    Dim encoded               As String

    Set ScriptEngine = CreateObject("ScriptControl")  'New ScriptControl
    ScriptEngine.Language = "JScript"
    ScriptEngine.AddCode "function encode(str) {return encodeURIComponent(str);}"
    EncodeString = ScriptEngine.Run("encode", sInput)
 
Error_Handler_Exit:
    On Error Resume Next
    If Not ScriptEngine Is Nothing Then Set ScriptEngine = Nothing
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: EncodeString" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

More Parameters

It is sad, but Microsoft has implemented a very crippled version of  e-mail hyperlinks as their dialog only offers the ability to specify a subject.  The reality is that there are many more parameters to control all the different aspects of an e-mail message, things like: cc, bcc and body.

While Microsoft does not explicitly provided textboxes for each of these, you can still append them to the subject entry in the dialog.  What is important here is to separate each by an & symbol.

For instance, to add body content you can do (don’t forget about encoding!)

Someone#mailto:someone@somewhere.com?subject=Meeting%20Request&body=Just%20a%20Test#

Building on the above, let’s add a cc entry, by doing:

Someone#mailto:someone@somewhere.com?subject=Meeting%20Request&body=Just%20a%20Test&cc=someoneelse@somewhereelse.com#

As you can see, all of it can be controlled, but it can become complex quite fast.

I hope this helps demystify Microsoft Access hyperlinks a bit.

A Few Resources on the Subject

Data types for Access desktop databases

Overview of data types in Access desktop databases, and detailed data type reference, including Short Text, Long Text, and Date/Time.

Introduction to data types and field properties

Overview of data types and field properties in Access, and detailed data type reference. including Memo, Date/Time, and Text.

Application.HyperlinkPart method (Access)

The HyperlinkPart method returns information about data stored as a Hyperlink data type.

Creating hyperlinks - Learn web development | MDN

That’s it for links, for now anyway! You’ll return to links later on in the course when you start to look at styling them. Next up for HTML, we’ll return to text semantics and look at some more advanced/unusual features that you’ll find useful — Advanced text formatting is your next stop.