UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Threshold Message, SharePoint Server 2010    
 
   
Brepea
post May 4 2018, 08:17 PM
Post#1



Posts: 559
Joined: 11-January 09
From: UK


Hi All

I have all of a sudden began to receive "disconnected from sharepoint tables" message with Access (FE) 2010 and SharePoint (BE) 2010. I've gone into my audit table and the attached message is displayed - would this threshold be cause for disconnected tables from SP? Is it just a standard warning. As i understood - the threshold limit is just about when you are trying to view data within the table - so if you were to run a query with more than 5k rows - then it will lock out - but you can have as many rows as you want in a list (just make sure the queries, etc. don't show more than 5k) - is that right?

Attached File  Threshold.png ( 36.22K )Number of downloads: 7


Thanks
Go to the top of the page
 
RobKoelmans
post May 5 2018, 02:40 AM
Post#2



Posts: 456
Joined: 25-November 14
From: Groningen, Netherlands


here iit says that you need to add filters https://sharepoint.stackexchange.com/questi...llion-documents
We only got it above the threshold by using a bcs (business connectivity servicce). If a normal document or list folder got over threshold, it left out the last records of the queried selection.
Hope this helps (a little).
Rob
Go to the top of the page
 
Brepea
post May 5 2018, 03:10 AM
Post#3



Posts: 559
Joined: 11-January 09
From: UK


I'm not sure I understand. In my example the list concerned just records changes as they happen on certain fields within the application (so it's never "called" in a view/ query - but if it was I would naturally only call data from it where an ID was equal to some number).

Go to the top of the page
 
RobKoelmans
post May 5 2018, 03:43 AM
Post#4



Posts: 456
Joined: 25-November 14
From: Groningen, Netherlands


I expect MS-Access does one way or another. How exactly did you connect to SharePoint from within MS-Access? Knowing that, I can perhaps ask around to some experts.
Kind regards,
Rob
Go to the top of the page
 
Brepea
post May 5 2018, 04:50 AM
Post#5



Posts: 559
Joined: 11-January 09
From: UK


I have a SharePoint site which has lists in it (these lists i use as "tables" in Access). I "link" to the lists via external tab in Access...Access/External Data/More/SharePoint Lists - enter my SharePoint site and then it shows all lists that i am able to link to...I never know if it is a requirement to include lists generated by SP - for example - UserInfo (i never include it - but i wonder what the implication of not including it is [if any])...

