SharePoint Lists, MS Access and that pesky 5000 record limit.

John Sheehy
John Sheehy used Ask the Experts™
on
SharePoint Lists,  MS Access and the 5000 record limit.

So I am very familiar with the 5000 list limit with SharePoint.   And my SP admin will not increase that threshold.  I understand because of the performance.
But here is the issue I am facing and it's odd.

So I have two list in SharePoint.  tbl_Parent_Inventory and tbl_Child_Inventory.  They were created in Access first, with a one to many relationship from the Parent to Child.  Then upsized to SP.  I reached the 5000 limit with the parent table and it would not allow changes in the child inventory.  Which has not reached the 5000 limit.

I also have one other list, tblAuditTrail.  This list holds all the actions that have been performed on all the forms.  There is 24,000 records there.  I have no issue viewing these records in Access.  All 24,000 show up.  I can make changes to them and I can add more.  I can see all the records in the other two tables.  But I can not make changes.

So my thinking is the relationship between the two tables is the issue.  But I am not sure.  Has anyone else encountered this before and if so how did you overcome it.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
I don't know from first-hand experience, but am aware that indexing plays into how many records you can work with in a Sharepoint list.  If indexes are defined, it's possible to filter lists with more than 5,000 items.   However once you hit the 5,000 limit, if you have not defined indexes, you are stuck because you can't modify indexing.

 Also the 5,000 record limit is a default and it can be changed.  It's not something that is built-in.  There's some recommendations here:

https://support.office.com/en-us/article/manage-large-lists-and-libraries-in-sharepoint-b8588dae-9387-48c2-9248-c24122f07c59?ui=en-US&rs=en-US&ad=US

 From Microsoft on working with large lists.

Jim.
Distinguished Expert 2017

Commented:
And my SP admin will not increase that threshold.
Perhaps he would be willing to write a letter to that effect to the president of the company telling him that he has to stop accepting new business????
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
Maybe not accepting new business is a better choice than bringing all business to a stop.  The limit exists and was set to 5,000 by Microsoft for a reason.  I don't think we can second guess the admin's position.

The only way I'd modify that is if:

a. it was done to allow modifications to be put in place, then after those are done, put it right back.
b. in prep for a migration.

Jim.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

John SheehySystem Security Manager

Author

Commented:
So in researching this issue the 5,000 limit applies in the following cases:
When trying to view all the records with in SharePoint.
When trying to perfom a lookup via another Application.  In my case it was Access.

So here what happened.  Tables were designed in Access.  With a Primary Key in the Parent Table (hardware_ID) and the same filed in the child table.  Then linked via a one to many relationship.  So when I migrated them to SharePoint, SharePoint changed the Hardware_ID field in the child table to a lookup field to the Parent Table.  

So here's why we received an error.  The Access form was able to display the record.  And the subform that held the child table info was also displayed.  But when we went to add more records to the subform it would throw an error because the Hardware_ID, now a lookup, was outside the 5,000 record limit.

So here's the solution without having the SP admin change the limit.  (And if you have ever changed the limit, you know why they say not to)
I redesigned the tables and forms to be independent of each other.  The user doesn't see it that way.  Their forms look the same way.
How I maintained the "link" was when a new record was being added to the subform the on current event would copy the Hardware ID from the Parent for to the Hardware_ID field on the child form.

It may not be best solution, but it does work.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
Excellent.   Make sure you select your last comment as the solution.

Jim.
System Security Manager
Commented:
So in researching this issue the 5,000 limit applies in the following cases:
When trying to view all the records with in SharePoint.
When trying to perfom a lookup via another Application.  In my case it was Access.

So here what happened.  Tables were designed in Access.  With a Primary Key in the Parent Table (hardware_ID) and the same filed in the child table.  Then linked via a one to many relationship.  So when I migrated them to SharePoint, SharePoint changed the Hardware_ID field in the child table to a lookup field to the Parent Table.  

So here's why we received an error.  The Access form was able to display the record.  And the subform that held the child table info was also displayed.  But when we went to add more records to the subform it would throw an error because the Hardware_ID, now a lookup, was outside the 5,000 record limit.

So here's the solution without having the SP admin change the limit.  (And if you have ever changed the limit, you know why they say not to)
I redesigned the tables and forms to be independent of each other.  The user doesn't see it that way.  Their forms look the same way.
How I maintained the "link" was when a new record was being added to the subform the on current event would copy the Hardware ID from the Parent for to the Hardware_ID field on the child form.

It may not be best solution, but it does work.
Distinguished Expert 2017

Commented:
The solution seems to be the best that could be done given the circumstances.  Thanks for getting back to us.  No wonder the marriage of Access with Share Point never gained any traction.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial