I am just starting to look at porting one of our MS Access applications, across to SQL Server - and need some help.
Our current application has numerous clients, with each client running lots of individual projects - all of which requiring its own individual database (.accdb file). With the existing setup, this is easy as we have each client running on their own dedicated RDP server, and on that server, there is simply a folder for each .accdb - back end. The front-end Access application (running on the RDP), then switches to the desired back-end as needed by the user.
The plan is, to eventually create a front-end web interface, which links directly to the SQL server. However, I need to ensure the following:
- Each client has their own secure area, that cannot be viewed by other clients
- It's easy to setup and manage new clients and project databases
- We can create temporary tables (used for various functional/ reporting tasks), that do not get bloated
I am sure there will be 1000 other things that I will need to factor in, but I could do with a little reassurance that what I have listed above is relatively easy to achieve/manage. I'm not too concerned about the data/table structure itself as that is already fully SQL compliant and I have in the distant past worked on SQL Servers (but I know it will have changed a lot).