I must add what i've seen happen recently: So using .accdb in design mode it shows as "online with SharePoint" as you'd expect. I compile the application and save as .accde (i've been doing this for a year now without issues). I obviously distribute .accde to all users. Since i've been experiencing the "disconnected from SP tables" message - i can run the compiled (or .accdb - doesn't make a difference which i run - the same outcome) and launch it fine - ="online with SP". I exit the application and relaunch - and it still shows "online with SP". I make changes and save these - all fine = "Online with SP". But if i do these say 3 or 4 times in a row the "disconnected from SP tables" shows. I click to "synchronize" and it synchronizes. I then relaunch and sometimes it "online with SP" and sometimes it's "disconnected tables"...seems quite random. The company has had some similar issue in the past when i spent a full 4 days trying to resolve something with my application - and then on the 5th day the application just started working normally (it was raised with corporate IT of course who then must have rolled back some update). At present i am just trying to ensure there is nothing wrong from my application side... and the only difference i've seen (as i've made no structural or code changes to make the application respond like this) is this threshold message - and about me understanding it and how it will effect my application (if at all).
This post has been edited by Brepea: May 5 2018, 05:00 AM
Go to the top of the page
 
RobKoelmans
post May 5 2018, 06:10 AM
Post#6



Posts: 456
Joined: 25-November 14
From: Groningen, Netherlands


The problem seems to be that MS-Access links to a folder, not to one of the views from that folder.

This is a good article on it. You may have found it yourself already: https://sharepointmaven.com/how-to-overcome...imit-threshold/

In your case I would definitely use the BCS. Those numbers of items that you have require the serious stuff. In BCS you actually perform a query on the source data also from SharePoint. The content in the SharePoint list is actually retrieved from the database dynamically. The SP list only 'contains' the data that you requested, so to speak. I doubt MS-Access can link a BCS ListFolder but you wanna connect to the SQL-database directly anyway of course. Watch out for limitations on BCS Listfolders in comparison to standard ListFolders first, especially on Workflow. You can also attach workflow to the database tables or use OnCreate triggers there.

Sorry for not having better suggestions. I think, most of the time the Access link is just adding or deleting single items on both sides most of the time and performs a complete rebuild in SharePoint now and then (giving you the warning).

The easy way is splitting up the ListFolder of course, as the article in the link suggests or increase the threshold.

Rob
This post has been edited by RobKoelmans: May 5 2018, 06:20 AM
Go to the top of the page
 
Brepea
post May 5 2018, 08:42 AM
Post#7



Posts: 559
Joined: 11-January 09
From: UK


Rob - that links references as per my initial post - that the threshold is about viewing the data in said list. I am not sure what you mean when you reference a folder... I simply have my site ...sharepoint/database application/ lists - each on of those lists acts like a "linked-table" (if using Access-Access split application - but instead in this case it's lists to SP).

Therefore - I can only conclude that any message relating to threshold (and this message is not received from Access interface in any way - but only when you browse to the specific SP list - and then it doesn't pop-up as a message - it's just displayed on top under the list name (so in list settings) - has nothing to do with a reason behind why the "disconnected from SP tables" notification keeps taking place. I think this has to be related to some update the organisation has implemented without thinking about the potential effect it has on older applications (some users are being migrated from 2010 to Office 365).

If you recall - i mentioned we had a similar issue beginning March 2018 - a stressed out time of trying to establish what the issue was with my application, only to eventually work out that some update was applied and once rolled-back it worked normally (but no one in the IT team could tell us what the specific update was)...? ""I think"" something similar could be happening now.

I am not sure what BCS is...?
Go to the top of the page
 
RobKoelmans
post May 5 2018, 09:15 AM
Post#8



Posts: 456
Joined: 25-November 14
From: Groningen, Netherlands


Regarding from what I see on blogs en supportsites, your problem is quite widely known. https://social.technet.microsoft.com/Forums...ceitproprevious

There's mention of a patch in a couple of them, but I doubt this is stell relevant in your case. A lot of blog articles on the issue have been cleaned out but are still referenced at other places.

If you look at the causes they sum up for getting issues like this, you can see that "always worked well and now suddenly it doesn't anymore" was included in the architecture.

BCS is Business Connectivity Service (mentioned in my first response).

You may be right of course, but I doubt this is caused by some external recent event. It's also my prime suspect most of the time, but it hardly ever is in my experience.
Kind regards,
Rob
Go to the top of the page
 
Brepea
post May 5 2018, 02:30 PM
Post#9



Posts: 559
Joined: 11-January 09
From: UK


Thanks for the help all - i've just logged out to corporate pc and now all of a sudden everything is working fine again!!
Go to the top of the page
 
RobKoelmans
post May 6 2018, 05:36 AM
Post#10



Posts: 456
Joined: 25-November 14
From: Groningen, Netherlands


Good. With a bit of luck you'll be fine a couple of years again.
Kind regards,
Rob
Go to the top of the page
 
RobKoelmans
post May 6 2018, 05:36 AM
Post#11



Posts: 456
Joined: 25-November 14
From: Groningen, Netherlands


Oops, pressed Continue (Submit now) twice by accident.

This post has been edited by RobKoelmans: May 6 2018, 05:39 AM
Go to the top of the page
 
AlbertKallal
post May 6 2018, 01:55 PM
Post#12


UtterAccess VIP
Posts: 2,621
Joined: 12-April 07
From: Edmonton, Alberta Canada


If you are running a “on-site” instance of SP, then those limits can be turned off. However if it is a office 365 hosted version, then I don’t believe the limits can be turned off.

So the query limits are a setting in SP – they can be turned of for on-site instances of SP.

Regards,
Albert D. Kallal (Access MVP, 2003-2017)
Edmonton, Alberta Canada
kallal@msn.com

Go to the top of the page
 
GroverParkGeorge
post May 7 2018, 08:48 AM
Post#13


UA Admin
Posts: 32,586
Joined: 20-June 02
From: Newcastle, WA


This is probably a good time to take the SharePoint Admin for your site out for coffee and a team-building conversation. As Albert pointed out, the site admin controls its configuration; you need to be on the same team when it comes time to deal with problems of this sort.

--------------------
Go to the top of the page
 
Brepea
post May 7 2018, 04:21 PM
Post#14



Posts: 559
Joined: 11-January 09
From: UK


But to clarify - we're talking about "anything becoming an issue" only when you're trying to reference - via a query (or whatever) 5k+ records at one time -right? I doubt i will ever have the scenario where i'm trying to view the output of 5 rows - ever. So am i right in saying this 5k threshold will not impact the application...? So even if my audit table grows to 30k records - as long as i don't need a view/query for >5k records we're fine!?
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    26th May 2018 - 07:04 AM