Forum Discussion

SpreadsheetHub's avatar
SpreadsheetHub
Copper Contributor
Jun 30, 2023

MS Access & Sharepoint limitations

Hey everyone,

I've got a topic I'd like to discuss that relates to my MS Access database. The situation is that I need to make it accessible remotely for multiple people scattered across Europe. Now, I know there are two options I can explore:

  1. Splitting the current database into two files, the Front End (FE) and Back End (BE). I'd store the BE file in the cloud and keep the FE file on my local machine.
  2. Rewriting the whole thing in SQL and storing it in MS SQL or something similar.

My main focus right now is on the first option. But here's where I need your help: If I want to store the BE file in SharePoint, do you know if it can be done with SharePoint Online or if I need SharePoint Server? And how do I go about setting it up so that the local files stay connected to the backend at all times? My ideal scenario is to have multiple people with their own FE files, and any changes they make should update the BE in real time.

I've spent ages searching for answers and even talked to support, but I just end up confusing myself more each time. I'm really looking for straightforward information rather than opinions.

If any of you have any insights or can lend a hand, I'd greatly appreciate it.

Thanks!

access
Access Database
Access Web Database
SharePoint
  • Karl_Donaubauer's avatar
    Karl_Donaubauer
    MVP
    Jun 30, 2023

    Hi,

     

    Just briefly to your option 2:

    > Rewriting the whole thing in SQL and storing it in MS SQL or something similar.

     

    You can't rewrite a (frontend) Access application in MS SQL Server or online Azure SQL database. SQL Server can just hold the data and some basic logic in respect to data. It is not a frontend tool to provide a UI for users and the according programming. So, you would need a development environment for the frontend also for this option.

     

    Sharepoint:

    Yes, you can use Sharepoint online as it is provided in most Microsoft 365 subscriptions or as a separate subscription as data storage for an Access frontend. Technically, this basically works as described in this support article.

     

    I have been running such a complete (relatively small) client application (+ a few other very small projects) for about 8 years and have done a lot of tests and several presentations about the subject (most of my slides etc. are available online only in German though).

     

    However, there are some limitations and weaknesses of the Sharepoint connection in Access that you should be aware of.

    The biggest limitation from a practical POV is the limited amount of data you can manage with usable performance with Access+SP. This goes well up to a few thousand records per table, with some knowledge and optimization even with a few ten thousand records. More is not possible with this data connection, at least not with acceptable performance.

     

    In addition, there are some problems and necessary tricks with the conversion, with the performant design of the table structure, regarding action queries, the data accesses etc.. That would lead too far here. For more information you can watch the AUG Pacific video with Albert Kallal, who has a lot of practical experience with it.

     

    p.s. I don't know if for you, but in principle there is a third option: Providing the Access application in a virtual online machine, e.g. on Azure. You can find a presentation about this by George Young at DAAUG.

     

    Servus
    Karl
    ****************
    Access Bug Trackers
    Access News
    Access DevCon

Resources