Results 1 to 2 of 2
  1. #1
    gopherking is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2011
    Posts
    71

    Refresh Linked Sharepoint List to Show New Records

    I wasn't sure if this should have been posted under the Sharepoint forum, but since I am looking for a VBA solution, I thought this would be the best place for it.

    So, the issue I have at hand is that I am trying to Refresh my linked Sharepoint Lists via VBA which would be executed when a user clicks a button in my FE DB. The Refresh is to update my linked Sharepoint List with any new records that have been added to the list via my Sharepoint Site. At this time, the only updates that I am receiving are any updates made to existing records. The only way a user can currently get the latest NEW records is to close the FE DB and reopen it, which during the Splashscreen Form it performs the following procedure to ReLink the Sharepoint Lists:

    Code:
    Sub RefreshSPListLinks(DBType As String)
        Dim Site As String
        Dim ERList, ERHList As String
        Dim ERTbl, ERHTbl As String
        
        If DBType = "Prod" Then
            ERList = "{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}"
            ERHList = "{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}"
            Else
                ERList = "{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}"
                ERHList = "{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}"
        End If
           
        Site = "[Enter Sharepoint Web Site Here]
        ERTbl = "Enhancement Request"
        ERHTbl = "Enhancement Request History"
        
        'Relink Sharepoint lists to DB
        DoCmd.TransferSharePointList acLinkSharePointList, Site, ERList, , "Enhancement Request"
        DoCmd.TransferSharePointList acLinkSharePointList, Site, ERHList, , "Enhancement Request History"
        
        'hide the Navigation Pane
        DoCmd.NavigateTo "acNavigationCategoryObjectType"
        DoCmd.RunCommand acCmdWindowHide
        
        
    End Sub
    The above code works like a charm, but I can't use it at other times due to it leaves the Navigation Pane open, which I want hidden from the users to prevent accessing the backend tables (they are curious, and can be very dangerous; so I try to protect them as best I can).

    I have also tried using the commands below which do refresh, but do not return the new records:

    Code:
    DoCmd.SelectObject acTable, [TableName], True
    DoCmd.RunCommand acCmdRefreshSharePointList
    And finally, I have also tried to remove the linked Sharepoint Lists and then relink them using the code in the intial FE DB open process, but I can't delete them due to existing Relationships on the tables (P.S. the relationships are in Sharepoint, not Access so they won't let me delete them either).

    Has anyone encountered this issue? Does anyone have any ideas or possibly a solution? Thanks in advance for your ideas, attempts, and help!

  2. #2
    gopherking is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2011
    Posts
    71
    Ok, now I feel dumb ...

    So the Sharepoint Lists are already linked to my DB, right? I have a process that copies all the records from the linked tables to a BE DB table so that a user doesn't have to view directly into the Sharepoint List for a record (depending on your network speed, this can be terribly slow ... and I am avoiding the Work Offline option for Sharepoint Lists as well ...). The problem that I was having is during that copy, the new records were not being copied over. So I just did a little test by creating a new record in my Sharepoint List ...

    After the record was created, I opened the Linked Sharepoint List within my FE DB and magically found the record I just created!!!! SO, since this resolved my initial thought that the new records were not being updated into the Linked Sharepoint List, it had me thinking that JUST MAYBE the copy process wasn't picking up the new records since the Linked Sharepoint List hadn't been accessed recently by my FE DB.

    With this in mind, I added a few lines to my Import Sharepoint Records procedure to Open and then Close the Linked Sharepoint List Tables and VOILA!!! It now copies over the new records.

    SO, the moral of the story is that if you have a Linked Sharepoint List in your FE DB and you are trying to copy all the records from the lists to a BE DB Table, you must initiate the Linked Sharepoint List in some way prior to your copy to ensure that the latest records are in your Linked Sharepoint List!!!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 01-05-2012, 11:52 AM
  2. Syncronizing Linked Lists from Sharepoint to Access
    By se7en9057 in forum Import/Export Data
    Replies: 0
    Last Post: 11-02-2011, 02:04 PM
  3. Linked Sharepoint Updates
    By Lorlai in forum SharePoint
    Replies: 1
    Last Post: 09-19-2011, 08:07 AM
  4. Replies: 2
    Last Post: 11-05-2010, 04:47 AM
  5. Lookup or list field: how Do I not show all records
    By scott munkirs in forum Forms
    Replies: 4
    Last Post: 04-13-2006, 12:44 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums