Refresh All SharePoint Linked Tables
acCmdRefreshSharePointList
Tom Beck from Microsoft wrote this routine in answer to the following problem.
SharePoint lists that are linked to Access 2007 database files will not reflect structural changes to the lists in SharePoint. Once someone changes list schema, you can continue to use the linked lists in Access for read-only purposes without even knowing that the list structure has changed. You cannot, however, update the list data from within Access after a structure change unless you first refresh the list.
'***************** Code Start *******************
' This code was originally written by Tom Beck
' It appeared on the The Microsoft Office Blog
Sub RefreshSharePointLinks()
'DoCmd.Hourglass True
Dim dbs As Database
Set dbs = CurrentDb()
For Each tbl In dbs.TableDefs
If (Mid(tbl.Name, 1, 1) <> "~") And ((tbl.Attributes And dbAttachedTable) = dbAttachedTable) Then
If Left(tbl.Name, 21) <> "User Information List" Then
If Left(tbl.Connect, 3) = "WSS" Then
sql = "SELECT * FROM [" & tbl.Name & "];"
Set rst = dbs.OpenRecordset(sql, dbOpenDynaset)
If Not rst.Updatable Then
DoCmd.SelectObject acTable, tbl.Name, True
DoCmd.RunCommand acCmdRefreshSharePointList
End If
End If
End If
End If
Next
'DoCmd.Hourglass False
End Sub
'****************** Code End ********************
