Code to refresh SharePoint Link Tables
We have an internal Access Discussion alias that is used for by MS employees to get answers to their questions. Recently I saw a good question about how to refresh SharePoint lists. Here is the 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.
Tom Beck wrote a handy little function to call refresh on all SharePoint link tables.
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
Next week I expect to tell you more about what Tom is doing with SharePoint.
Cheers!
Updated 10/20/2009 – Tom added a check for “WSS” in the connection string to handle other linked sources that are not from WSS and a check for updatability.
