• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2087
  • Last Modified:

MS Access Linked Table to SharePoint - Broken Link

I am able to link lists in SharePoint to MS Access programmatically with VBA. Extract of the code as follows:
Dim GUID, AccountChoice As String
ListChoice = [       ]    ' the VBA code can grab the ListChoice
GUID  = [       ]   ‘ the VBA code can grab the GUID

DoCmd.TransferDatabase acLink, "WSS", "WSS;HDR=NO;IMEX=2;DATABASE=servername/;LIST=" & GUID & ";VIEW=;RetrieveIds=Yes;TABLE=" & ListChoice, acTable, , ListChoice
Assuming 5 lists have been created in SharePoint and linked to MS Access ( i.e.  List1, List2, List3, List4 and List5 ). Somewhere down the road, List5 is deleted in SharePoint and is re-created with the same name ( but now with a new GUID ).

In the MS Access DB, the linked table, List5 – the link would have been broken as it was pointing to a GUID that has been deleted.

My questions: How can I programmatically ( using VBA  in MS Access)
1.      test in the case of List5, whether the link is broken ( or its equivalent ),
2.      And if the link is broken,  how to delete the linked table,  List5 in the MS Access DB.  

Thank you in advance for your assistance.
1 Solution
Dale FyeCommented:
In my linking routines, I link to the list and then immediately test to see whether the link is valid by using a DLOOKUP function call.  

x = DLOOKUP("ID", myTable)

Add an error handler so that if the operation fails, it jumps to the error handler to delete the table.

But if you need table5, why are you deleting it, or is someone else messing with the lists in SharePoint?  If the code is deleting the list, then you should consider just deleting the records, not the table.

To delete the table from Access, you would use:

Docmd.DeleteObject acTable, tablename

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now