MS Access Linked Table to SharePoint - Broken Link

Posted on 2012-08-24
Last Modified: 2012-08-24
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.
Question by:Russellbrown
    1 Comment
    LVL 47

    Accepted Solution

    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

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Note:  There are two main ways to deploy InfoPath forms:  Server-side and directly through the SharePoint site.  Deploying a server-side InfoPath form means the form is approved by the Administrator, thus allowing greater functionality in the form. …
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now