Small DB, 5 users, what would be the best solution? (1 Viewer)

[Captain Frog]

Hey!
I have developed a DB for my Sailing club. Nothing fancy nor big. About 230 entries for members and boats. The club has a MS 365 abo with SharePoint for 5 users, but actually only 3 person need access to the DB (President, Cashier and Harbor master).
Until now I did not split the DB. It is stored on SharePoint and when someone wants to change data, he has to download it to his local machine, make the need changes and upload it again to SharePoint with a new Name (YYMMDD).

What would be the simplest and cheapest solution if I wanted to split the DB and have the data in stored centraly (SharePoint?) and every user having a front end installed on his machine?

Please keep it simple as my knowledge about network, SQL server, Azure and the like is limited.

 

[theDBguy]

I think your requirements is well within the limits of SharePoint. What you could do is convert your tables into SharePoint Lists and then link them to your Access database. That way, all users would be able to edit the data simultaneously, and you can avoid the process of downloading and uploading the Access file each time a change is needed.

 

[Minty]

The alternative would be a really simple remote access set up, if you have a machine that is on 24/7 and can be configured suitably.
If not that would require hosting somewhere, then it can get a bit expensive, so Sharepoint would be the way to go.

 

[GPGeorge]

Hey!
I have developed a DB for my Sailing club. Nothing fancy nor big. About 230 entries for members and boats. The club has a MS 365 abo with SharePoint for 5 users, but actually only 3 person need access to the DB (President, Cashier and Harbor master).
Until now I did not split the DB. It is stored on SharePoint and when someone wants to change data, he has to download it to his local machine, make the need changes and upload it again to SharePoint with a new Name (YYMMDD).

What would be the simplest and cheapest solution if I wanted to split the DB and have the data in stored centraly (SharePoint?) and every user having a front end installed on his machine?

Please keep it simple as my knowledge about network, SQL server, Azure and the like is limited.

I agree that SP lists would be the more direct path.

Every users gets a copy of the Access accdb FE. All of the FE's are linked the SharePoint lists. It's a good solution for small database applications.

Watch this video presentation on setting it up

 

[Isaac]

sharepoint lists is good, if it works - I worked for a company a couple years ago where I had ENDLESS problems with non-updateable recordsets in sharepoint lists and views. any time there was [insert several types of fields, including People fields] in the list/view, the whole thing became non-updateable from Access. your mileage may vary, but the glitchiness seems limitless with access-to-sharepoint when trying to update

 

[Pat Hartman]

Much as I dislike SharePoint lists, this a good use for them. The row count is very small and the number of concurrent users is also small. There is one other important thing you need to implement. That is a version table as a SharePoint list and a version table as a local table in the FE. Since you can't rely on non-technical people to do the right thing (or even technical people sometimes), you, the developer, need to ensure that the users are always using the proper FE. If you make changes to the app, you can upload it to a SharePoint folder and the users can download it to their desktop from there. When the FE opens, code compares the version in the FE version table with the version in the BE (SharePoint) version table. If they match, the app opens. If they don't match, the user gets a message to download a new FE before continuing and the FE closes.

 

[GPGeorge]

@Pat Hartman

Much as I dislike SharePoint lists, this a good an acceptable use for them.
Fixed it for you.

 

[Isaac]

Plus, depending on what gadgetry your company has signed up to pay for, you may be able to complement your Access+Sharepoint system with an "ETL" or "workflow" system of sorts - Power Automate/Flow. I'd look into it, it's quite enjoyable and useful for triggering stuff like email reminders, automatic trigger updates, etc.

And Flow is truly made to work with Sharepoint, unlike (as of recent years unfortunately), Access.

 

[CJ_London]

Just curious as I have very limited experience with sharepoint. If you have a sharepoint folder, can you not store an access back end there and link to it from a local front end?

 

[Isaac]

This can work if the sharepoint folder is mounted as a drive for all users (or if your preferred startup widget does that), but trust me, it's glitchy as heck. Kinda like network/wifi/wan

 

[GPGeorge]

Just curious as I have very limited experience with sharepoint. If you have a sharepoint folder, can you not store an access back end there and link to it from a local front end?

Not safely.
You'll end up playing "last change wins" among all of the potential users, rather than "share and share alike".

 

[CJ_London]

Thought there might be a reason since no-one was suggesting it!

 

[Captain Frog]

