I have found that when working with linked sharepoint 2007 tables, in Access
2007, that table changes on the sharepoint side do not, real time, show up in
the linked table on the Access side. I have this code that I am attempting
to basically "refresh" my links, by deleting, and then relinking the tables
before critical code is run that relies on "latest" data:


Sub refreshSharepointTables()
'----------------------------------------------------------------------------
' experimental: Refresh of Assets & CI sharepoint links (not currently used)
'----------------------------------------------------------------------------
' First delete the existing CI Table Link
'----------------------------------------------------------------------------
unlinkWS ("CI Data")
'----------------------------------------------------------------------------
' Next relink the table
'----------------------------------------------------------------------------
DoCmd.TransferDatabase acLink, "WSS", "WSS;HDR=NO;IMEX=2;" & _
"DATABASE=http://teams1.sharepoint.hp.com/teams/IPGassets;" & _
"LIST={9CE103C0-D36A-4E6B-96E4-77DA4AA9650C};" & _
"VIEW=;RetrieveIds=Yes;TABLE=CI Data", acTable, , _
"CI Data"
End Sub

Sub unlinkWS(WSName As String)
'--------------------------------------------------------------------------
' After successful processing, unlink the excel worksheet
'-------------------------------------------------------------------------
DoCmd.DeleteObject acTable, WSName

End Sub

While the do.Cmd.transferdatabase codes work "perfectly", the unlinkWS
subroutine, that I used to delete the tables, before linking, does not. It
returns a "Run-time error '2387': You cannot delete the table 'CI Data'; it
is participating in one or more relationships".

THe problem is that there are actually no relationships to other tables
(when I bring up the relationships map). Any idea what is happening here?

Patk

It means the relationships established in Sharepoint, not your mdb.
--
Dave Hargis, Microsoft Access MVP


"PatK" wrote:

> I have found that when working with linked sharepoint 2007 tables, in Access
> 2007, that table changes on the sharepoint side do not, real time, show up in
> the linked table on the Access side. I have this code that I am attempting
> to basically "refresh" my links, by deleting, and then relinking the tables
> before critical code is run that relies on "latest" data:
>
>
> Sub refreshSharepointTables()
> '----------------------------------------------------------------------------
> ' experimental: Refresh of Assets & CI sharepoint links (not currently used)
> '----------------------------------------------------------------------------
> ' First delete the existing CI Table Link
> '----------------------------------------------------------------------------
> unlinkWS ("CI Data")
> '----------------------------------------------------------------------------
> ' Next relink the table
> '----------------------------------------------------------------------------
> DoCmd.TransferDatabase acLink, "WSS", "WSS;HDR=NO;IMEX=2;" & _
> "DATABASE=http://teams1.sharepoint.hp.com/teams/IPGassets;" & _
> "LIST={9CE103C0-D36A-4E6B-96E4-77DA4AA9650C};" & _
> "VIEW=;RetrieveIds=Yes;TABLE=CI Data", acTable, , _
> "CI Data"
> End Sub
>
> Sub unlinkWS(WSName As String)
> '--------------------------------------------------------------------------
> ' After successful processing, unlink the excel worksheet
> '-------------------------------------------------------------------------
> DoCmd.DeleteObject acTable, WSName
>
> End Sub
>
> While the do.Cmd.transferdatabase codes work "perfectly", the unlinkWS
> subroutine, that I used to delete the tables, before linking, does not. It
> returns a "Run-time error '2387': You cannot delete the table 'CI Data'; it
> is participating in one or more relationships".
>
> THe problem is that there are actually no relationships to other tables
> (when I bring up the relationships map). Any idea what is happening here?
>
> Patk

Thanks Dave! So, in actuality, I do not wish to delete, and then relink my
sharepoint tables. What I really want to do is a quick "refresh" so that
changed made on the sharepoint side (or since I last process) reflect in the
table. I have found that unless I "refresh" (in this case, manually, in
access), the linked tables do not get refreshed.

Is there a better way you might suggest? Thanks!!!

patk

"Klatuu" wrote:

> It means the relationships established in Sharepoint, not your mdb.
> --
> Dave Hargis, Microsoft Access MVP
>
>
> "PatK" wrote:
>
> > I have found that when working with linked sharepoint 2007 tables, in Access
> > 2007, that table changes on the sharepoint side do not, real time, show up in
> > the linked table on the Access side. I have this code that I am attempting
> > to basically "refresh" my links, by deleting, and then relinking the tables
> > before critical code is run that relies on "latest" data:
> >
> >
> > Sub refreshSharepointTables()
> > '----------------------------------------------------------------------------
> > ' experimental: Refresh of Assets & CI sharepoint links (not currently used)
> > '----------------------------------------------------------------------------
> > ' First delete the existing CI Table Link
> > '----------------------------------------------------------------------------
> > unlinkWS ("CI Data")
> > '----------------------------------------------------------------------------
> > ' Next relink the table
> > '----------------------------------------------------------------------------
> > DoCmd.TransferDatabase acLink, "WSS", "WSS;HDR=NO;IMEX=2;" & _
> > "DATABASE=http://teams1.sharepoint.hp.com/teams/IPGassets;" & _
> > "LIST={9CE103C0-D36A-4E6B-96E4-77DA4AA9650C};" & _
> > "VIEW=;RetrieveIds=Yes;TABLE=CI Data", acTable, , _
> > "CI Data"
> > End Sub
> >
> > Sub unlinkWS(WSName As String)
> > '--------------------------------------------------------------------------
> > ' After successful processing, unlink the excel worksheet
> > '-------------------------------------------------------------------------
> > DoCmd.DeleteObject acTable, WSName
> >
> > End Sub
> >
> > While the do.Cmd.transferdatabase codes work "perfectly", the unlinkWS
> > subroutine, that I used to delete the tables, before linking, does not. It
> > returns a "Run-time error '2387': You cannot delete the table 'CI Data'; it
> > is participating in one or more relationships".
> >
> > THe problem is that there are actually no relationships to other tables
> > (when I bring up the relationships map). Any idea what is happening here?
> >
> > Patk

Not sure what you mean by refresh. Do you mean data changes or schema changes?

And, no, I don't have any ideas to share. I am only starting to play with
Sharepoint. I think refreshing your links would take care of it.
--
Dave Hargis, Microsoft Access MVP


"PatK" wrote:

> Thanks Dave! So, in actuality, I do not wish to delete, and then relink my
> sharepoint tables. What I really want to do is a quick "refresh" so that
> changed made on the sharepoint side (or since I last process) reflect in the
> table. I have found that unless I "refresh" (in this case, manually, in
> access), the linked tables do not get refreshed.
>
> Is there a better way you might suggest? Thanks!!!
>
> patk
>
> "Klatuu" wrote:
>
> > It means the relationships established in Sharepoint, not your mdb.
> > --
> > Dave Hargis, Microsoft Access MVP
> >
> >
> > "PatK" wrote:
> >
> > > I have found that when working with linked sharepoint 2007 tables, in Access
> > > 2007, that table changes on the sharepoint side do not, real time, show up in
> > > the linked table on the Access side. I have this code that I am attempting
> > > to basically "refresh" my links, by deleting, and then relinking the tables
> > > before critical code is run that relies on "latest" data:
> > >
> > >
> > > Sub refreshSharepointTables()
> > > '----------------------------------------------------------------------------
> > > ' experimental: Refresh of Assets & CI sharepoint links (not currently used)
> > > '----------------------------------------------------------------------------
> > > ' First delete the existing CI Table Link
> > > '----------------------------------------------------------------------------
> > > unlinkWS ("CI Data")
> > > '----------------------------------------------------------------------------
> > > ' Next relink the table
> > > '----------------------------------------------------------------------------
> > > DoCmd.TransferDatabase acLink, "WSS", "WSS;HDR=NO;IMEX=2;" & _
> > > "DATABASE=http://teams1.sharepoint.hp.com/teams/IPGassets;" & _
> > > "LIST={9CE103C0-D36A-4E6B-96E4-77DA4AA9650C};" & _
> > > "VIEW=;RetrieveIds=Yes;TABLE=CI Data", acTable, , _
> > > "CI Data"
> > > End Sub
> > >
> > > Sub unlinkWS(WSName As String)
> > > '--------------------------------------------------------------------------
> > > ' After successful processing, unlink the excel worksheet
> > > '-------------------------------------------------------------------------
> > > DoCmd.DeleteObject acTable, WSName
> > >
> > > End Sub
> > >
> > > While the do.Cmd.transferdatabase codes work "perfectly", the unlinkWS
> > > subroutine, that I used to delete the tables, before linking, does not. It
> > > returns a "Run-time error '2387': You cannot delete the table 'CI Data'; it
> > > is participating in one or more relationships".
> > >
> > > THe problem is that there are actually no relationships to other tables
> > > (when I bring up the relationships map). Any idea what is happening here?
> > >
> > > Patk

Ok..maybe someone else has run into this....to bad MS is not more clear about
this. The problem is that when you link a sharepoint table, the data is
"static" on the Access side, until you either 1), manually refresh the data,
or 2) find some other way to do the refresh.

