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. <div