Well first of all thank you for all these information. Sharepoint lists seems the way to go.!
I do have nevertheless some questions about user access.
Until now my DB is build so that users never see the tables (only forms and reports) and I did implement a simple user rights access with password (stored in a "tblUsers") as an opening form so that only some users will be allowed to make any change in the data.

Will it still be possible to limit acces to data once the tables are uploaded as list in sharepoint?
The 5 users do login to MS365 abo with a different account name and password for the club. Will I have to install the FE separatly on every single computer? ( means will the link path to the lists be different for each user?).

 

[GPGeorge]

Well first of all thank you for all these information. Sharepoint lists seems the way to go.!
I do have nevertheless some questions about user access.
Until now my DB is build so that users never see the tables (only forms and reports) and I did implement a simple user rights access with password (stored in a "tblUsers") as an opening form so that only some users will be allowed to make any change in the data.

Will it still be possible to limit acces to data once the tables are uploaded as list in sharepoint?
The 5 users do login to MS365 abo with a different account name and password for the club. Will I have to install the FE separatly on every single computer? ( means will the link path to the lists be different for each user?).

The interface does not need to change.
Yes, everyone must have their own copy of the accdb FE on their own computers.
No, the links are to the same SharePoint lists, so they will be the same for everyone.

Please watch Albert's presentation. It's the most comprehensive I've seen.

 

[Pat Hartman]

Just curious as I have very limited experience with sharepoint. If you have a sharepoint folder, can you not store an access back end there and link to it from a local front end?

No. Doc has written extensively on why "cloud" drives can't work as a shared BE unless the users have a way of communicating with each other so that a second person NEVER opens the BE at the same time that the first person does - even just to read. Remember, Access updates the BE when you just open the database. Notice that the update date constantly changes whether you updated anything or not. That forces the "cloud" drive to write over whatever is there.

 

[nector]

The solution here is to have a proper MS SQL Server Cloud database managed by a professional company such as winhost.com you will be able to get your desired results.

I have never believed in SharePoint especially where you have users scattered across the cities.

 

[GPGeorge]

The solution here is to have a proper MS SQL Server Cloud database managed by a professional company such as winhost.com you will be able to get your desired results.

I have never believed in SharePoint especially where you have users scattered across the cities.

There's definitely a more limited profile for SharePoint as compared to hosted SQL Server or SQL Azure.

However, if an organization does have maximum 5 or 6 users and no more than a few thousands records, it can be a viable option. Many, many years ago, I worked with a developer who volunteered his time supporting a local non-profit. The non-profit had a branch somewhere in North Africa. I don't recall the country. One user there needed to access the data in their Access database. They used SharePoint lists for the back end. It worked. I won't say it dazzled anyone, but they were able to operate that way.

Today, I'd probably urge them to mature into a hosted SQL Server or Azure database and probably a web-based app, but with non-profits, costs are always a significant consideration.

 

[Steve R.]

What would be the simplest and cheapest solution if I wanted to split the DB and have the data in stored centraly (SharePoint?) and every user having a front end installed on his machine?

You have asked an open question. Since you have posted here, I believe that you are probably asking for an MS Access solution. Nevertheless, going beyond your initial question there is non MS Access approach that would meet some of your criteria. Especially concerning the word "cheap". That would be to use an opensource database such as MariaDB in conjunction with Apache (HTTP Server) and a web browser. Essentially this will give you a web style access to your database on your local LAN.

Besides cost savings, a major advantage is that you would not need to have a front end installed on any computer. The major problem with using a web style application to access your database is that it would require a lot of study, work and time to create initially. So if you do not feel-up to the technical challenge don't even try it. Additionally, if you are already fully committed to a Windows and MS Access based computing environment, I would not recommend trying to implement this approach.

 

[Pat Hartman]

What would be the simplest and cheapest solution if I wanted to split the DB and have the data in stored centraly (SharePoint?) and every user having a front end installed on his machine?

SharePoint as the BE and Access as the FE is the cheapest solution since that is what you already have. If you are capable of creating a Web app, that would allow you to provide access to the database to a wider pool of users than just your 5. This website for example serves thousands of users, dozens or even hundreds at a time during peak hours but web solutions are not inexpensive, nor are they easy to create. If you are not in a position to recreate the FE using a different code platform, there is no reason to go off down the rabbit hole of any other BE solution other than SharePoint which you already have and I presume intend to keep.