Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Sep 2004
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Editing hyperlink field (2007 SP1)

    A hyperlink field contains a file path to a network drive that is going to change. I had hoped to simplify the process by stripping out the entire path in front of the filename with the Search & Replace tool in Access. However, the final result has now appended an "http:" in front of the filename.
    Example: KeptDataBusProcSvcfilename.xxx becomes http:filename.xxx
    Is there a way to accomplish this task?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Editing hyperlink field (2007 SP1)

    You shouldn't delete the entire path, leave at least a backslash

    But why not replace the path with the new one? That would seem to be more efficient.

  3. #3
    New Lounger
    Join Date
    Sep 2004
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Editing hyperlink field (2007 SP1)

    Unexpected behavior!
    I had originally tested this on a copy of the database where my path to the network drive is not valid (It's someone else' database). However, if I perform the Search & Replace on a system where the path is valid, ONLY the "Text to display" portion of the URL gets changed -- the actual "Adress" portion of the hyperlink does not get altered.
    I created my own test URL data type, where I linked to an image file (e.g., 00002860812.tif) on my Windows desktop. I then created a record with a fully qualified version of the URL with the exact path (e.g., Cocuments and SettingsrjolynykDesktop00002860812.tif).
    In that scenario, Find & Replace (Look In parameter = the specific table, Match = Any Part of the Field) only modified the "Text to display" portion of the URL . Of course, the link worked, but that was because it was still directing to the fully qualified path.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Editing hyperlink field (2007 SP1)

    You can use an update query, with the Replace function. An update query acts on the actual contents of the hyperlink field, which is of the form

    displaytext#address#subaddress

    where displaytext and subaddress can be empty (subaddress is empty for a hyperlink to a file unless you link to a specific place inside the file, such as a bookmark in a Word document or a cell in an Excel worksheet).

  5. #5
    New Lounger
    Join Date
    Sep 2004
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Editing hyperlink field (2007 SP1)

    Thanks.

    I'm a bit weak in the scripting department. Are there any links or discussion treads you can recommend that could serve as examples?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Editing hyperlink field (2007 SP1)

    You don't have to write code, you can create a query in design view.
    Let's say that your hyperlink field is named MyField and that you want to replace the path KeptDataBusProcSvc with L:SomewhereElse

    - Create a backup copy of the database (or table), so that you can experiment safely.
    - Activate the Queries section of the Database window.
    - Click Create query in Design View.
    - Add the relevant table.
    - Add MyField to the query grid.
    - Select Query | Update Query.
    - Enter the following expression in the Update to line:

    Replace([MyField],"KeptDataBusProcSvc","L:SomewhereElse")

    - Select Query | Run or click the Run button on the toolbar (the exclamation mark).

  7. #7
    New Lounger
    Join Date
    Sep 2004
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Editing hyperlink field (2007 SP1)

    Yes, this does work. I had thought that I would have had to use other error handling, but it doesn't appear so. In this case, however, I did have to omit the slash to make the filepath work properly.

    Many thanks!

Posting Permissions

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