Update hyperlink addresses for a recordset using VBA

Occasional Visitor

Greetings VBA Access gurus,


I am trying to find the correct code to firstly list the existing target addresses for a recordset of hyperlinks, and then to update the address to a new SharePoint address...


This was my first and unsuccessful attempt:


Private Sub cmdHyperlinkList_Click()

Dim myDB As DAO.Database
Dim rst As DAO.Recordset
Dim strTable As String
Dim intRecords As Integer, i As Integer

strTable = "HyperlinkTest"
Set myDB = CurrentDb()
Set rst = myDB.OpenRecordset(strTable, dbOpenTable)
rst.Index = "PrimaryKey"
intRecords = rst.RecordCount

For i = 1 To intRecords

rst("HyperlinkAddress") = rst("NetworkFolderLocation").Hyperlink.Address


Next i
Set rst = Nothing
Set myDB = Nothing

DoCmd.OpenTable strTable, acViewNormal, acReadOnly

End Sub


The line in blue is causing the problem 




Any help from anyone would be much appreciated.



1 Reply

@Rodster, the core problem here is that a Field has no Hyperlink property.

You can use the built-in Application.HyperlinkPart method to extract individual parts of the value of a Hyperlink column. I also published more sophisticated functions to extract hyperlink parts.

In general, I rather advise against using the Hyperlink data type  because it has some issues without bringing much benefit. Using a simple text column is often a better approach.