Where to put a back end

[Ganymede]

I have built a database that will be used by multple users in my organization (sometimes simultaneously). I have read that the best way to go about this is to split my database into a back end (with all the tables) and a front end (with all the objects and queries and links to the tables from the back end).



I understand that the backend needs to be stored on a network folder where everyone has read-write permissions. I suppose my question becomes what qualifies as a network folder? What about Sharepoint/Teams? One Drive?

[June7]

Definitely not OneDrive. Also think SharePoint is not appropriate. Common topic, search internet.

[mike60smart]

Hi
You should have a folder on your Server which contains both the FE & the BE
Every User will then have a Copy of the FE on their individual PC's

Th following https://support.microsoft.com/en-us/...3-51b1d73498cc explains the process.

[Micron]

The network connection should be wired to minimize corruption. Access over WIFI would be a bad idea. If users are remote, then you need Citrix or whatever they're calling Terminal Services now.

[Ganymede]

Definitely not OneDrive. Also think SharePoint is not appropriate. Common topic, search internet.

Can you tell me why SharePoint is not appropriate? I've read conflicting reports on it. Some say it's no good becasues it has a 5,000 record limit. But others say that only applies if you open the list in web view. (See discussion here.) Is there some other reason to avoid SharePoint? It would be the easiest solution for me and my team. We don't have traditional network folder as far as I'm aware. Everything is on teams.

[Welshgasman]

Hi
You should have a folder on your Server which contains both the FE & the BE
Every User will then have a Copy of the FE on their individual PC's

Th following https://support.microsoft.com/en-us/...3-51b1d73498cc explains the process.

I would not have the FE on the server, if users can get to it.

[June7]

I've never used SharePoint so my understanding is limited. I think SharePoint was intended to be a way to serve Access database on the web. Review https://support.microsoft.com/en-us/...1-322ddbcf5411. Web database construction tools were removed from Access in version I think 2010.

But if the linking described in the reference you posted really works for multi-user interaction, go for it.

[Gustav]

Sharepoint and OneDrive don't work because you actually read/write a local copy which gets syncronised with the server in a way that Access can't handle.
So, use a file share known as a SMB file share. Any Windows Server offers that as well as most NAS units. Users must have read/write/delete access to the shared folder.

Also, users' workstations must have wired LAN access; Wi-Fi access will in most cases fail sooner or later.

As for distributing the frontend, do create a strict process for that. See my article:
https://www.experts-exchange.com/art...X8LitkMCd/Y%3D

[ArviLaanemets]

Keep back-ends and front-ends in same LAN.
Back-end on shared folder on some server;
Front-ends either on every user's computers (only when the user always uses the database from same computer and in this LAN);
or on network resources dedicated for specific users, or on Terminal Server profile for this user (in case the user may work from different computers in same LAN, or from outside of LAN the BE is situated on, using some kind of terminal connection).

This design is least prone for data corruption, as all data exchanges between FE and BE are made in same LAN.
I myself would prefer the design where FE is on shared server resource, and BE's are on user-dedicated server resources, as then all database data exchange does happen between server resources - the data exchange is fastest possible, and the risk of breaks in data exchange will be minimal.
And in case of terminal use of database, all data exchange between FE and BE will happen in same LAN. Between user's computer and copy of FE in LAN, only keystrokes, mouse movements and clicks, and screen images are exchanged.

And btw. your LAN can be composed of different geographical locations, in case you have the connection speed and parallel connections supporting this. My last firm I worked in used servers outsourced from some provider, had multiple factories in different countries with local sublans in those locartions, and had all those connected as single company LAN. This worked OK!

[Ganymede]

Sharepoint and OneDrive don't work because you actually read/write a local copy which gets syncronised with the server in a way that Access can't handle.
So, use a file share known as a SMB file share. Any Windows Server offers that as well as most NAS units. Users must have read/write/delete access to the shared folder.

Also, users' workstations must have wired LAN access; Wi-Fi access will in most cases fail sooner or later.

As for distributing the frontend, do create a strict process for that. See my article:
https://www.experts-exchange.com/art...X8LitkMCd/Y%3D

I will look into SMB fileshare. However, unfortunately everyone in my office is on wifi. And I mean everyone. Our laptops don't even have ethernet connectors. Fortunately the database I built is just intended to be an interim solution while a professional company builds a database for us. But this will have to last about 15 weeks.

What will cause the db to fail? What if I arrange the records in a way so that no more than 1 user is in a record at a given time?

[jojowhite]

Wifi is a no, no. Your db is susceptible to corruption.
You need a Wired Lan connection.

you can create a pwersonal network using simple
instruction as like in the video:
How To Setup A Local Area Network And Share Resources

[ArviLaanemets]

... However, unfortunately everyone in my office is on wifi. And I mean everyone. Our laptops don't even have ethernet connectors...

Then only foolproof solution would be all FE's stored on LAN folders or in TS profiles for users, and any user must working with DB only from his/her TS profile (I hope connections between your servers aren't through wifi )

[Welshgasman]

Our laptops don't even have ethernet connectors

Wow, what make and model are those?

[Ganymede]

Wow, what make and model are those?

Thinkpad X1.

[Welshgasman]

Thinkpad X1.

Thank you. Ones to avoid, if I go looking for a new laptop.