How to allow other access applications to connect to a sql server dB installed on one computer?

[ezybusy]

Hi guys, I am planning to use MS SQL SERVER as the backend dB and MS ACCESS as frontend application that connect to the backend dB.
4 users will connect to the backend dB from their front end application from different computers.
In short we are going to have 1 central Backend dB, 4 frontend applications (each placed on a different computer, so 4 computers).

The plan:
I am planning to Install the SQL SERVER backend dB on one of the computer (with its own frontend application) then let the other frontend applications placed on the other computers have access to the backend dB installed on the first computer.



My questions:
1 - Is it possible to achkeve what i described above?

2 - If yes then please what are the hardware requirement? (Kindly note that we have router which allow our clients to browse the Internet).

3 - What are the steps to successful set up the structure explained in the plan?

I would be very glad to hear from someone who has successfully completed such work before. Thank you in advance.

[orange]

A few more details would be helpful.
-what are the 4 applications? in business terminolgy, plain English
-do you have a database design (normalized structures)? can you show us a jpg/png
-do you have a LAN/ shareable server?

What exactly does this mean?
What are the steps to successful set up the structure explained in the plan?

You may get some ideas/insight from links here.

Good luck.

[ezybusy]

@Orange.
- By application I meant a frontend interface for users. So there will be a version of that frontend interface on each of the 4 computers. That's what I termed as 4 applications.

- Yes I have a database design. Everything created in SQL SERVER. (Kindly find attached image).

- I don't really know what you mean by LAN/Shareable server, but we do have a LAN which give us the Internet.

- I am planning to Install the SQL SERVER backend dB on one of the desktop computers; then allow the other frontend Interfaces placed on the other 3 computers do read/write action to the SQL SERVER backend dB installed on the first computer.

Sorry for my bad English.

[orange]

I don't really know what you mean by LAN/Shareable server, but we do have a LAN which give us the Internet.


All I mean is that the BE database has to be in a location that can be accessed by each of the FE databases.

Your English is fine. It's just a few details for clarification that I was seeking. I don't know exactly what is in your plan.

[ezybusy]

All I mean is that the BE database has to be in a location that can be accessed by each of the FE databases.
That is my problem. I don't know how to do that.

[ArviLaanemets]

Are all of those 4 front-end users in your LAN, or some are connecting from WAN (i.e. from somewhere over internet)?

PS. To put Orange's question abour shareable server more directly - why do you want to install SQL Server on some computer (as follows from your post), instead insatlling a SQL Server on some server hardware in your LAN? The main difference is, that server is available for 24/7 (occasional shutdowns excluded), for simple computer you can't quarantee this easily.

[ezybusy]

@ArviLaanemets

All frontend user will be on the same LAN Network.

I am planning to use sql server as the backend because I don't want anyone to mess up with the backend database. Access file can be open easily with a simple double click, and no need for any deep know to change or modify the actual data. Meanwhile with sql server things seem to be more protected as on need some database knowledge to play in sql server.

Beside I red somewhere that access is restricted to a max size of 2gb. So instead of planning for an upsizing later on, I want to setup everything once and for all.

I want to believe that the above are good enough reason for my choice.

Now since my business is a small one with I don't have enough money to buy a server, so I want to use a normal desktop computer where sql server will be install then use some tools to prevent that desktop computer from going into sleep/hybernating mode.

My problem is how to allow the frontend user to connect to that backend database? I have no clue as to what to do.

[ArviLaanemets]

So you don't have any servers at all - i.e. you have only a router, which is working as DHCP-server for your LAN, and as firewall, and gives your the access to internet, and it is all?

Maybe you have to look at Azure? At some time I tried designing a Access web app there (but it was too limited for my taste), and I'm almost sure you can have SQL Server there too (no idea which limitations you have there though). Maybe someone else here has some experience with it?

[ezybusy]

So basically what am planning to do can't be done using sql server and access?

[ArviLaanemets]

I'm not saying it is impossible, but I'm out of my comfort zone here. I never have developed applications not to be used in Windows Domain (all rights are managed through Domain User Groups, Network resources mapped, etc.) except a couple of cases designed for use on single computer.