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?

    Thanks,

    McGeeky
     
    McGeeky, May 6, 2009
    #1
    1. Advertisements

  2. "McGeeky" <> wrote in message
    news:...

    > 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.
    >


    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...

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


    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:

    http://blogs.msdn.com/access/archive/2009/02/04/code-to-refresh-sharepoint-link-tables.aspx

    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
    be
    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 (Access MVP)
    Edmonton, Alberta Canada
     
    Albert D. Kallal, May 7, 2009
    #2
    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


    "Albert D. Kallal" <> wrote in message
    news:...
    > "McGeeky" <> wrote in message
    > news:...
    >
    >> 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.
    >>

    >
    > 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...
    >
    >> Is there another way to achieve the same thing, perhaps through VBA?
    >>

    >
    > 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:
    >
    > http://blogs.msdn.com/access/archive/2009/02/04/code-to-refresh-sharepoint-link-tables.aspx
    >
    > 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
    > be
    > 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 (Access MVP)
    > Edmonton, Alberta Canada
    >
    >
    >
     
    McGeeky, May 7, 2009
    #3
  4. McGeeky

    Shin

    Joined:
    Sep 27, 2014
    Messages:
    1
    Likes Received:
    0
    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
    CurrentDB.TableDefs("old_table").RefreshLink
    3) Delete the new linked table.
     
    Shin, Sep 27, 2014
    #4
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Holger Osterbuhr

    A97 - Relink A Textfile Doesn't Work

    Holger Osterbuhr, Aug 21, 2003, in forum: Access External Data
    Replies:
    0
    Views:
    150
    Holger Osterbuhr
    Aug 21, 2003
  2. Jeff Friedman

    Relink SQL Server Tables in Access

    Jeff Friedman, Aug 22, 2003, in forum: Access External Data
    Replies:
    0
    Views:
    209
    Jeff Friedman
    Aug 22, 2003
  3. A Dubey

    Need to relink ODBC table at startup.

    A Dubey, Jan 23, 2004, in forum: Access External Data
    Replies:
    0
    Views:
    167
    A Dubey
    Jan 23, 2004
  4. Brig R. Lamoreaux

    Relink workgroup table

    Brig R. Lamoreaux, Jan 29, 2004, in forum: Access External Data
    Replies:
    0
    Views:
    111
    Brig R. Lamoreaux
    Jan 29, 2004
  5. Keith Saby
    Replies:
    2
    Views:
    183
  6. karen scheu via AccessMonster.com

    Why relink tables

    karen scheu via AccessMonster.com, Apr 22, 2005, in forum: Access External Data
    Replies:
    1
    Views:
    87
  7. Tony Girgenti

    How to tell if Relink Access tables from code is working

    Tony Girgenti, Dec 2, 2006, in forum: Access External Data
    Replies:
    11
    Views:
    231
    David W. Fenton
    Dec 4, 2006
  8. Ron
    Replies:
    0
    Views:
    151
Loading...