i'm completely new to sharepoint. I had a fairly complex Access 2016 database where there was a "backend" Access database with 15 tables, a Access "admin" frontend with lots of queries for reporting, and another Access frontend that has a single form for staff to enter their data. This form looks up two values in the tables, and writes the appropriate reporting data into a third table.
I have migrated all the backend tables to SharePoint lists to facilitate a move to getting rid of our on site file servers and moving to the cloud using OneDrive. A few of these new SharePoint lists are quite large with 20,000 - 40,000 rows. I've linked the two front end access databases to these sharepoint lists, and popped indexes on the key ID columns used to link the various tables.
Now i seem to have two major issues:
1) it appears that only one member of staff can use the front end access database at a time, as if all the tables are locked rather than just the few rows in use.
2) Some users can access only some of their records but not all, whilst other users can access those records that the first user can not access. For example when I put in the appropriate details into the form, it brings up the correct information as expected. But when my collegue puts in the same details, in the same form, she gets a blank (unmatched/not found). Even so my collegue can open most of her other form details as normal so its not her skills, nor her permissions in SharePoint that are the issue.
This is very troubing and I’m at a complete loss
Do you have any thoughts?
Is this too much to ask of sharepoint, and should i consider a proper SQL server?