Access 2007 - SharePoint relink lists command

Discussion in 'Access External Data' started by McGeeky, May 6, 2009.

  1. McGeeky

    McGeeky Guest

    Hi. I want to change the SharePoint lists that my Access tables are linked
    to; but when I try using the relink lists command from the External Data
    ribbon tab there is not enough space to enter the SharePoint site URL in the
    "new site" edit box, only 64 characters max. This is odd because Access
    allows me to enter long site URLs when originally linking the tables.

    Is there another way to achieve the same thing, perhaps through VBA?


    McGeeky, May 6, 2009
    1. Advertisements

  2. Very odd....and I just tried the above...I can re-produce the above...

    I will submit this as a bug right after I done this post...
    I'm thinking the work around is to delete all your links, and then relink to
    the new share point site. This seems like a painful and lousy suggestion by
    me...but that's about all I have right now.

    I don't have a VBA relink example.

    Note that if you change the structures on the share point site, you must
    refresh the links on the access side, and the following piece of code will
    do that
    for you:

    However for re-link to a different site? (or application area within a
    existing SP site?) -- golly, without the built-in re-link this is going to
    quite difficult.

    My spider sense tells me you have to delete the link, and then re-add it.

    The reason for this is two fold.

    First, we have what is called an offline
    mode. That means local data might be cached and NOT YET SYNCED to
    SharePoint. So I'm not really quite sure how the whole process gets resolved
    when you do in fact run re-link code in which data is locally cached and not
    yet synced. Anyway, lets ignore the sync issue.

    We do have the following command to create a "link" in access:

    DoCmd.TransferSharePointList acLinkSharePointList,
    "Site Address", "list id", , "access linked tablename"

    The problem with above is that the "list id" is in fact a GUID.
    The GUID is *different* between sites even when the list has the same name.
    (in fact the GUID is different in different application area's on the same
    SharePoint site).

    So, re-linking code would have to retrieve a "list" of SharePoint tables
    (lists), and then grab the GUID, and then match up the existing table names
    using the new GUID's.

    I think right now you stuck with delete...and then re-link....

    If I find some code in the next day...I post it here....
    Albert D. Kallal, May 7, 2009
    1. Advertisements

  3. McGeeky

    McGeeky Guest

    Hi Albert,

    Thanks for your comprehensive response and following up with a bug posting!

    Your workaround of deleting and relinking should be OK I think, even the
    TransferSharePointList should be OK as I don't mind doing the coding to find
    out the GUID.

    Thanks again!!!

    McGeeky, May 7, 2009
  4. McGeeky


    Sep 27, 2014
    Likes Received:
    This is an old post, but I did not find a good solution to this problem on internet easily. I was able to relink the table using following process on MS Access 2010 (x86).

    To relink a table to a new SharePoint site,
    1) Create a new linked table.
    2) In VBA
    CurrentDB.TableDefs("old_table").Connection = CurrentDB.TableDefs("new_table").Connection
    3) Delete the new linked table.
    Shin, Sep 27, 2014
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.