Deleting Sharepoint Linked Tables

[PatK]

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

 

[Klatuu]

It means the relationships established in Sharepoint, not your mdb.

 

[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]

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.

 

[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

 

[Klatuu]

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.

 

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

 

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

 

[Klatuu]

Are you familiar with refreshing links to an mdb backend?

 

[PatK]

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

Patk

 

[Klatuu]

I will send you some code tomorrow. I am wrapping up to go home now.

 

[PatK]

My HERO! Thanks Dave!

Patk

I will send you some code tomorrow. I am wrapping up to go home now.

 

[Kyla Gronau]

dffdgsggggvdsdfggfdssssdfg

My HERO! Thanks Dave!

Patk

 

[gllincoln]

Hi Pat,

I know you have been kicking this around a lot - pardon my late entry here and I may be way off target?

Have you tried (on the access side) running me.requery after updates?

something as simple as this??? (probably have, but just in case)

dim rs as dao.recordset
dim id as variant

set rs=me.recordsetclone
id = rs.bookmark
me.requery
me.bookmark = id
rs.close
set rs = nothing

Hope this helps...
Gordon

 

[Klatuu]

For some reason I was not able to reply to any posts all day long yesterday,
but here is the code I promised.

Dim dbs As Database
Dim tdf As TableDef
Dim strConnect As String
Dim intCount As Integer
Dim strNewPath As String

' Determine if the Back End Database Link is Okay
' If not, Allow user to relink
Me.SetFocus
On Error Resume Next

Set dbs = CurrentDb()
For intCount = dbs.TableDefs.Count - 1 To 0 Step -1 ' Look for a linked
table
Set tdf = dbs.TableDefs(intCount)
If tdf.Connect <> "" Then ' This is not a linked table
strConnect = dbs.TableDefs(dbs.TableDefs(0).NAME).Connect '
Find The Current Link
tdf.Connect = strConnect
tdf.RefreshLink ' Try to connect to it
If Err = 0 Then ' The link is still good, carry on
Exit For
Else
Me.Modal = False
Me.PopUp = False
If MsgBox("Cannot Link to the Back End Database" & vbCrLf & _
"Click OK to Relink to the Back End or Cancel to Quit",
vbOKCancel, _
"Relink Back End Database") = vbCancel Then
DoCmd.Quit
Else
cdlBackEnd.ShowOpen
If Err = 32755 Then 'Cancel was selected
DoCmd.Quit
Else
strNewPath = cdlBackEnd.FileName
If ReLink(strNewPath) Then
Me.Modal = True
Me.PopUp = True
Else
Me.txtname.Enabled = False
Me.txtPassword.Enabled = False
Me.cmdCancel.SetFocus
End If
End If
End If
Exit For
End If ' Err = 0
End If ' tdf.Connect <> ""
Next intCount

Set dbs = Nothing
Set tdf = Nothing