This is a topic of discussion that I have participated in the forums a number of times in the past and will probably continue to engage in the future. I have decided to write an article to summarize my opinions on this subject. Essentially, the dilemma is whether to use a Text or a Hyperlink field to store a "link" to a file or folder or website, etc.
First of all, let me clarify that I am not exclusively in one camp or the other. Rather, this article just attempts to bring up points and counterpoints regarding the advantages and disadvantages of using one or the other in your database.
Using a Hyperlink Field
When designing a table in Access, using a Hyperlink field may be the easiest choice when trying to store "link" data. For example, let's say you are creating a table for clients and you want to include a field that points to a network folder where you store all the documents relating to each particular client. Using a hyperlink field makes this easy because it comes with built-in functionalities that alleviates the developer from writing code to make it work. For example, by right-clicking on the hyperlink field and selecting Hyperlink > Edit Hyperlink, the user is presented with a dialog box for building the hyperlink. For our scenario, the user can navigate to the folder location and then click 'OK' to store the link to that folder in the table. Once the link is stored in the table, the user can simply click on the field to open a folder window to gain access to all the documents for that client.
Advantages
The main advantage of using a hyperlink field is its ease of use and creation. The hyperlink builder is very intuitive and Access does all the work for you. There's no need to write any code to open the folder window or email client or whatever the program the link requires. It just works!
Another huge advantage of using a hyperlink field is that it can store "any" link. That means you can have one field that can store links to a file or folder path, a website, an email address, an FTP site, etc. Access should reconize the protocol specified in the hyperlink and then execute the appropriate program to open the link. This again would be a big task for the developer to determine the appropriate action to take and program to execute based on the protocol embedded in the link.
The last advantage of using a hyperlink field is that you can "display" a short text in place of the actual link to help the user understand better what the link is pointing to. For example, instead of seeing the full path to the folder such as
\\ServerName\OfficeLocation\DepartmentNo\ClientName\Proposals, you can simply display something like "
Client Proposals." Clicking on the link will automatically take the user to the correct folder path.
Disadvantages
Unfortunately, that last advantage of the hyperlink field can also be considered as a disadvantage by other developers. For anyone not familiar with how hyperlink fields are stored in the table, it actually has three components separated by hash marks: the display text, the hyperlink address, and the hyperlink subaddress. So, in the above example, the hyperlink address is the full folder path, and the display text is "
Client Proposals," which is all the users see when looking at the data.
The problem appears when the developer starts to use the value in the hyperlink field in code. For example, if we add an unbound textbox on a form bound to a table with the hyperlink field and use the following as its Control Source: =[HyperlinkFieldName], you will see the actual value stored in the table instead of just the hyperlink address, which is what we would normally want. For the above example, the unbound textbox will display the following value:
Client Proposals#\\ServerName\OfficeLocation\DepartmentNo\ClientName\Proposals#
Notice the hash tags separating the other components of the hyperlink field? If the user sees that, it could cause some confusion. Also, grabbing the hyperlink address using code by just referencing the field's value could cause unexpected results. For this reason, some developers prefer to use a text field instead.
Using a Text Field
As I have mentioned above, a hyperlink field may have many advantages for beginners but most experienced developers will try to avoid the pitfalls and additional maintenance that may be brought on by using it and opt to use a text field instead. A text field doesn't disguise what is stored in the table and it's easy enough to simulate a hyperlink using a text field.
Advantages
I already mentioned the main advantage of using a text field - what you see is what you get. What is being displayed in the field is exactly what is stored in the table, no surprises. Because of that, the developer is then free to manipulate the data using code. The simplest approach to simulate a hyperlink using a text field is by using the following code:
Application.FollowHyperlink HyperlinkFieldName
Disadvantages
This may not matter to seasoned developers but the first disadvantage of using a text field to simulate a hyperlink is the need to use code. As you can see above, it's not really hard to do that. However, if you can remember one of the advantages of a hyperlink field, if there's a situation where the "link" data stores various types of protocols, then the code to execute the hyperlink can become vastly complicated. For example, simply using the FollowHyperlink method when the link only contains the following data will not work because it is missing the proper protocol: thedbguy.blog.com. The developer may have to include additional logic to the code to assign and use the appropriate protocol for the link in question. These protocols might include types such as ftp, news, gopher, etc.
Conclusion and Disclaimer
Understanding that a hyperlink field really contains three components helps the developer get a better handle in manipulating the link data. But, if the purpose of the hyperlink field is just to store websites, email addresses, or file/folder paths, then using a text field will probably save you the frustration of dealing with the surprises that comes with a hyperlink field.
As I mentioned in the beginning, I am not really recommending to use one over the other but merely trying to bring up possible arguments that you may hear when you get into a discussion regarding this topic with others.
Oh, and by the way, if you were wondering how to grab just the hyperlink address from a hyperlink field as if it was a text field, you can try the following code:
Split(PlainText(HyperlinkFieldName),"#")(1)
As usual, any comments are welcome. Cheers!
Original post date: March 28, 2014
