Update hyperlink addresses for a recordset using VBA

Question

Greetings VBA Access gurus,&nbsp;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...&nbsp;This was my first and unsuccessful attempt:&nbsp;Private Sub cmdHyperlinkList_Click()Dim myDB As DAO.DatabaseDim rst As DAO.RecordsetDim strTable As StringDim intRecords As Integer, i As IntegerstrTable = "HyperlinkTest"Set myDB = CurrentDb()Set rst = myDB.OpenRecordset(strTable, dbOpenTable)rst.Index = "PrimaryKey"rst.MoveLastrst.MoveFirstintRecords = rst.RecordCountFor i = 1 To intRecordsrst.Editrst("HyperlinkAddress") = rst("NetworkFolderLocation").Hyperlink.Addressrst.Updaterst.MoveNextNext iSet rst = NothingSet myDB = NothingDoCmd.OpenTable strTable, acViewNormal, acReadOnlyEnd Sub&nbsp;The line in blue is causing the problem&nbsp;&nbsp;&nbsp;Any help from anyone would be much appreciated.&nbsp;Thanks

phils · Answer

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.

Forum Discussion

Update hyperlink addresses for a recordset using VBA

Share

Resources