1

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?

Ta

phil

-1

Just to say i have got it to work in SharePoint but it is quirky to say the least.

Sorry i should expand - the real issue is that Access was caching the tables and for some reason this made it almost unusable. Also having the Access file on a shared OneDrive folder also seemed to cause issues.

1) Go to Options / Current Database and tick NEVER CACHE under Caching Web Service and Share Point tables.

2) Get users to take a personal copy of the Access data file and save it locally on a drive that is not shared with others through OneDrive

Ta

phil

We're looking for long answers that provide some explanation and context. Don't just give a one-line answer; explain why your answer is right, ideally with citations. Answers that don't include explanations may be removed.

  • 1
    Please add complete answer. How you have resolved your problem. So that other users can benefit from it. – Ganesh Sanap Jan 12 at 17:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.