Clever Geek Handbook

Convert plain text in SQL to hyperlink in Access

I've just started a new job the first time since graduating from university and as part of this first task of mine, I need to convert a fully accessible Access 2003 database to a Front Access, SQL back-end.

The Access database consists of a series of front-end forms for adding or analyzing personnel data, as this has hyperlinks pointing to the employee's photo as well as CVs located on a shared drive. They have been stored as hyperlinks in the access database.

Since then I have converted the data to an access database in SQL and saved it in the database for testing, now as part of the data conversion, the photos and CV locations have been converted to nvarchar from the hyperlink. I did it using SSMA.

My problem is that I need these text links to appear and work as front-side hyperlinks hidden behind the words "Photo" and "CV", but I'm not sure how to do this, as I have used SQL in the past. not Access.

Any help or suggestions would be appreciated and if I am not clear in any areas feel free to ask questions and I will try to do something for you.

+2


source to share


3 answers


Thanks to everyone I was able to solve.

Everyones answer helped solve the problem, although I ended up having to recreate the form as it was an issue with how it was created.



I just updated the form by adding textboxes that will display the data and activate the hyperlink property after which they worked fine.

+1


source


You can, for example, proceed as follows:

  • Paste a command or text control at the command line.
  • set its signature or tag to "Image"
  • add onclick event
  • In the onClick procedure, view the field in the base recordset containing the file path
  • use this value to run followHyperlink method
  • You can, for example, have a button control or text control for a "picture", another for a "cv", etc.

This is one of the possibilities that you have. Another would be to have a basic "personnel" form with the "personnel documents" subform. Then you can list all the documents related to the person without any limit on the number of documents available (here we are talking about the ratio between people and the staff table), but the principle will be the same: by clicking or doing some action on the staffDocument line, you get the filename from the base recordset and send it to the Application.FollowHyperLink method. This can be done using the command button in the record lines, or even using the commandBarControl button in the main menu bar, or in the context menu. The main advantage of command tables isthat you can add additional functionality without increasing the number of labels or controls in your form.

For example, commandBars is very easy to add some additional CommandBarButtons that will also allow you to (2) send the file to someone (with some Outlook automation code) or (3) copy the file to your computer.



Working with commandBars, a typical "documents" menu in our applications will look like this:

alt text http://www.imagechicken.com/uploads/1256033845035758200.gif

(sorry for the french version, but ...) where the last three commandBarControls allow you to view / copy / send the associated file. The previous two let you download a file from a scanner or from an existing location.

+1


source


First, you need to decouple the storage issue from the hyperlink processing. The data type of a hyperlink in Access is actually a memo field with a hyperlink embedded in it in a specific format, precisely because the largest Access / Jet / ACE text field can handle 255 characters, so you need a memo field to handle even sensible URLs. not sure exactly what format you will end up with with an SSMA import operation. I'd prefer plain old text because you don't need a hyperlink field to use the FollowHyperlink method to open the target file.

I suggest you start by checking the help file for "hyperlinks". I think you would like to start with "About Hyperlinks", especially the "PARTS OF HYPERLINK ADDRESS" and EXAMPLES OF HYPERLINK ADDRESS. Then you should check the FollowHyperlink method reference. Together, this should give you enough information to figure out how to deal with them.

But I'm assuming not a complex storage at your end, i.e. plain text or something that Access can easily parse into plain text.

+1


source






More articles:


All Articles