Hi Pic,
Yes, I think so. Here are some queries and a function that I worked on in an
effort to help you with this challenge. I usually store hyperlinks as
separate components in text fields: TextToDisplay, Path, and FileOrFolder. I
can concatenate these on-the-fly into clickable hyperlinks. This makes
updating paths really easy.
You should first make a back-up copy of your database, since update queries
are used, just in case things don't go so smoothly. You might also want to
make a copy of the affected table, so that restoring the original data will
be much easier during the test phase. You may need to refine the update
queries with appropriate criteria, so that only the applicable records are
updated.
First, create the following SELECT query. To create this query, you do not
need to select a table in the select tables dialog. Once in query design
view, click on View > SQL View to change from the QBE (Query by Example view)
to the SQL view. Copy the SQL statement shown below. I used "strHyperlink" as
the name of the field and "tblLink" as the name of the table. You need to
change these to the appropriate names for your table. As an alternative to
changing these references in the queries below, you might just want to create
a new database for testing purposes and import your table into it. Then
change the fieldname to strHyperlink and the table name to tblLink:
SELECT CStr([strHyperlink]) AS HyperlinkPath,
InStr([strHyperlink],"#")+1 AS [StartingPositionOf#Sign],
InStrRev([strHyperlink],"\") AS BackSlashPosition,
InStrRev([strHyperlink],"/") AS ForwardSlashPosition
FROM tblLink;
Save this query as qryHyperlinkInfo. You can use this query to gain useful
information about your hyperlinks, which can be used to decide on the
appropriate criteria if needed.
Create another new query by copying the following SQL statement into the SQL
window, however, don't try running it yet until you add the ResetPath
function to a new module, as shown below:
UPDATE tblLink
SET tblLink.strHyperlink =
ResetPath([strHyperlink],"AR","G:\Temp","../");
Save this query as "qupdAbsoluteHyperlinkToRelativeHyperlink". Let me
explain the parameters now. The first parameter is the name of the field. The
second parameter is the conversion type: Absolute to Relative = "AR". The
other valid value for this parameter is "RA", which stands for Relative to
Absolute. The third parameter is the path to find and the fourth parameter is
the new path that you desire.
Create a new stand-alone module. Save it as basResetHyperlinkPath. Add the
following function to this module. I left a bunch of debug.print statements
in this function, which you can use to help troubleshoot things if needed:
Option Compare Database
Option Explicit
Public Function ResetPath(varHyperlink As Variant, strPathTypeConv As
String, _
strPathToFind As String, strNewPath As String) As String
Dim intStart As Integer 'Location of the first "#" character
Dim intEnd As Integer 'Location of the last "\" character
Dim intLength As Integer 'Length of the hyperlink string
If Not IsNull(varHyperlink) Then
Debug.Print varHyperlink
intStart = InStr([varHyperlink], "#")
intLength = Len([varHyperlink])
Select Case strPathTypeConv
Case "AR" 'Absolute (G:\Files) to Relative (../)
Debug.Print "Converting absolute path to relative path"
varHyperlink = Replace(varHyperlink, "\", "/")
intEnd = InStrRev([varHyperlink], "/") + 1
Case "RA" 'Relative (../) to Absolute (G:\Files)
Debug.Print "Converting relative path to absolute path"
varHyperlink = Replace(varHyperlink, "/", "\")
intEnd = InStrRev([varHyperlink], "\")
Case Else
MsgBox "An incorrect value was passed into the function for
the strPathTypeConv.", _
vbCritical, "Stop"
Exit Function
End Select
'Debug.Print varHyperlink
'Debug.Print Mid(varHyperlink, 1, intStart)
'Debug.Print strNewPath
'Debug.Print Mid(varHyperlink, intEnd, intLength)
ResetPath = Mid(varHyperlink, 1, intStart) & strNewPath &
Mid(varHyperlink, intEnd, intLength)
Debug.Print ResetPath
Debug.Print
Else
Exit Function
End If
End Function
Save the module. Now try running the update query and see what happens. Make
adjustments as required, which might include adding the appropriate criteria
to filter out certain records.
Here is a different update query to change relative paths to absolute:
UPDATE tblLink
SET tblLink.strHyperlink = ResetPath([strHyperlink],"RA","../","G:\Temp");
Save this query as "qupdRelativeHyperlinkToAbsoluteHyperlink". The
parameters are similar, but here I have switched the AR in the first update
query to RA. This one might be a bit more difficult to get working properly,
especially if there are a variety of relative paths, such as "../", "../../",
"../../../", etc.
That's my shot at doing this. It has only been marginally tested. I'll leave
the fine tuning and testing to you.
Good Luck,
Tom
http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
:
Is it possible to do a global search and replace on the underlying
hyperlink in a hyperlink field? I need to change a whole mess of
absolute paths to relative paths.
Thanks in advance,
Pic
