How do I update/correct the hyperlink path in a field?

  • Thread starter Thread starter Mike Webb
  • Start date Start date
M

Mike Webb

Using Access 2K2 in WinXP over a P2P. Experience level: Beginner, with a
little experience. :)
=============================================================

Made a BIG mistake .... I am scanning news articles & items for the last 25
years into simple Access DB (in PDF format in my Shared Docs folder). I've
gotten a couple of years worth scanned and entered (about 2500 records) and
then checked how the links would work when the report was published to Word
(.RTF). No surprise - didn't work. Quickly found out (wish I'd done so at
the BEGINNING) that I used a local path vice UNC path. Is their a way to
have Access update the paths for me? If so, can someone point me to a link
or two to the article/tip/hint that can show me how?

TIA!!
 
Hi Mike,

If you've just stored the paths in a text field, it's quite simple. Be
sure to make a backup of your database before you start. You can use an
update query with an expression like this as the criterion

Like "C:\*"

and a replacement expression like this:

"\\Server\Share\" & Mid([FieldName], XX)

where XX is a number 1 greater than the length of the part of the path
you are replacing (i.e. 4 if it's C:\).



If you've used hyperlink fields things can be more complicated. Create a
select query with an calculated field like this:
ShowMe: CStr([HyperlinkFieldName])

This will show you how Access actually stores hyperlinks, in up to three
pieces separated by #. The first part is what the field will display,
the second is the actual hyperlink, and the third is the (optional)
subaddress, e.g. an anchor in an HTML page or a bookmark in a Word
document. You need to work out a replacement expression using string
functions such as Replace() and Mid() to make the changes you need, and
then use it in an update query as if the hyperlink were text field.
 
Thanks! I kinda understand what you're talking about. I can probably find
out the "How" by looking at Help in Access and in VB (ALT+F11 key).

Mike

John Nurick said:
Hi Mike,

If you've just stored the paths in a text field, it's quite simple. Be
sure to make a backup of your database before you start. You can use an
update query with an expression like this as the criterion

Like "C:\*"

and a replacement expression like this:

"\\Server\Share\" & Mid([FieldName], XX)

where XX is a number 1 greater than the length of the part of the path
you are replacing (i.e. 4 if it's C:\).



If you've used hyperlink fields things can be more complicated. Create a
select query with an calculated field like this:
ShowMe: CStr([HyperlinkFieldName])

This will show you how Access actually stores hyperlinks, in up to three
pieces separated by #. The first part is what the field will display,
the second is the actual hyperlink, and the third is the (optional)
subaddress, e.g. an anchor in an HTML page or a bookmark in a Word
document. You need to work out a replacement expression using string
functions such as Replace() and Mid() to make the changes you need, and
then use it in an update query as if the hyperlink were text field.

Using Access 2K2 in WinXP over a P2P. Experience level: Beginner, with a
little experience. :)
=============================================================

Made a BIG mistake .... I am scanning news articles & items for the last 25
years into simple Access DB (in PDF format in my Shared Docs folder). I've
gotten a couple of years worth scanned and entered (about 2500 records) and
then checked how the links would work when the report was published to Word
(.RTF). No surprise - didn't work. Quickly found out (wish I'd done so at
the BEGINNING) that I used a local path vice UNC path. Is their a way to
have Access update the paths for me? If so, can someone point me to a link
or two to the article/tip/hint that can show me how?

TIA!!
Click to expand...
Click to expand...
 
Should have mentioned in my reply that this is a Hyperlink field, so I'll
have to follow the suggestion in the latter half of your reply.

Mike

John Nurick said:
Hi Mike,

If you've just stored the paths in a text field, it's quite simple. Be
sure to make a backup of your database before you start. You can use an
update query with an expression like this as the criterion

Like "C:\*"

and a replacement expression like this:

"\\Server\Share\" & Mid([FieldName], XX)

where XX is a number 1 greater than the length of the part of the path
you are replacing (i.e. 4 if it's C:\).



If you've used hyperlink fields things can be more complicated. Create a
select query with an calculated field like this:
ShowMe: CStr([HyperlinkFieldName])

This will show you how Access actually stores hyperlinks, in up to three
pieces separated by #. The first part is what the field will display,
the second is the actual hyperlink, and the third is the (optional)
subaddress, e.g. an anchor in an HTML page or a bookmark in a Word
document. You need to work out a replacement expression using string
functions such as Replace() and Mid() to make the changes you need, and
then use it in an update query as if the hyperlink were text field.

Using Access 2K2 in WinXP over a P2P. Experience level: Beginner, with a
little experience. :)
=============================================================

Made a BIG mistake .... I am scanning news articles & items for the last 25
years into simple Access DB (in PDF format in my Shared Docs folder). I've
gotten a couple of years worth scanned and entered (about 2500 records) and
then checked how the links would work when the report was published to Word
(.RTF). No surprise - didn't work. Quickly found out (wish I'd done so at
the BEGINNING) that I used a local path vice UNC path. Is their a way to
have Access update the paths for me? If so, can someone point me to a link
or two to the article/tip/hint that can show me how?

TIA!!
Click to expand...
Click to expand...
 
Back
Top