Code Examples

Refresh All SharePoint Linked Tables


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
   'DoCmd.Hourglass False

End Sub

'****************** Code End ********************

© 1998 - 2011 Terry Wickenden TKW Design Site developed maintained and hosted by TKW Design. This site is best viewed at 1024 x 768. Optimised for Firefox.