Solved Bulk Edit Hyperlinks

[Tieval]

I have a huge database where the backend is being moved server and it contains lots of hyperlinks in the table fields. For some reason some machines reference the hyperlink to a document as z:/datafiles/data001.pdf and others as //oldserver/datafiles/data001.pdf

Technically the hyperlink is Data001#z:/datafiles/data001.pdf or the other but trying to generate a query just shows it as Data001 and I cannot therefore use find and replace in a query, I would like to find //oldserver and replace all with z: but cannot do this.

Any suggestions as a query or as VBA would be most welcome.

 

[cheekybuddha]

Hi,

Make a backup of your table first!

Have you tried a simple update query:

UPDATE YourTable
  SET LinkField = Replace(LinkField, '#//oldserver/', '#z:/')
;

You may find that Access is just being 'helpful' and only showing you the display portion of the Hyperlink field (ie the bit before the first hash symbol).

hth,

d

 

[Tieval]

That worked perfectly, many thanks.

 

[cheekybuddha]

You're welcome!