I am assisting a friend by moving him from managing a business entity from Excel to Access. Because he desires the data to be viewed/edited from multiple locations, I suggested a MS Access database on the front end with linked tables to a backend Sharepoint site or SQL server. So, I created the Access 2007 database and am now ready to set up the backend. That's where I'm stuck. I tried Sharepoint Online. I managed to move several tables containing between 200 records to more than 20,000 records to the Sharepoint Online public site. I was then able to link to these tables and view the data within Access. I thought I was done. However, I have run into a problem. When I used the linked data, I run into the problem of "this recordset is not updateable." After researching this problem, it was suggested I create indexes on columns in Sharepoint. So, I tried to do that but I get an error saying I exceeded my list threshold. I am aware of the 5,000 record limit. So, I adjusted the tables on the Access end making sure indexes existed for both lists with the primary key being on an auto_number (which it says it requires) and re-exported/replaced the lists to sharepoint. When I checked the indexes on the new list tables - none exit. When I try to add them, I get the same "exceeded list threshold" error. It's odd, because other tables accepted the Access defined indexes - those with fewer than 5,000 records. So, am I missing something here? Why won't Sharepoint accept my indexed fields from Access on tables with greater than 5000 records? And/or why am I unable to create new indexes in Sharepoint for these given lists?
You cannot index a list that exceeds the threshold.
This is explained in this Microsoft article: Manage lists and libraries with many items
Because creating an index requires accessing all items in the list, it's possible you may be blocked from creating an index for a column if the entire list exceeds the List View Threshold. If this occurs, do the operation during the daily time window, or contact your administrator.
Create the index before the list reaches the threshold. Then you can add more items.