External data on sharepoint is not visible in ACCESS untill I Relinked 1

[Niebotel]

In my ACCESS 2007 application I have exported all tables to a sharepoint. This works fine for 2 years now.
The frontends are installed with 6 users (local on there PC's) and they are using the same data on the sharepoint.

For 1 of the tables we run into a problem. Records which were added (in ACCESS application) were not visible anymore after logging off and logging on again.

If I look directly to the table on the sharepoint the records are there, but in ACCEsS we see only the situation before adding the records (in fact 1 old record).

To try to find a solution for this problem I relinked all tables and the problem seems to be solved.

But I would like to understand why and prevent this for happening again, because no nessages indicate something is wrong so the end-user has to see the problem!

 

[MajP]

This is a pretty common bug in Sharepoint, and even the MS experts dance around answering why this happens. On all my databases that run linked lists I now always run code to relink the list on database open. That solves the problem.

From experience the list you are having problems with is your largest list. Once it gets around 2k entries you start to see this problem.

 

[Niebotel]

The list on which I had the problem a year ago was a list with 600 records; thelist on which I have the problem now had 20 records.

The relinking code was always included in my database (in the past when I was woking with linked tables in a ACCESS backend), but i do not know how to relink to a sharepont list via VBA.

Do you know?

I could not find any MS topics anywhere. Do you have a link for me?

 

[MajP]

Not sure if the same problem, but sounds like it. My problem is always on a big table. In my opinion 1.5k records in a Sharepoint list is a gamble, that is when I start seeing problems. I have not seen it on such small tables.

I am not sure anymore how I even figured this out. As you know documentation in using Access as a Front end is horrible.

Sub RefreshSharePointLinks()
  DoCmd.Hourglass True
   Dim dbs As Database
   Dim tbl As TableDef
   Dim SQL As String
   Dim rst As DAO.Recordset
   Set dbs = CurrentDb()
   For Each tbl In dbs.TableDefs
      If (Mid(tbl.Name, 1, 1) <> "~") And ((tbl.Attributes And dbAttachedTable) = dbAttachedTable) Then
         If Left(tbl.Name, 21) <> "User Information List" Then
            If Left(tbl.Connect, 3) = "WSS" Then
               SQL = "SELECT * FROM [" & tbl.Name & "];"
               Set rst = dbs.OpenRecordset(SQL, dbOpenDynaset)
               If Not rst.Updatable Then
                  DoCmd.SelectObject acTable, tbl.Name, True
                  DoCmd.RunCommand acCmdRefreshSharePointList
               End If
            End If
         End If
      End If
   Next
   DoCmd.Hourglass False
End Sub

I appears that I have a lot of if checks to ensure the linked table is in fact a sharepoint list. There must be some errors if you try this on a standard table. Not even sure anymore what the "WSS" is. Not sure if that is unique to my sharepoint connection. But the jist is that it goes through all the tables. Determines if it is a Sharepoint list. Then it returns a recordset from that table. So it appears the problem is that the database can still see the table, but thinks it is not updateable (No idea how I figured that out because I asked MS and they were clueless). So if you can see the table, but cannot update it then it has to be refreshed.

 

[Niebotel]

Hi MajP,
Thanks, for your update; I did include the coding and it seems to work (no errors)

The problem however is not that I cannot update the sharepoint list but the changes are not reflected in my access database (as I described in my problem)

So I do not know if the check: "If Not rst.Updatable Then..."
is the right one.

Any idea how I can test this?
Because now I do not have any problems anymore with the table (I relinked it manaully to solve the problem.

 

[Niebotel]

One addition: Is it OK to run de refreshshrepointlist on all sharepoint tables everytime I open the database? (so not depending of the question wheter it is updateble?

 

[MajP]

I believe that the only reason I had that check in there was to save time and only refresh the link of the ones not working. If you take the check out it takes longer.

 

[Niebotel]

Any idea how to "create" a bad link to a sharepoint list, so I can test?

 

[MajP]

No, but I think that code may be the solution even though your problem is different.

 

[Niebotel]

I tried to run the code but I get an error: saying something like: "The search key is not found in any record"(translated from Dutch)

Is it possible for me to look into TableDefs; how can I find them

 

[lameid]

Which line of code and what is the error number?

Not sure what you mean by look at Tabledefs... A tabledef object is an Access table.... The code is looping through them...

 

[Niebotel]

The error occurs on the second loop on statement:
DoCmd.RunCommand acCmdRefreshSharePointList
error number 3709

What I mean with Tabledef: It is an access table, but why do I not see it in the list of tables; I switched on to diplay the system tables but they als start with Msys...

 

[MajP]

What is interesting, the error
"The search key is not found in any record"

Is the error I received, prior to running the code. In my case prior to installing this code, I would try to access a table called "tblActivities" bound to a form. When the form opened I would get that error.
In my case it is like a broken link, but not really. You can actually verify the link exists but you cannot touch the data. That is why I had to refresh all my links to the sharepoint lists.

You should put an error check in this code to see the offending table

Something like

on error goto errLbl
your code
exit sub

errLbl:
if err.number = 3709 then
msgbox "Error 3709 caused by table: " & tbl.name
resume next
else
msgbox err.number & " " Err.description
end if


It sounds like you are saying that the link is actually completely lost and no longer showing in your tables.

 

[MajP]

To clarify there is a difference from relinking and refreshing. In my case I am refreshing the linked list, because the link was never lost.

BTW here is an expanded version with some notes

http://www.access-programmers.co.uk/forums/showthread.php?t=211688

If I get an opportunity I will look at doing a complete relink. I am unable to find any information on what refreshing actually does.

 

[Niebotel]

That would be very nice.
To be sure: A manual relink did solve the problem.
So if I have the code for that, that would be great!
(Still thinking when to run the code; every logon will be to much for the performance; perhaps a button in the settings menu of my application which can be clicked if any records are missing?)

Have a nice weekend!

 

[Niebotel]

I suppose you did not have a look yet?

 

[MajP]

Sorry, between work and taxes no time.

 

[Niebotel]

I understand; any suggestion where to get support?
Even de manual relinking does not work anymore.

 

[MajP]

what happens if you delete the link and then relink?

 

[Niebotel]

That works!