Hi.
Thanks for reading.
I am creating an access db to link to multiple sharepoint lists on multiple sharepoint sites. I build the links in VB. I want to manage all of the lists thru access instead of going to each url to edit the data in the lists.
I loop thru an sql server table to get the project numbers & urls of the lists... each project has it's own site / sharepoint lists. All of the different lists, on the different sharepoint sites, have the same names.. Risks & Issues & userinfo. Based on the project #, I link the lists and rename them (concatenating "_Risk" or "_Issue"). When I link the Risks list, userinfo automatically pops in as a linked list in access. I rename the Userinfo... that is where i think my problem is. I think that by renaming it, the 'lookups' in the risks and issues lists gets broken. If I try to open the userinfo list, sometimes it shows #deleted#.
1) do I delete the userinfo table when it automatically gets linked by linking the risks list? Then manually link the userinfo list and rename it during the linking?
2) how do I keep the lookups from teh risks / issues lists to the userinfo list?
code to link tables based on looping thru project numbers / url associated. In the code below, I pass the new table name... example PRJ-00090111 is passed as PRJ-00090111_Risks & PRJ-00090111_Issues.
Code:
Public Sub Link_New_Issues_Risks_Tables(url As String, table_name As String)
DoCmd.TransferSharePointList acLinkSharePointList, url, "Risks", , table_name & "_Risks"
DoCmd.Rename table_name & "_UserInfo", acTable, "UserInfo" ' userinfo list gets automatically linked after I link the risks list, so I rename it
DoCmd.TransferSharePointList acLinkSharePointList, url, "Issues", , table_name & "_Issues"
End Sub
Thanks.
Steve