The problem is that my users will not necessarily always understand that
they have to refresh the link manually. In my beta testers, I have already
had a bunch of folks tell me the links are not updated when they change data
on the sharepoint side, even tho I have "explictly told them that they have
to do the manual refresh (and these are people who are more technical than
will ultimately use this db. ARG! I can only see the support tickets
shooting in on this when released, if I do not figure this out.

Anyway...appreciate the response...still looking the "the" answer.

patk

"PatK" wrote:

> Thanks Dave! So, in actuality, I do not wish to delete, and then relink my
> sharepoint tables. What I really want to do is a quick "refresh" so that
> changed made on the sharepoint side (or since I last process) reflect in the
> table. I have found that unless I "refresh" (in this case, manually, in
> access), the linked tables do not get refreshed.
>
> Is there a better way you might suggest? Thanks!!!
>
> patk
>
> "Klatuu" wrote:
>
> > It means the relationships established in Sharepoint, not your mdb.
> > --
> > Dave Hargis, Microsoft Access MVP
> >
> >
> > "PatK" wrote:
> >
> > > I have found that when working with linked sharepoint 2007 tables, in Access
> > > 2007, that table changes on the sharepoint side do not, real time, show up in
> > > the linked table on the Access side. I have this code that I am attempting
> > > to basically "refresh" my links, by deleting, and then relinking the tables
> > > before critical code is run that relies on "latest" data:
> > >
> > >
> > > Sub refreshSharepointTables()
> > > '----------------------------------------------------------------------------
> > > ' experimental: Refresh of Assets & CI sharepoint links (not currently used)
> > > '----------------------------------------------------------------------------
> > > ' First delete the existing CI Table Link
> > > '----------------------------------------------------------------------------
> > > unlinkWS ("CI Data")
> > > '----------------------------------------------------------------------------
> > > ' Next relink the table
> > > '----------------------------------------------------------------------------
> > > DoCmd.TransferDatabase acLink, "WSS", "WSS;HDR=NO;IMEX=2;" & _
> > > "DATABASE=http://teams1.sharepoint.hp.com/teams/IPGassets;" & _
> > > "LIST={9CE103C0-D36A-4E6B-96E4-77DA4AA9650C};" & _
> > > "VIEW=;RetrieveIds=Yes;TABLE=CI Data", acTable, , _
> > > "CI Data"
> > > End Sub
> > >
> > > Sub unlinkWS(WSName As String)
> > > '--------------------------------------------------------------------------
> > > ' After successful processing, unlink the excel worksheet
> > > '-------------------------------------------------------------------------
> > > DoCmd.DeleteObject acTable, WSName
> > >
> > > End Sub
> > >
> > > While the do.Cmd.transferdatabase codes work "perfectly", the unlinkWS
> > > subroutine, that I used to delete the tables, before linking, does not. It
> > > returns a "Run-time error '2387': You cannot delete the table 'CI Data'; it
> > > is participating in one or more relationships".
> > >
> > > THe problem is that there are actually no relationships to other tables
> > > (when I bring up the relationships map). Any idea what is happening here?
> > >
> > > Patk

Oh Boy, I didn't know that about Access/Sharepoint. Not good. Can you do
updates or appends to the data, or is it read only?

If you need to refresh the tables, you could put a timer event in a form
that is always open and do the refresh periodically without the user havint
to deal with it.
--
Dave Hargis, Microsoft Access MVP


"PatK" wrote:

> Ok..maybe someone else has run into this....to bad MS is not more clear about
> this. The problem is that when you link a sharepoint table, the data is
> "static" on the Access side, until you either 1), manually refresh the data,
> or 2) find some other way to do the refresh.
>
> The problem is that my users will not necessarily always understand that
> they have to refresh the link manually. In my beta testers, I have already
> had a bunch of folks tell me the links are not updated when they change data
> on the sharepoint side, even tho I have "explictly told them that they have
> to do the manual refresh (and these are people who are more technical than
> will ultimately use this db. ARG! I can only see the support tickets
> shooting in on this when released, if I do not figure this out.
>
> Anyway...appreciate the response...still looking the "the" answer.
>
> patk
>
> "PatK" wrote:
>
> > Thanks Dave! So, in actuality, I do not wish to delete, and then relink my
> > sharepoint tables. What I really want to do is a quick "refresh" so that
> > changed made on the sharepoint side (or since I last process) reflect in the
> > table. I have found that unless I "refresh" (in this case, manually, in
> > access), the linked tables do not get refreshed.
> >
> > Is there a better way you might suggest? Thanks!!!
> >
> > patk
> >
> > "Klatuu" wrote:
> >
> > > It means the relationships established in Sharepoint, not your mdb.
> > > --
> > > Dave Hargis, Microsoft Access MVP
> > >
> > >
> > > "PatK" wrote:
> > >
> > > > I have found that when working with linked sharepoint 2007 tables, in Access
> > > > 2007, that table changes on the sharepoint side do not, real time, show up in
> > > > the linked table on the Access side. I have this code that I am attempting
> > > > to basically "refresh" my links, by deleting, and then relinking the tables
> > > > before critical code is run that relies on "latest" data:
> > > >
> > > >
> > > > Sub refreshSharepointTables()
> > > > '----------------------------------------------------------------------------
> > > > ' experimental: Refresh of Assets & CI sharepoint links (not currently used)
> > > > '----------------------------------------------------------------------------
> > > > ' First delete the existing CI Table Link
> > > > '----------------------------------------------------------------------------
> > > > unlinkWS ("CI Data")
> > > > '----------------------------------------------------------------------------
> > > > ' Next relink the table
> > > > '----------------------------------------------------------------------------
> > > > DoCmd.TransferDatabase acLink, "WSS", "WSS;HDR=NO;IMEX=2;" & _
> > > > "DATABASE=http://teams1.sharepoint.hp.com/teams/IPGassets;" & _
> > > > "LIST={9CE103C0-D36A-4E6B-96E4-77DA4AA9650C};" & _
> > > > "VIEW=;RetrieveIds=Yes;TABLE=CI Data", acTable, , _
> > > > "CI Data"
> > > > End Sub
> > > >
> > > > Sub unlinkWS(WSName As String)
> > > > '--------------------------------------------------------------------------
> > > > ' After successful processing, unlink the excel worksheet
> > > > '-------------------------------------------------------------------------
> > > > DoCmd.DeleteObject acTable, WSName
> > > >
> > > > End Sub
> > > >
> > > > While the do.Cmd.transferdatabase codes work "perfectly", the unlinkWS
> > > > subroutine, that I used to delete the tables, before linking, does not. It
> > > > returns a "Run-time error '2387': You cannot delete the table 'CI Data'; it
> > > > is participating in one or more relationships".
> > > >
> > > > THe problem is that there are actually no relationships to other tables
> > > > (when I bring up the relationships map). Any idea what is happening here?
> > > >
> > > > Patk

The updates actually work, it is the refresh that is key. Up to now, I have
only made changes on the sharepoint side (that should be seen in access, per
the MS provided info), and am not seeing them unless I do the manual refresh.
Processing I do after changing a data element on the sharepoint side, is not
available for my Access-side processing without this update.

Your point about refreshing the table is exactly what I am trying to do.
Whether timer based, or I just execute it before critical processing (which
is my direction), that "code" is what I am seeking.

I am wondering if I could not create a "recorded macro" of the manual steps,
then rip it apart in VB, to see what it is doing....hmm.....

"Klatuu" wrote:

> Oh Boy, I didn't know that about Access/Sharepoint. Not good. Can you do
> updates or appends to the data, or is it read only?
>
> If you need to refresh the tables, you could put a timer event in a form
> that is always open and do the refresh periodically without the user havint
> to deal with it.
> --
> Dave Hargis, Microsoft Access MVP
>
>
> "PatK" wrote:
>
> > Ok..maybe someone else has run into this....to bad MS is not more clear about
> > this. The problem is that when you link a sharepoint table, the data is
> > "static" on the Access side, until you either 1), manually refresh the data,
> > or 2) find some other way to do the refresh.
> >
> > The problem is that my users will not necessarily always understand that
> > they have to refresh the link manually. In my beta testers, I have already
> > had a bunch of folks tell me the links are not updated when they change data
> > on the sharepoint side, even tho I have "explictly told them that they have
> > to do the manual refresh (and these are people who are more technical than
> > will ultimately use this db. ARG! I can only see the support tickets
> > shooting in on this when released, if I do not figure this out.
> >
> > Anyway...appreciate the response...still looking the "the" answer.
> >
> > patk
> >
> > "PatK" wrote:
> >
> > > Thanks Dave! So, in actuality, I do not wish to delete, and then relink my
> > > sharepoint tables. What I really want to do is a quick "refresh" so that
> > > changed made on the sharepoint side (or since I last process) reflect in the
> > > table. I have found that unless I "refresh" (in this case, manually, in
> > > access), the linked tables do not get refreshed.
> > >
> > > Is there a better way you might suggest? Thanks!!!
> > >
> > > patk
> > >
> > > "Klatuu" wrote:
> > >
> > > > It means the relationships established in Sharepoint, not your mdb.
> > > > --
> > > > Dave Hargis, Microsoft Access MVP
> > > >
> > > >
> > > > "PatK" wrote:
> > > >
> > > > > I have found that when working with linked sharepoint 2007 tables, in Access
> > > > > 2007, that table changes on the sharepoint side do not, real time, show up in
> > > > > the linked table on the Access side. I have this code that I am attempting
> > > > > to basically "refresh" my links, by deleting, and then relinking the tables
> > > > > before critical code is run that relies on "latest" data:
> > > > >
> > > > >
> > > > > Sub refreshSharepointTables()
> > > > > '----------------------------------------------------------------------------
> > > > > ' experimental: Refresh of Assets & CI sharepoint links (not currently used)
> > > > > '----------------------------------------------------------------------------
> > > > > ' First delete the existing CI Table Link
> > > > > '----------------------------------------------------------------------------
> > > > > unlinkWS ("CI Data")
> > > > > '----------------------------------------------------------------------------
> > > > > ' Next relink the table
> > > > > '----------------------------------------------------------------------------
> > > > > DoCmd.TransferDatabase acLink, "WSS", "WSS;HDR=NO;IMEX=2;" & _
> > > > > "DATABASE=http://teams1.sharepoint.hp.com/teams/IPGassets;" & _
> > > > > "LIST={9CE103C0-D36A-4E6B-96E4-77DA4AA9650C};" & _
> > > > > "VIEW=;RetrieveIds=Yes;TABLE=CI Data", acTable, , _
> > > > > "CI Data"
> > > > > End Sub
> > > > >
> > > > > Sub unlinkWS(WSName As String)
> > > > > '--------------------------------------------------------------------------
> > > > > ' After successful processing, unlink the excel worksheet
> > > > > '-------------------------------------------------------------------------
> > > > > DoCmd.DeleteObject acTable, WSName
> > > > >
> > > > > End Sub
> > > > >
> > > > > While the do.Cmd.transferdatabase codes work "perfectly", the unlinkWS
> > > > > subroutine, that I used to delete the tables, before linking, does not. It
> > > > > returns a "Run-time error '2387': You cannot delete the table 'CI Data'; it
> > > > > is participating in one or more relationships".
> > > > >
> > > > > THe problem is that there are actually no relationships to other tables
> > > > > (when I bring up the relationships map). Any idea what is happening here?
> > > > >
> > > > > Patk

I should also add that Access "seems" to refresh the links upon opening the
DB. It is just subsequent changes on the sharepoint side that seem to be
missing.

"PatK" wrote:

> The updates actually work, it is the refresh that is key. Up to now, I have
> only made changes on the sharepoint side (that should be seen in access, per
> the MS provided info), and am not seeing them unless I do the manual refresh.
> Processing I do after changing a data element on the sharepoint side, is not
> available for my Access-side processing without this update.
>
> Your point about refreshing the table is exactly what I am trying to do.
> Whether timer based, or I just execute it before critical processing (which
> is my direction), that "code" is what I am seeking.
>
> I am wondering if I could not create a "recorded macro" of the manual steps,
> then rip it apart in VB, to see what it is doing....hmm.....
>
> "Klatuu" wrote:
>
> > Oh Boy, I didn't know that about Access/Sharepoint. Not good. Can you do
> > updates or appends to the data, or is it read only?
> >
> > If you need to refresh the tables, you could put a timer event in a form
> > that is always open and do the refresh periodically without the user havint
> > to deal with it.
> > --
> > Dave Hargis, Microsoft Access MVP
> >
> >
> > "PatK" wrote:
> >
> > > Ok..maybe someone else has run into this....to bad MS is not more clear about
> > > this. The problem is that when you link a sharepoint table, the data is
> > > "static" on the Access side, until you either 1), manually refresh the data,
> > > or 2) find some other way to do the refresh.
> > >
> > > The problem is that my users will not necessarily always understand that
> > > they have to refresh the link manually. In my beta testers, I have already
> > > had a bunch of folks tell me the links are not updated when they change data
> > > on the sharepoint side, even tho I have "explictly told them that they have
> > > to do the manual refresh (and these are people who are more technical than
> > > will ultimately use this db. ARG! I can only see the support tickets
> > > shooting in on this when released, if I do not figure this out.
> > >
> > > Anyway...appreciate the response...still looking the "the" answer.
> > >
> > > patk
> > >
> > > "PatK" wrote:
> > >
> > > > Thanks Dave! So, in actuality, I do not wish to delete, and then relink my
> > > > sharepoint tables. What I really want to do is a quick "refresh" so that
> > > > changed made on the sharepoint side (or since I last process) reflect in the
> > > > table. I have found that unless I "refresh" (in this case, manually, in
> > > > access), the linked tables do not get refreshed.
> > > >
> > > > Is there a better way you might suggest? Thanks!!!
> > > >
> > > > patk
> > > >
> > > > "Klatuu" wrote:
> > > >
> > > > > It means the relationships established in Sharepoint, not your mdb.
> > > > > --
> > > > > Dave Hargis, Microsoft Access MVP
> > > > >
> > > > >
> > > > > "PatK" wrote:
> > > > >
> > > > > > I have found that when working with linked sharepoint 2007 tables, in Access
> > > > > > 2007, that table changes on the sharepoint side do not, real time, show up in
> > > > > > the linked table on the Access side. I have this code that I am attempting
> > > > > > to basically "refresh" my links, by deleting, and then relinking the tables
> > > > > > before critical code is run that relies on "latest" data:
> > > > > >
> > > > > >
> > > > > > Sub refreshSharepointTables()
> > > > > > '----------------------------------------------------------------------------
> > > > > > ' experimental: Refresh of Assets & CI sharepoint links (not currently used)
> > > > > > '----------------------------------------------------------------------------
> > > > > > ' First delete the existing CI Table Link
> > > > > > '----------------------------------------------------------------------------
> > > > > > unlinkWS ("CI Data")
> > > > > > '----------------------------------------------------------------------------
> > > > > > ' Next relink the table
> > > > > > '----------------------------------------------------------------------------
> > > > > > DoCmd.TransferDatabase acLink, "WSS", "WSS;HDR=NO;IMEX=2;" & _
> > > > > > "DATABASE=http://teams1.sharepoint.hp.com/teams/IPGassets;" & _
> > > > > > "LIST={9CE103C0-D36A-4E6B-96E4-77DA4AA9650C};" & _
> > > > > > "VIEW=;RetrieveIds=Yes;TABLE=CI Data", acTable, , _
> > > > > > "CI Data"
> > > > > > End Sub
> > > > > >
> > > > > > Sub unlinkWS(WSName As String)
> > > > > > '--------------------------------------------------------------------------
> > > > > > ' After successful processing, unlink the excel worksheet
> > > > > > '-------------------------------------------------------------------------
> > > > > > DoCmd.DeleteObject acTable, WSName
> > > > > >
> > > > > > End Sub
> > > > > >
> > > > > > While the do.Cmd.transferdatabase codes work "perfectly", the unlinkWS
> > > > > > subroutine, that I used to delete the tables, before linking, does not. It
> > > > > > returns a "Run-time error '2387': You cannot delete the table 'CI Data'; it
> > > > > > is participating in one or more relationships".
> > > > > >
> > > > > > THe problem is that there are actually no relationships to other tables
> > > > > > (when I bring up the relationships map). Any idea what is happening here?
> > > > > >
> > > > > > Patk

Are you familiar with refreshing links to an mdb backend?
--
Dave Hargis, Microsoft Access MVP


"PatK" wrote:

> I should also add that Access "seems" to refresh the links upon opening the
> DB. It is just subsequent changes on the sharepoint side that seem to be
> missing.
>
> "PatK" wrote:
>
> > The updates actually work, it is the refresh that is key. Up to now, I have
> > only made changes on the sharepoint side (that should be seen in access, per
> > the MS provided info), and am not seeing them unless I do the manual refresh.
> > Processing I do after changing a data element on the sharepoint side, is not
> > available for my Access-side processing without this update.
> >
> > Your point about refreshing the table is exactly what I am trying to do.
> > Whether timer based, or I just execute it before critical processing (which
> > is my direction), that "code" is what I am seeking.
> >
> > I am wondering if I could not create a "recorded macro" of the manual steps,
> > then rip it apart in VB, to see what it is doing....hmm.....
> >
> > "Klatuu" wrote:
> >
> > > Oh Boy, I didn't know that about Access/Sharepoint. Not good. Can you do
> > > updates or appends to the data, or is it read only?
> > >
> > > If you need to refresh the tables, you could put a timer event in a form
> > > that is always open and do the refresh periodically without the user havint
> > > to deal with it.
> > > --
> > > Dave Hargis, Microsoft Access MVP
> > >
> > >
> > > "PatK" wrote:
> > >
> > > > Ok..maybe someone else has run into this....to bad MS is not more clear about
> > > > this. The problem is that when you link a sharepoint table, the data is
> > > > "static" on the Access side, until you either 1), manually refresh the data,
> > > > or 2) find some other way to do the refresh.
> > > >
> > > > The problem is that my users will not necessarily always understand that
> > > > they have to refresh the link manually. In my beta testers, I have already
> > > > had a bunch of folks tell me the links are not updated when they change data
> > > > on the sharepoint side, even tho I have "explictly told them that they have
> > > > to do the manual refresh (and these are people who are more technical than
> > > > will ultimately use this db. ARG! I can only see the support tickets
> > > > shooting in on this when released, if I do not figure this out.
> > > >
> > > > Anyway...appreciate the response...still looking the "the" answer.
> > > >
> > > > patk
> > > >
> > > > "PatK" wrote:
> > > >
> > > > > Thanks Dave! So, in actuality, I do not wish to delete, and then relink my
> > > > > sharepoint tables. What I really want to do is a quick "refresh" so that
> > > > > changed made on the sharepoint side (or since I last process) reflect in the
> > > > > table. I have found that unless I "refresh" (in this case, manually, in
> > > > > access), the linked tables do not get refreshed.
> > > > >
> > > > > Is there a better way you might suggest? Thanks!!!
> > > > >
> > > > > patk
> > > > >
> > > > > "Klatuu" wrote:
> > > > >
> > > > > > It means the relationships established in Sharepoint, not your mdb.
> > > > > > --
> > > > > > Dave Hargis, Microsoft Access MVP
> > > > > >
> > > > > >
> > > > > > "PatK" wrote:
> > > > > >
> > > > > > > I have found that when working with linked sharepoint 2007 tables, in Access
> > > > > > > 2007, that table changes on the sharepoint side do not, real time, show up in
> > > > > > > the linked table on the Access side. I have this code that I am attempting
> > > > > > > to basically "refresh" my links, by deleting, and then relinking the tables
> > > > > > > before critical code is run that relies on "latest" data:
> > > > > > >
> > > > > > >
> > > > > > > Sub refreshSharepointTables()
> > > > > > > '----------------------------------------------------------------------------
> > > > > > > ' experimental: Refresh of Assets & CI sharepoint links (not currently used)
> > > > > > > '----------------------------------------------------------------------------
> > > > > > > ' First delete the existing CI Table Link
> > > > > > > '----------------------------------------------------------------------------
> > > > > > > unlinkWS ("CI Data")
> > > > > > > '----------------------------------------------------------------------------
> > > > > > > ' Next relink the table
> > > > > > > '----------------------------------------------------------------------------
> > > > > > > DoCmd.TransferDatabase acLink, "WSS", "WSS;HDR=NO;IMEX=2;" & _
> > > > > > > "DATABASE=http://teams1.sharepoint.hp.com/teams/IPGassets;" & _
> > > > > > > "LIST={9CE103C0-D36A-4E6B-96E4-77DA4AA9650C};" & _
> > > > > > > "VIEW=;RetrieveIds=Yes;TABLE=CI Data", acTable, , _
> > > > > > > "CI Data"
> > > > > > > End Sub
> > > > > > >
> > > > > > > Sub unlinkWS(WSName As String)
> > > > > > > '--------------------------------------------------------------------------
> > > > > > > ' After successful processing, unlink the excel worksheet
> > > > > > > '-------------------------------------------------------------------------
> > > > > > > DoCmd.DeleteObject acTable, WSName
> > > > > > >
> > > > > > > End Sub
> > > > > > >
> > > > > > > While the do.Cmd.transferdatabase codes work "perfectly", the unlinkWS
> > > > > > > subroutine, that I used to delete the tables, before linking, does not. It
> > > > > > > returns a "Run-time error '2387': You cannot delete the table 'CI Data'; it
> > > > > > > is participating in one or more relationships".
> > > > > > >
> > > > > > > THe problem is that there are actually no relationships to other tables
> > > > > > > (when I bring up the relationships map). Any idea what is happening here?
> > > > > > >
> > > > > > > Patk

Nope, but it sounds interesting! Let me dig and see what I can find...that
should certainly be similar.

Patk

"Klatuu" wrote:

> Are you familiar with refreshing links to an mdb backend?
> --
> Dave Hargis, Microsoft Access MVP
>
>
> "PatK" wrote:
>
> > I should also add that Access "seems" to refresh the links upon opening the
> > DB. It is just subsequent changes on the sharepoint side that seem to be
> > missing.
> >
> > "PatK" wrote:
> >
> > > The updates actually work, it is the refresh that is key. Up to now, I have
> > > only made changes on the sharepoint side (that should be seen in access, per
> > > the MS provided info), and am not seeing them unless I do the manual refresh.
> > > Processing I do after changing a data element on the sharepoint side, is not
> > > available for my Access-side processing without this update.
> > >
> > > Your point about refreshing the table is exactly what I am trying to do.
> > > Whether timer based, or I just execute it before critical processing (which
> > > is my direction), that "code" is what I am seeking.
> > >
> > > I am wondering if I could not create a "recorded macro" of the manual steps,
> > > then rip it apart in VB, to see what it is doing....hmm.....
> > >
> > > "Klatuu" wrote:
> > >
> > > > Oh Boy, I didn't know that about Access/Sharepoint. Not good. Can you do
> > > > updates or appends to the data, or is it read only?
> > > >
> > > > If you need to refresh the tables, you could put a timer event in a form
> > > > that is always open and do the refresh periodically without the user havint
> > > > to deal with it.
> > > > --
> > > > Dave Hargis, Microsoft Access MVP
> > > >
> > > >
> > > > "PatK" wrote:
> > > >
> > > > > Ok..maybe someone else has run into this....to bad MS is not more clear about
> > > > > this. The problem is that when you link a sharepoint table, the data is
> > > > > "static" on the Access side, until you either 1), manually refresh the data,
> > > > > or 2) find some other way to do the refresh.
> > > > >
> > > > > The problem is that my users will not necessarily always understand that
> > > > > they have to refresh the link manually. In my beta testers, I have already
> > > > > had a bunch of folks tell me the links are not updated when they change data
> > > > > on the sharepoint side, even tho I have "explictly told them that they have
> > > > > to do the manual refresh (and these are people who are more technical than
> > > > > will ultimately use this db. ARG! I can only see the support tickets
> > > > > shooting in on this when released, if I do not figure this out.
> > > > >
> > > > > Anyway...appreciate the response...still looking the "the" answer.
> > > > >
> > > > > patk
> > > > >
> > > > > "PatK" wrote:
> > > > >
> > > > > > Thanks Dave! So, in actuality, I do not wish to delete, and then relink my
> > > > > > sharepoint tables. What I really want to do is a quick "refresh" so that
> > > > > > changed made on the sharepoint side (or since I last process) reflect in the
> > > > > > table. I have found that unless I "refresh" (in this case, manually, in
> > > > > > access), the linked tables do not get refreshed.
> > > > > >
> > > > > > Is there a better way you might suggest? Thanks!!!
> > > > > >
> > > > > > patk
> > > > > >
> > > > > > "Klatuu" wrote:
> > > > > >
> > > > > > > It means the relationships established in Sharepoint, not your mdb.
> > > > > > > --
> > > > > > > Dave Hargis, Microsoft Access MVP
> > > > > > >
> > > > > > >
> > > > > > > "PatK" wrote:
> > > > > > >
> > > > > > > > I have found that when working with linked sharepoint 2007 tables, in Access
> > > > > > > > 2007, that table changes on the sharepoint side do not, real time, show up in
> > > > > > > > the linked table on the Access side. I have this code that I am attempting
> > > > > > > > to basically "refresh" my links, by deleting, and then relinking the tables
> > > > > > > > before critical code is run that relies on "latest" data:
> > > > > > > >
> > > > > > > >
> > > > > > > > Sub refreshSharepointTables()
> > > > > > > > '----------------------------------------------------------------------------
> > > > > > > > ' experimental: Refresh of Assets & CI sharepoint links (not currently used)
> > > > > > > > '----------------------------------------------------------------------------
> > > > > > > > ' First delete the existing CI Table Link
> > > > > > > > '----------------------------------------------------------------------------
> > > > > > > > unlinkWS ("CI Data")
> > > > > > > > '----------------------------------------------------------------------------
> > > > > > > > ' Next relink the table
> > > > > > > > '----------------------------------------------------------------------------
> > > > > > > > DoCmd.TransferDatabase acLink, "WSS", "WSS;HDR=NO;IMEX=2;" & _
> > > > > > > > "DATABASE=http://teams1.sharepoint.hp.com/teams/IPGassets;" & _
> > > > > > > > "LIST={9CE103C0-D36A-4E6B-96E4-77DA4AA9650C};" & _
> > > > > > > > "VIEW=;RetrieveIds=Yes;TABLE=CI Data", acTable, , _
> > > > > > > > "CI Data"
> > > > > > > > End Sub
> > > > > > > >
> > > > > > > > Sub unlinkWS(WSName As String)
> > > > > > > > '--------------------------------------------------------------------------
> > > > > > > > ' After successful processing, unlink the excel worksheet
> > > > > > > > '-------------------------------------------------------------------------
> > > > > > > > DoCmd.DeleteObject acTable, WSName
> > > > > > > >
> > > > > > > > End Sub
> > > > > > > >
> > > > > > > > While the do.Cmd.transferdatabase codes work "perfectly", the unlinkWS
> > > > > > > > subroutine, that I used to delete the tables, before linking, does not. It
> > > > > > > > returns a "Run-time error '2387': You cannot delete the table 'CI Data'; it
> > > > > > > > is participating in one or more relationships".
> > > > > > > >
> > > > > > > > THe problem is that there are actually no relationships to other tables
> > > > > > > > (when I bring up the relationships map). Any idea what is happening here?
> > > > > > > >
> > > > > > > > Patk