Global hyperlink change

G

Goomba79

Registered User.
Local time
Today, 12:06
Joined
Jul 25, 2012
Messages
18
Hello,

I'm having a problem changing the hyperlinks in my access table.
the hyperlinks point to locations of various documents on the server i.e.
\\Server\Quality\Documents\Doc1.docx

we have 2 domains at work so on one domain the hyper link works but on the other it doesn't.

I did a find and replace so i have formatted all hyperlinks as such..
\\Server.mydomain.local\Quality\Documents\Doc1.docx

However, this approach hasn't worked as it has only altered the 'text to display' and not the actual address (if i right click the hyperlink -> edit hyperlink the address is still \\Server\Quality\Documents\Doc1.docx)

Is there away to change the Address fields of all the hyperlinks in one go?

I really don't want to do each one separately as there are thousands :banghead:

If anyone knows how to get round this it would be greatly appreciated!!!!

Thanks!!

Iain
 
How about an update query?
 
i'll have a look into that, although not to sure how i would change the address and not just the displayed text
 
ah i just read changing the field from hyperlink to text and then changing back again will sort it!
 
Yes you are right.
Make a copy (backup) of the table for test.
Set the table in design view, change the hyperlink field til Type="Text", change the field lenght to 200 or longer.
Close the table, if all looks okay, (should now show both text and the hyperlink), run an update query, change only the text between "#" and "#".
When done change the field to hyperlink again.
 
Well I just wrote this function.. Paste the following function on a new/pre existing module, save Compile..

Code: 
Public Function renameHyperLinks(actualLink As String) As String
    Dim changeAs As String, splitArr() As String
    changeAs = "\\Server.mydomain.local\Quality\Documents\"
    splitArr = Split(actualLink, " # ")
    splitArr(1) = Replace(splitArr(1),"\\Server\Quality\Documents\", changeAs)
    renameHyperLinks = splitArr(0) & " # " & splitArr(1)
End Function
To do this, create a new field in your table tempHyperLink then have an update query something like.
Code: 
UPDATE [COLOR=Blue]yourTableName[/COLOR] SET [COLOR=Blue]yourTableName[/COLOR].tempHyperLink = renameHyperLinks([COLOR=Blue]yourTableName[/COLOR].[COLOR=Blue]hyperlinkFieldName[/COLOR]);
Change the blue bits..
This will sort it out, Try it on a backup copy..
EDIT : Oops seems like I am typing like a grandpa.. There has been other replies.. :o, but this is still worth a try..
 
Last edited:
One option is just working with text format. You can then set the IsHyperlink property in your form controls. Alternatively, you can update everything with the following SQL statement modified to fit your table and field name:
Code: 
UPDATE [TableName] SET [urlField] = Replace([urlField],"<CurrentText>","<NewText>");

You can run it through VBA or query. It's always a good idea to back up your data before doing mass updates.

Other random things that might be useful to know about hyperlink fields:
- Form.hyperlinkcontrol.Hyperlink.Address gives you the hyperlink address
- Form.hyperlinkcontrol.Hyperlink.TextToDisplay gives you the displayed text
- Form.hyperlinkcontrol gives "<TextToDisplay>#<Address>#"
- HyperlinkPart function gives you a variety of the hyperlink properties
 
Hello,

thanks for the replies!

i have written this update query.....

UPDATE Document Table
SET PathInformation = Replace(pathinformation,"SERVER.srl.local")
WHERE pathinformation Like "*SERVER*";

however i get a syntx error in update statement, i can't see what i am doing wrong (think i have been looking at it for too long!!)
 
Replace takes in three arguments,
1. The String,
2. The Pattern that you are searching for,
3. The replacement text.
Your Replace has only 2..

Also I think this UPDATE Query will form a Circular reference Error if you try to UPDATE the same field..
 
pr2-eugin said:
Replace takes in three arguments,
1. The String,
2. The Pattern that you are searching for,
3. The replacement text.
Your Replace has only 2..

Also I think this UPDATE Query will form a Circular reference Error if you try to UPDATE the same field..
Click to expand...

how would i write it?
all the examples i can find look as how i have written it!
 
Hello,
no i didn't try Post 6, can i just paste that into a query in access?
 
Goomba79 said:
can i just paste that into a query in access?
Click to expand...
NO.. If you read the post carefully, I would have mentioned it goes into a Module, Copy the function into a module and use the Query to just call the function..
 
looking at your link regarding the replace function I have modified my SQL to

Update "Document Table"
Set PathInformation = replace(pathinformation, "SERVER" , "SERVER.srl.local")
Where PathInformation Like "SERVER";

no when i run it i get syntax error in query. Incomplete query clause?
 
OK i see what you are saying with the module thing,
but surely i should be able to do it with a query??
 
Goomba79 said:
OK i see what you are saying with the module thing,
but surely i should be able to do it with a query??
Click to expand...
It doesn't sound like you've really tried most of our suggestions. It might be helpful if you do. For running from query, just paste this into your query window in SQL mode:
Code: 
UPDATE [Document Table] SET [PathInformation] = Replace([PathInformation],"SERVER","SERVER.srl.local");

The where clause is optional in this case. If it doesn't find "SERVER", then the string doesn't change and the record is just replaced with the same value.
 
Hello,

I didn't want to go the module route as that seemed a bit of a long winded way to go about things.

I knew that it should be possible to do it with an update query just wasn't sure of the syntax and access isn't very helpful discribing which part of the sql was wrong.

after alot of googleing i managed to get it working as you described!!

Thanks for the help
 
You must log in or register to reply here.

Similar threads

S
Hyperlink field does not work with Office 365 URL for Stream video
Replies
3
Views
755
sohcir
S
J
hyperlinks.
Replies
2
Views
465
John Sh
J
H
Solved Handling of address property in hyperlink fields
Replies
11
Views
660
Harry Paraskeva
H
C
Embedding a linked document in a report
Replies
7
Views
627
isladogs
isladogs
J
Almost working Drag & Drop
2
Replies
20
Views
611
MajP
MajP

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom