Access 2007 - SharePoint relink lists command

[McGeeky]

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

 

[Albert D. Kallal]

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

 

[McGeeky]

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

 

[Shin]

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.