Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2007
    Posts
    5

    Unanswered: Global change to hyperlink addresses

    First, I am a fairly unsophisticated Access user/developer. I have created a database of engineering drawings that have various informational fields. One field is a hyperlink to a scanned TIF file associated with the drawing entry. I have about 15,000 of these that point to a location such as...

    \\engr19\image$\name_of_imagefile.tif

    The engr19 machine has died and has been replaced. I need to change all these entries to...

    \\sdi-av2\image$\name_of_imagefile.tif

    Basically, I need to search on "engr19" and replace it with "sdi-av2" in the hyperlink field. How can I do this?

    Thanks.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

  3. #3
    Join Date
    Oct 2007
    Posts
    5
    Thanks for posting! Since the hyperlink address is not directly a field value, (I gather from other searching that it is some special combination of data) I suspect I need some sub-function to access this information. Does this sound right?

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You're right in thinking that it's not technically just text, but an update statement works perfectly fine!
    Here's the general idea
    Code:
    UPDATE links
    SET myHyperlink = Replace(myHyperlink, 'engr19', 'sdi-av2')
    note the use of single quotes

    Make sure you make a backup of your data before you start fiddling!
    George
    Home | Blog

  5. #5
    Join Date
    Oct 2007
    Posts
    5
    Well, I certainly appreciate the pointer!

    You have managed to convince me that I am sorely lacking in database programming skills I do know how to program, but I am clueless as to how to interface with Access. I have never written a VisualBasic script (which I assume is the code snippet you have provided). I am going to have to study some before I "fiddle".

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    nah thats a SQL update query.
    if you open the query designer
    dont select any table
    then select the SQL view and then paste that and run the query it shoudl do the job.

    but as georgev says make sure you have a an adequate backup first

  7. #7
    Join Date
    Oct 2007
    Posts
    5
    I clearly am lost.

    I have a table named "All Drawing Files". Within that table is a field called "Scanned Filename" that contains the hyperlinks. When I run the query that georgev proposed, I get a SQL window with SELECT; displayed. I replaced SELECT; with

    UPDATE links
    SET myHyperlink = Replace(myHyperlink, 'engr19', 'sdi-av2')

    "The MS Jet database engine cannot find the input table or query links..."

    Sorry...

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    UPDATE <insert your table name>
    SET <insert field name> = Replace....
    George
    Home | Blog

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by rfurnival
    I clearly am lost.

    I have a table named "All Drawing Files". Within that table is a field called "Scanned Filename" that contains the hyperlinks. When I run the query that georgev proposed, I get a SQL window with SELECT; displayed. I replaced SELECT; with

    UPDATE links
    SET myHyperlink = Replace(myHyperlink, 'engr19', 'sdi-av2')

    "The MS Jet database engine cannot find the input table or query links..."

    Sorry...

    so your SQL is going to be something like....

    UPDATE [All Drawing Files]
    SET [Scanned Filename] = Replace([Scanned Filename], 'engr19', 'sdi-av2')

    Incidentally can I suggest you get out of the habit of using spaces in your object defintioins (tables, rows, queries or whatever) then make it that bit harder to maintain over time. if you prefer your came capitalisation eg
    ScannedFilename
    AllDrawingFiles

  10. #10
    Join Date
    Oct 2007
    Posts
    5
    I can't thank you guys enough for helping me with this. Not only did it work, but I've learned something too! Thanks,

    FWIW, I had previously tried something structured as the code from healdem, but I must have missed something along the way. I did get an error message when I ran the query, but since it offered to run anyway, I hit OK. The records were updated.

    Again, thanks.

    Rick

  11. #11
    Join Date
    Oct 2008
    Posts
    1

    Thumbs up Too Cool

    We had a server crash as well and I saw a nightmare in getting all the Hyperlinks pointed to the new server. However this worked great. Can't express my thanks enough.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •