Code to refresh Sharepoint List links

[trythis]

I found 2 examples of code to refresh my attached table link to sharepoint lists.

I have a scheduled task open my Access 2010 db and an autoexec macro runs and closes the db.

My problem is my tables disconnect from sharepoint 2010 and the update fails.

I added the code I found to a module and added a line in my macro to run the code. Now I cannot get the code to work.

Am I at least on the right track? I just want to know before I spend many hours getting this code to work.

These are the two locations of code I found.
http://www.access-programmers.co.uk/forums/showthread.php?t=211688
http://blogs.office.com/b/microsoft...4/code-to-refresh-sharepoint-link-tables.aspx

I think part of my problem is that I have a regular table that I add a txt file to that is not linked to sharepoint so I need to exclude it from the code.

Thanks for any advice

 

[trythis]

Well I just got the code to work.

After reading both code examples again I realized I had changed the order of the fields on sharepoint and changed the default view. I still have my custom fields they are just not in the default view and I made sure the order of the fields matched my access tables. The code ran perfectly and all 4 of my tables updated.

Duh. something so simple. I used the code in the first link. Thanks for giving me a place to think this through.

 

[moorracr]

Hi all...i've been searching for a solution to Macro Action RefreshSharepointLists not working, and the well known code everyone is saying works but doesn't (two links above in original post).

I'm not sure why, in the many, many blogs, posts, sites, etc. that I have found the above code in various versions that no one is willing to explicitly recognize that it just doesn't work...not even Microsoft power users or MVP's (except the newbies and less experienced users that are the ones starting threads asking for help on why it isn't working). Maybe we are all on different version of Access???

Anyway, I finally managed to get to something that works...I am using Access 2010 and here is the code working in my database (this is a modified version of what Will Suhr posted in response to the second link above). I placed this code in a module and called it when the Main Menu form opens in my database, may change it to call in an AutoExec...still toying with when I need it to run.

Function RefreshSharePointLinks()
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
'UserInfo table name can have different variations depending on the version of
'Access/Sharepoint you are using/linking to
If tbl.Name <> "UserInfo" Then
DoCmd.SelectObject acTable, tbl.Name, True
DoCmd.OpenTable tbl.Name
DoCmd.Close acTable, tbl.Name
End If
End If
Next
End Function

Hope this helps a few people avoid the many months of trial and error I have gone through.

 

[CodeJohnny]

Agree completely with you Moorracr, however I've run into two problems with the other code, and then your changes.

1. the other code was doing a left(x,3) to find WSS which shows up as ACEWSS; in the .Connect string of the table object in the loop. I see you're not checking this at all though, so you'd be doing a open close on all tables and all links, not just Sharepoint.

So the issue truly though is things like locks, and changes on the sharepoint that are not reflected in the Access Table, and open close doesn't seem to fix that at all for some instances of how you might do a refresh.

Do you have a variation off this concept? Otherwise, thank you for the fix,
-
CodeJohnny

 

[moorracr]

I was under the impression that the attribute dbAttachedTable was ensuring it was only running on attached or linked tables not all tables. For my database it runs quickly as is and works in that it successfully refreshes my linked SP tables so I havent changed from what I posted as of now. If I do ever check into what you have pointed out I will be sure to post new code. Thanks! :0)

 

[shamusot]

Thank you so much for this code. I've been struggling with this issue and your revised code seems to have done the trick. I was on the verge of tears!

 

[moorracr]

So glad it has helped someone avoid the tears also.

 

[shamusot]

Hi moorracr. Unfortunately, the code didn't end up being the solution to my particular database's issue after all, although it looked so promising.

My database issue appears to have related to the database needing to be logged into my Office365 account in order to link to the linked sharepoint lists.

Here is what worked for me in the end:

insert the following function into a module, replace "table1" with any linked sharepoint table in your db:

Function refreshList()
CurrentDb.TableDefs("table1").RefreshLink
End Function

go to Create tab > then select Macro
Insert the 'RunCode' macro and in the space enter 'refreshList()', this should invoke the login prompt correctly.

Note: just remember to make sure you don't have a form that uses sharepoint data opening up when the database opens by default.

I cannot take credit for this solution. It turned up in stackoverflow website under the heading: Access with links to Sharepoint Crashes.

I cannot copy the link here as I'm a newbie member and so I'm not permitted to.

Hope this is useful to others having this issue.