best way to have 2 remote users connect to Microsoft Access database concurrently
Good day! My Access-SQL experience is limited to enterprise client-server applications with an inhouse database server. For my next project I need to create an Access or SQL Server database that will be populated by a user in another country. Eventually there will be a FE to connect 8-10 remote users from accross the USA and Canada. For quickest availability should I use a managed hosting service like accessdatabasecloud.com or accesshosting.com? What would it take to host the database myself as I am invested in this project for the long term? I found a few older posts but nothing recent. Thank you kindly for your advice.
As John said, run RDS (Remote Desktop Services).
Note however that your app might need a few adjustments. On a RDS server, everyone has the same "C" drive (so you need to be careful with things like temp files), and will be sharing the same printers possibly.
The last thing is that everyone will still need an individual copy of the front end, so you'll need to create a mapped drive letter based on their login name.
Other than that, it is pretty much plug and play, and it works well even in low bandwidth situations.
With an RDS server, everything is running on the server and only KVM (Keyboard, Video, and Mouse) is running over the network.
You don't even have to use SQL server if you don't want to. An Access database for the data store will work fine.
Jim.
Note however that your app might need a few adjustments. On a RDS server, everyone has the same "C" drive (so you need to be careful with things like temp files), and will be sharing the same printers possibly.
The last thing is that everyone will still need an individual copy of the front end, so you'll need to create a mapped drive letter based on their login name.
Other than that, it is pretty much plug and play, and it works well even in low bandwidth situations.
With an RDS server, everything is running on the server and only KVM (Keyboard, Video, and Mouse) is running over the network.
You don't even have to use SQL server if you don't want to. An Access database for the data store will work fine.
Jim.
> I need to create an Access or SQL Server database that will be populated by a user in another country.
Slightly unrelated pro tip: Make sure when working with dates you have the format down correctly as countries can differ as far as their standard date format being MM-DD-YYYY or DD-MM-YYYY. Also text collation may be in play for different languages and accents.
Good luck.
Slightly unrelated pro tip: Make sure when working with dates you have the format down correctly as countries can differ as far as their standard date format being MM-DD-YYYY or DD-MM-YYYY. Also text collation may be in play for different languages and accents.
Good luck.
ASKER
Thank you for all 3 helpful responses. I'm not a network admininstrator so just want to clarify that the CALs you are referring to are Windows Server CALS? And to clarify the Access installation, BE in a shared folder and FE on mapped drive based on user login. Not clear on Access installation. Full installation for BE on the server and run-time for each user FE? I'm just not clear on what all gets installed on the server because everyone will be connecting from their own personal device. I've used RDP extensively to authenticate through a VPN and then log on to a user desktop on a network which has a full version of Access and a FE program file installed but this is a slightly different situation. No existing network.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I have done this often for multiple use cases it is best if you have SQL if not it still works but less stable
Step 1: create your database with tables in SQL / Access
step 2: create another access database and link it to the database created in step 1 (use the external database menu)
step 3: create all your forms in access
step 4: put the .accdb file where users can get to it (share point or a shared drive)
they can now all use it at the same time and see the same data.
Step 1: create your database with tables in SQL / Access
step 2: create another access database and link it to the database created in step 1 (use the external database menu)
step 3: create all your forms in access
step 4: put the .accdb file where users can get to it (share point or a shared drive)
they can now all use it at the same time and see the same data.
IF you use SQL for BE there is no "need" for all the above....you just switch to linked tables and you bypass all the expenses.
The problem is that SQL has different behavior in many areas and you need to adjust accordingly
Also linked tables while convenient they will not deliver the best performance .. as the recordcount increases...for that you need to work with pass through.
The problem is that SQL has different behavior in many areas and you need to adjust accordingly
Also linked tables while convenient they will not deliver the best performance .. as the recordcount increases...for that you need to work with pass through.
SQL as the BE offers many advantages
1. Your data is secure (access db corruption is a nightmare)
2. better performance (esp on multiuser)
3. you get to use SSRS / Power BI / SSAS for reporting
4. stored procedures
5. easier to change to another FE if the usage of the app grows
6. better security (authentication/ authorisation)
the major downside is the requirement for SQL cals
1. Your data is secure (access db corruption is a nightmare)
2. better performance (esp on multiuser)
3. you get to use SSRS / Power BI / SSAS for reporting
4. stored procedures
5. easier to change to another FE if the usage of the app grows
6. better security (authentication/ authorisation)
the major downside is the requirement for SQL cals
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If they utilize the RemoteApp functionality it will be almost transparent to the end users...pretty much as they had it on their desktop.