Issues with the Hyperlink data type in Microsoft Access

by Philipp Stiefel, originally published May 23^rd, 2016

Photo by cocoparisienne, used here under CC0 licensing

Storing data such as email and web addresses (URLs) in an Access database sounds ridiculously simple. - And it can be.

However, if you studied some of the example databases provided by Microsoft, you might be tempted to use a Hyperlink datatype in your table to store those addresses. This makes them instantly clickable. Convenient, isn’t it? – Don’t do that!

What is behind the Hyperlink data type?

Storing data in a Hyperlink column will change the data you entered. The Hyperlink datatype is not plain text. It is a composite data type consisting of a DisplayText, the Address and optionally a SubAddress and a ScreenTip (Tool tip).

If you enter text manually or paste it into the field from the clipboard, the text entered gets analyzed by Access and, depending on the actual link text, additional info is added and the data is stored internally in a concatenated string in the format of DisplayText#Address#SubAddress#ScreenTip.

Here are some examples of this. I highlighted the parts of the stored data added by Access.

Pay attention to line 4. It is unchanged. More on that in a second.

Why is that so and what’s wrong with it?

The additional protocol information is required to make the contents of the field an actual working hyperlink. While on one hand it is convenient to have all this structured info in just one column, on the other hand it creates three problems at least.

1. When entering, pasting or importing data in a Hyperlink column, Access will separate the text into the individual parts mentioned above. This works well for the most common hyperlinks, but sometimes the result is mangled data.

   Pay particular attention to line 4 in the table above. The original address contained an anchor sub-address already. Access failed to recognize this and stored the data unchanged. So the original address is only the DisplayText now and the anchor sub-address has become the main Address. This hyperlink will not work in Access.

2. If you are not aware of the internal structure of the hyperlink data, you might see unexpected results when working with that data in VBA code. Even if you do, it is more complex and difficult to work with the data from such a column later down the line.

3. If you insert a new record with data in the hyperlink column, you need to format the data to be inserted in the format used internally (DisplayText#Address#SubAddress). Otherwise, if your data is only the actual link address, it will be written into the hyperlink column without changes and thus ending up being only the DisplayText of a not working hyperlink.

I strongly recommend to use the plain (Short) Text data type to store your email addresses or URLs. The maximum length of a Short Text field can be up to 255 characters. There might be cases where an URL’s address is longer than that, but usually this should be enough.

If you expect longer link addresses you might want to use a Memo/Long Text column. Just be aware there are some limitations with that data type too.

But I want my hyperlink to be clickable!

Ok, I convinced you to go with my recommendation. You are storing your hyperlink addresses in a plain text format. That’s good. J

Now you want your email addresses or URLs to be clickable and look like a link when displayed in a form in your applications GUI. You can still achieve that with formatting properties and a few lines of VBA in your form.

The formatting part is pretty easy in current versions of Microsoft Access (Access 2010 and newer). Just go to the Property Sheet for the textbox control that is displaying the hyperlink and set the DisplayAsHyperlink property to “Screen Only” and the ForeColor property to “Hyperlink Color”. – Done.

If you want your hyperlink-textbox to respond to single clicks, as a built-in hyperlink would do, just write an event procedure for it’s On Click-Event. Call the Application.FollowHyperlink method in your procedure and pass the value of the textbox as argument.

Here is a working sample for such an event procedure.

If that textbox is not only used for displaying the hyperlink but for editing it as well, I would rather recommend to use the On Dbl Click-Event (double click) to invoke the hyperlink.

So we are all set now with the GUI.

But what do I do with existing Hyperlink columns?

You came here, because you created a table with a hyperlink column in the past and you struggle with exporting the data or using it in VBA code now. – I understand.

All is not lost.

I created a VBA module with 3 fairly simple functions to extract the relevant data from a Hyperlink column in a query in Access.

GetHyperlinkRawData

The first function is GetHyperlinkRawData. It just returns the unprocessed hyperlink data. You can use it in an Access Query to show the data that is really stored in your hyperlink column. – There is a sample query that shows how to use this later on.

GetHyperlinkDisplayText

This function just extracts the DisplayText from the hyperlink column. If there is no DisplayText explicitly defined, it will return the full address including the sub-address.

GetHyperlinkFullAddress

Finally, GetHyperlinkFullAddress this the most important function. It extracts the hyperlink target address including the sub-address. For email addresses the function has an optional Boolean argument removeMailto to strip out the mailto:-part of the link and just return the plain email address.

This function will return the DisplayText if it is the only part of the hyperlink stored in the field and so fix links that are broken because a plain link was imported or written with VBA.

The function can however not fix the issue illustrated in line 4 of my sample table above. That type of broken link can only be fixed manually.

You can download all three functions in a VBA module here: modHyperlinkHelper

Extract the hyperlink data in a query

So now let me show you a simple example of you to uses these function to remedy you hyperlink troubles.

You just create a new select query, add the table containing the Hyperlink column, and add up to three expressions calling the function. Here is a screenshot of the design view:

The SQL of this query looks like this.

Why not use Application.HyperlinkPart?

You may ask, why parse the text of the hyperlink and not use the built-in Application.HyperlinkPart method? If you closely at my function, you see that they are somewhat more sophisticated and return useful output even if the hyperlink data was not parsed and stored in the exact format Access expects for a hyperlink.

If you explicitly want to keep the Address and SubAddress part of the Hyperlink separate, you will need to write your own function for this. In that case the HyperlinkPart method may work well for you.

Thank you for reading

So this is all for now. If you enjoyed reading this article or it did save you time dealing with your hyperlink troubles, please share it on your favorite social network.

If want to receive updates on new content on codekabinett.com, subscribe to my newsletter.

Share this article: