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"
rst.MoveLast
rst.MoveFirst
intRecords = rst.RecordCount

For i = 1 To intRecords

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

rst.MoveNext

Next i
Set rst = Nothing
Set myDB = Nothing

DoCmd.OpenTable strTable, acViewNormal, acReadOnly

End Sub

 

The line in blue is causing the problem 

 

Rodster_0-1647008571331.png

 

Any help from anyone would be much appreciated.

 

Thanks

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.