You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
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
Next
'DoCmd.Hourglass False End Sub
'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.
Comments: (11) Collapse
Hi Clint, Good to see new posts on using Access with SharePoint. I think these features also come in really handy if you need to manipulate, populate or update SharePoint lists, and I have a post on this here - workerthread.wordpress.com/.../using-access-2007-to-update-sharepoint-lists Derek
When i run this code, it creates duplicates of existing sharepoint links. I am not sure how to ensure i am refreshing a link versus creating a second link with the same name. (E.g. Employee and employee1) Any suggestions?
Joel K: If you link a SharePoint list to Access and table (or link) already exists by that name, Access will add a number to the name; such as, in your case, a "1". I can think of three ways this could be happening to you: using the Access UI to create the link using the External Data feature, using the SharePoint list feature to export to Access, and using the TransferSharePointList method using VBA. In each of these cases, you need consider whether an Access table (or link) by the same name already exists. If it already exists, you may want to delete it first. Otherwise, you can use the Refresh List option. If you can share more information about the steps you're taking, I may be able to help you further.
Thanks very much. Is there a VB way of eliminating a link reference without literally deleting the file on sharepoint. I have spent hours looking for that but to no avail. IF there was i would remove teh link then reestablish.
Joel K: You may simply delete the link in Access. I'm not yet aware of a way to delete a list in SharePoint from within Access. I use this in VBA:
DoCmd.DeleteObject acTable, strTableName You could be challenged, however, by Access during the deletion process that the object cannot be deleted because it is participating in one or more relationships. If possible, you may want to consider deleting subordinate objects first. I've found this to be the case with some list collections.
Anyone know what happens I re-link the connection between two different workspaces, or different SharePoint sites? Specific anyone know what happens to the local cache? If I re-link (or modify the GUID file name to where the link points), does the whole table get pulled down if I have local caching on? I guess I am asking can I re-link between two different sets of “lists” on two different SharePoint sites? And, if so, can I “preserve” the table cache when I change the links to point to a different site? This is partly a deployment issue, and a testing issue for me. In one scenario I want the customer to “change” to what location they are using. Thus I want to be able re-link to either “location”. I am testing this today, but in case anyone has more info on what happens to the cache (and can I preserve it), I am all ears. I am even willing to "copy" out the links to a local accDB file to keep the cache if that will speed up this process. So, in a sense, I am asking can one copy the links + cache to save them. Albert D. Kallal
Edmonton, Alberta Canada
kallal@msn.com
Albert--Unfortunately, I don't think this is a supported scenario. BTW - send me an email. I would love to hear how your SharePoint application is progressing.
Hi Clint, Looking forward to your posts with more about what Tom is doing with SharePoint. Keep up the good work. Thanks,
Josh
I have incorporated this code into my database, but I find that it is not working. I have intentionally changed the schema of one of my linked SharePoint lists, invoked the code, and find the linked list (table) to be unchanged. It is only if I right-click on the list itself, go to SharePoint List Options > Refresh List will it actually refresh to reflect the schema change (when in design mode of course, which defeats the purpose of this in the first place). Another comment would be to include code to have some sort of confirmation dialog that this has taken place (or not). BTW, our SharePoint is 3.0 if that matters. Would appreciate any assistance offered. Thank you.
Hi,
Im using the same code and I get the error - "Runtime Error - 3270. Property not found." at DoCmd.RunCommand acCmdRefreshSharePointList .
Could anyone help ?
Currently using using - Access 2007
Thanks
When I run MSCDE application the navigation pane show-up which not good and wss links was not refreshed.
THIS CODE NOT WORKING..
Currently using MS Access 2010.
MS Access Team any suggestions or update to this code?
Comments: (loading) Collapse