Updating Hyperlinks in Update queries

[Jen0dorf]

Hi



I added a field to a table and specified it as a hyperlink.

I then open my form in design mode and added that field.

I copied the xls file I wanted to call to the same directory and the database file.

I then right clicked on the hyper link and selected edit and browsed to my file "Plot_A.xls"

when I opened the table and clicked on the hyperlink the excel file opened.

So I then copied that hyperlink and created the update

UPDATE tblDeceased SET tblDeceased.PlotMap = "Plot_A.xlsx"
WHERE (((tblDeceased.Plot)="A"));

And it works perfectly except that the hyper link does not work.

I the n went into one maunally edited the hyperlink and pointed it to the file and it worked.

In html a hyperlink comprises of two parts the link text and the link itself [CODE<A HREF="http://www.htmlgoodies.com">Click Here for HTMLGoodies</A>

Is Access the same if so how do I copy the complete code so that I can use it in my update query to update all 37000 records.

I have to run the query 28 times as the plots are A,B,C,D, E etc but thats not a serious issue ;-)

thanks

Ian
][/CODE]

[ItsMe]

I added a field to a table and specified it as a hyperlink...

Most developers will not do this. Instead, it is more popular to use data type text and store the path as plain old text. To determine why you may be experiencing troubles, you might want to check what the actual data is that is being stored in your field. Maybe create a form and bind a control to your Hyperlink Field. Then execute some code to display the Value of your Hyperlink Field.

msgbox me.hyperlinkfieldname.value

[Neil Bingham]

In Access I used an ordinary update query to fill the hyperlink field in a table of many hundred records with the full correct file path (first entered file path manually in first record using "Edit Hyperlink" just to check it worked...)
Ran the query - it updated the hyperlink filed BUT although showing correct file path in blue and underlined none of the links worked.
In desperation, I changed the Field type in the table back to Text and saved and closed the table and then closed database and went to bed!
I reopened DB next day and went again to the table design and changed the same Field type BACK to Hyperlink, saved and closed - wow - the hyperlinks now work! Strange but prob solved for me - let me know if it works for you This was purely in Access of course - although I suspect that if you did it in Access it might (?) be possible to copy and paste into Excel - may be worth a try
I now have database with hyperlinks to my many thousand digital photos

[Neil Bingham]

Have just returned to hyperlink discussion - something that may help without resorting to code -
It is strange that query cannot be use to update hyperlink field in Access
I have found the easiest way (in Access 2010)
To bulk change the file locations shown in the hyperlink field use Find and Replace. eg if your files have a common digit or two at start then Find first two digits at beginning of field and Replace with new file path detail - example my file names all start with 20 so Find 20, Replace with F:\20 (in my case an external HD)
If you have file names have various start letters/nos then run a normal query first to add xx (for example) to front of file name (update to "xx"&[FileName]). Then proceed as above - Find xx Replace with missing file path detail ie F:\

If you wish to bulk create hyperlinks in Access then try this - first create and populate a new field with full path name using queries or whatever, secondly in table design properties change type of this field to Hyperlink - although I have sometimes found problems with this method

Hope this helps - please let me know (and others visiting this forum)

[isladogs]

As stated by ItsMe in post 2, using the hyperlink datatype is very unreliable
It is a memo field with 2 parts : a) URL and b) descriptor which may be identical to the URL or a 'friendly' alternative

For example this thread has a URL = "https://www.accessforums.net/showthread.php?t=56543"

If I rather pointlessly enter a link to this page, you will see this: https://www.accessforums.net/showthread.php?t=56543
but the actual code used is like this:


NOTE: It was tricky getting this to display the code without reverting automatically!

If you run an update query, you will be updating the URL but not the link itself

I'm assuming that changing the datatype from hyperlink to text & back again reinstated the new links

However, I would also recommend you use a text field to simplify the update process
You then link using code such as:

Application.FollowHyperlink "https://www.accessforums.net/showthread.php?t=56543"