Split Database Hyperlink Fix?

[sjd]

I inherited an unsplit DB at work. It's been running very, very slowly lately. I did a compact and repair and that helped. I have now split the database into a front end and backend. That has helped a bit as well, but I noticed the hyperlinks are broken.

Our item master table had a link field that, on click, would open up the PDF drawing files on the system. Unfortunately, the links appear to be setup as "\Drawings\<name of file>.pdf". Instead of "\\<servername>\Database\Drawings\<name of file>.pdf"

I've done some testing and running the FE from the server works fine. However, when you copy it to your own system, the hyperlinks break. No doubt because it is now looking for "My Documents\Database\Drawings\<name of file>.pdf" which does not exist.

Is there a way to run an update query that inserts the proper location into all the hyperlinks?

 

[Simon_MT]

Two choice: these hyperlinks point to a common area on the server. Or if there needs to flexiblity for these documents - laptops on the move therefore locally create a table or Function to prescribe the path:

Laptop linked to server vs remote

S:\Database\Drawings\ (S for Server mapped drive)
C:\Database\Drawings\ (C for local drive)

Simon

 

[sjd]

The hyperlinks should point to a common area on our server. Sadly, at the moment, they do not. I'm not sure how to fix them though, at least not in a realistically easy manner with an update query.

Basically an update that checks IF( IS NOT NULL, and NOT LIKE "*M:\Database\Drawings\*" ) Then modify the link to have "M:\Database\Drawings\" prefixed to the current hyperlink location.

I'm not sure how to do that though, since current hyperlinks appear to be "Link Text #LINK HREF#" as an entry in Access. I'm really not familiar with SQL String Functions (if they even exist).

Edit: I think all I really need to do is replace "#Drawings\" with "#M:\Database\Drawings\" and it would do exactly what I want it too. Just need to figure out how to get access to do that in an update query.

 

[sjd]

I got it working now. =)

UPDATE

SET [field] = replace( [field], 'String1', 'String2');

Using the above mentioned strings. Access displayed some warnings, but the links are now working.