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....
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