Converting plain text in SQL to hyperlink in Access

Question

Hello, I've just started a new job my first since leaving University and as part of this my first task is to convert a wholly Access 2003 database to an Access front-end, SQL back-end.

The Access database consists of a series of front end forms to add or review staff data, as part of this there is hyperlinks pointing to a photograph of the employee and there CV located on a shared drive. These were saved as hyperlinks within the Access DB.

I have since converted the data in the Access DB to SQL and stored it in a database for me to test on, now as part of the data conversion the photo and CV locations were converted to nvarchar from hyperlink. I've done this using SSMA.

My issue now is that I need to have these text links displayed and working as hyperlinks on the front end hidden behind the words "Photo" and "CV", but am unsure as to how to go about this, as in the past I've only ever used SQL and not Access.

Any help or suggestions would be appreciated and if I've not been clear in any areas please feel free to ask questions and I'll try to clear anything up for you.

Answer 1

First, you need to separate the issue of data storage vs. handling as a hyperlink. The hyperlink data type in Access is actually a memo field with the hyperlink embedded in it in a particular format, precisely because the largest text field Access/Jet/ACE can handle is 255 characters, so you need a memo field to handle even reasonable URLs. I don't know exactly what format you end up with via an SSMA import operation. I would prefer plain old text because you don't need a hyperlink field in order to use the FollowHyperlink method to open the target file.

I'd suggest you start by checking the help file for "hyperlinks." I think you'd want to start with "About hyperlinks," particularly the sections on PARTS OF A HYPERLINK ADDRESS and EXAMPLES OF HYPERLINK ADDRESSES. You should then check the help for the FollowHyperlink method. Together this should give you enough information to figure out how to handle them.

But I am assuming a non-complex storage in your back end, i.e., plain text, or something that Access can easily parse into plain text.

Answer 2

Thanks everyone I have been able to resolve this.

Everyones response helped in resolving the issue although in the end I had to recreate the form, as it was an issue with the way it had been created.

I simply recreated the form added the text boxes which would display the data and enabled the hyperlink property, after this they worked fine.