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!