I recently posted the following VBA code to the Access World Forums but I though I would also post it here on my blog.
I had found code that was supposed to work, but did not, so I modified the code to make it work for me.
Copy the code below and paste it into a new module. Be careful not to name this new module with the same name as the Public Function: “RefreshSharePointLinks”.
'This code is a modified version of the code that can be found at:
'The original code has been modified by Byron Polk
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
Public Function RefreshSharePointLinks()
Dim dbs As Database
Dim rst As dao.Recordset
Dim tbl As dao.TableDef
Dim fld As dao.Field
Dim strSql As String
Set dbs = CurrentDb()
On Error GoTo Err_ChkError
'iterate through all of the table objects in the database
For Each tbl In dbs.TableDefs
'only try to refresh linked or "Attadhed" tables
If (Mid(tbl.Name, 1, 1) <> "~") And ((tbl.Attributes And dbAttachedTable) = dbAttachedTable) Then
'***You may need to add additional "If" statements to have the code
' ignore specific tabale
'if the table is named "User Information List" do not relink the table
If Left(tbl.Name, 21) <> "User Information List" Then
'if the table is named "UserInfo" do not relink the table
If Left(tbl.Name, 8) <> "UserInfo" Then
'if the connection string for the linked or "Attached" table
'starts with "WSS" then this is a ShasrPoint table.
If Left(tbl.Connect, 3) = "WSS" Then
'try to open an editabel recordset from this table
strSql = "SELECT * FROM [" & tbl.Name & "];"
Set rst = dbs.OpenRecordset(strSql, dbOpenDynaset)
'locate an updatable field (not a calculated column)
For Each fld In tbl.Fields
If fld.Type = dbText Then
'if there is a value in this field use this field
If Not IsNull(rst.Fields(fld.Name).value) Then
'insure that this field is an updateable type field
If fld.DataUpdatable = False Then
'set the recordset to "edit" mode
'update the updatabale field with the same value
rst.Fields(fld.Name).value = rst.Fields(fld.Name).value
'attempt to update the record.
'if the table needs to be relinked, error 3851 will occur and
'our "On Error" error handling will kick in and relink the table
'Only if the update was successful, move on to the next linked table
***************End of Code***********
Hope it helps you.