Share via

Access frontend with SharePoint List backend?

Anonymous
2025-03-28T06:29:46+00:00

I am redesigning/adapting a data collection tool for an employee study at work. I've done this already twice before, but the study group has grown each successive time. The upcoming study will involve around 500 employees, most of which will be connected to the backend concurrently. A single Access backend will not be able to handle this many connections (255 hard limit but I've read that performance decreases well before that limit). Participants will only be submitting data entries through the tool; no editing existing records, but I am planning to give them the ability to delete their own entries in case they notice they made mistakes (previously I had them contact me to make edits if necessary, but that is a bigger hassle for both parties). Backend is fairly simple: an employees table to only allow access to people who need to partake in the study, an entry log table, and a few more tables for tasks and categories for filtering dropdown options on the tool. Entry form is not bound to a table; entries are submitted with VBA code.

I have two options to consider for how to host the backend. (Originally I thought SQL Server would be the best option, but after looking into it more, it's probably unnecessary, and since I don't have experience using it already, this is probably not a good time to learn and use for the first time; plus IT dept probably would not install it for me.)

First option is to split the backend into 5 separate backends for each group of participants. Each backend would have up to ~100 people connected concurrently. Hopefully that would be sufficient splitting since record locking should never be an issue (only submitting records and rarely deleting own records). Backends will be kept in a public network folder, and I am planning to upload the respective frontends to SharePoint for people to download (last time putting the frontend in a network folder for people to copy onto their workstations was messy, even despite having code that prevented them from using it directly in the network folder).

Second option which I am still unfamiliar with is converting the backend tables to SharePoint lists. I think this will avoid the issue with having 500 people all submitting entries simultaneously. There's also an issue of lag/latency when people work remotely connected to VPN, which shouldn't be an issue with SharePoint. One drawback is I believe SP lists don't support table relationships, so filtering tasks by category won't work like I've done it before. I'd have to store these as static lookup tables in the frontend, which would be fine if I don't need to change anything midway through the study, which I have had to do previously because supervisors and managers didn't have it all planned out well enough beforehand. Any such changes would require forcing users to download a new frontend version, which I've already devised a way to do with VBA.

People with experience connecting Access to SP, or split databases requiring more than 255 concurrent users, what do you suggest? In total, I expect the number of entries by the end to be in the tens of thousands, probably not over 10^5 but who knows. I've read that SP isn't good for over 5000 records, but not sure if that would be a problem when there will be little reading from/modifying the entry log.

Microsoft 365 and Office | Access | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

32 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. George Hepworth 22,765 Reputation points Volunteer Moderator
    2025-03-28T17:31:13+00:00

    As theDBguy said, if this project is valuable to the organization, it's worth investing in a viable solution.

    My suggestion is the same as theDBguy's. Involve IT in the decision and planning. SQL Server Express is a free version of SQL Server that should be more than adequate for this application. Setting it up for you would not challenge most IT departments.

    5 separate back ends would be the very last option I would consider, if all else falls through. Making it work would take a lot more effort than implementing either a SQL Server back end or setting up SP lists, IMO. Synching that data would be challenging.

    SharePoint is very iffy, if I'm doing the math right: 10^6 records would be 1,000,000 potential records. Performance with SP lists would bog down well before you got to that point.

    I can't say this enough, if what you need to do matters to the success of your organization, then an appropriate budget and reasonable solution are important as well. And that means you need to bring in the players who have the juice to make it happen and the ability to allocate the budget to make it happen.

    Keep in mind that you are on the third iteration of the study. That means you have the knowledge needed to design the application even if you require input from others in your organization to bring it to success.

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2025-03-29T00:27:34+00:00

    Oops, I meant 10^5 (100,000) as an upper limit. Not sure how much the list size will affect performance since people will mainly just be adding records to it. If it would be an issue, I could create a new list at the end of each week and rename the lists so that the following week's entries will input to a new empty table. Actually not sure if that would work. Seems like once you push the table up to SP, it link to the SP url, so changing the name of the SP list or the local table won't affect the connection. Probably leaning towards the separate Access backends at this point.

    0 comments
  3. Anonymous
    2025-03-28T15:57:45+00:00

    Realistically, I don't think SQL Server is an option for me to use. Maybe we can have someone in IT set up an SQL Server database for us and connect the Access frontend to it, if someone there wouldn't mind taking on a little extra work. I just know they likely won't be willing to make exceptions like this to normal business processes, as is the case with most IT departments. So I'm just trying to devise a solution with the tools I have readily available. If SharePoint is iffy, then is the 5 separate backends a reasonable solution? I just worry about people having problems when connecting through VPN.

    0 comments
  4. DBG 11,711 Reputation points Volunteer Moderator
    2025-03-28T15:12:42+00:00

    I agree with George. You should talk to your IT or to someone who can talk to them and find out if SQL Server is an option or not. If what you're doing is important to the organization, maybe they could justify investing in SQL Server. From what you have described, it might be a better option than SharePoint.

    0 comments
  5. George Hepworth 22,765 Reputation points Volunteer Moderator
    2025-03-28T14:17:01+00:00

    As much as I love Access, I wonder if it's the right tool for this project.

    "The upcoming study will involve around 500 employees, most of which will be connected to the backend concurrently."

    " In total, I expect the number of entries by the end to be in the tens of thousands, probably not over 10^6 but who knows"

    That would be no sweat for a SQL Server, or other RDBMS, database, but that's well beyond the scope for Access and I have serious doubts about using SharePoint lists in that scenario as well. I don't know what the hard limit on concurrent users for SharePoint lists would be, though. A quick search didn't turn up a number either. Perhaps there is none theoretically, or it could be quite large theoretically, but practically, I am not convinced yet.

    The ultimate number of records suggested could put you at the limit of acceptable performance with SharePoint lists.

    That said, here's a video on setting up SharePoint lists for Access . Note that although Referential Integrity between SharePoint lists is not established with Primary and Foreign Keys, the Lookup Field mechanism, properly implemented, provides a functional equivalent.

    https://youtu.be/Uw6JBJfcjCo?si=mAf5BOj75BpBo37r

    IMO, either you need to bite the bullet and get up to speed with SQL Server, or move entirely to a browser based application.

    0 comments