Moving from MS Access to MS SQL Server
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:
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).
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).
You need planning ...plain and simple...but because you mention it the most important thing is proper database design...
Given the fact you have already Access....the path would be :
Upload Access tables/queries to SQL
Replace whatever function you can with SQL function
Establish good functionality with Access as the front end...
Begin Web implementation...
Given the fact you have already Access....the path would be :
Upload Access tables/queries to SQL
Replace whatever function you can with SQL function
Establish good functionality with Access as the front end...
Begin Web implementation...
ASKER
Thank you both for coming back to me.
Ste5an - Correct + We control the individual RDP servers. If we get a new client, we spin up a VM server (from one of our templates), If a client gets a new project, or wants to modify an existing one, there are two options. Either, we login and do what needs to be done or they can do everything they need through our Front-end. So for example, if they need to build a new project, the front-end will: create the folder, add a blank .accdb b.e, setup the fields and everything else - and they're good to go.
John - That's pretty much how I see it.
In short - I know Access so well, it's very easy for me to setup new clients, create the back-end and modify everything via VBA to fit the client/project. I guess what I'm asking for is - can I do all of that with SQL, which I think I know the answer to already. The big one is - temporary tables. My current app has a blank Access database, that is copied from the server to the front-end folder (when needed), we then create the tables and link to that blank database and do what needs to be done. I think I remember SQL having the ability to have temporary tables that are removed when the session closes, but it's been a while.
Ste5an - Correct + We control the individual RDP servers. If we get a new client, we spin up a VM server (from one of our templates), If a client gets a new project, or wants to modify an existing one, there are two options. Either, we login and do what needs to be done or they can do everything they need through our Front-end. So for example, if they need to build a new project, the front-end will: create the folder, add a blank .accdb b.e, setup the fields and everything else - and they're good to go.
John - That's pretty much how I see it.
In short - I know Access so well, it's very easy for me to setup new clients, create the back-end and modify everything via VBA to fit the client/project. I guess what I'm asking for is - can I do all of that with SQL, which I think I know the answer to already. The big one is - temporary tables. My current app has a blank Access database, that is copied from the server to the front-end folder (when needed), we then create the tables and link to that blank database and do what needs to be done. I think I remember SQL having the ability to have temporary tables that are removed when the session closes, but it's been a while.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
Superb - thank you everyone. That gives me lots of food for thought.
Massively appreciated - thanks again.
Massively appreciated - thanks again.
and keeping the multiple front-end RDP servers. But optionally using a single web-front end later.
Is this correct?
The current RDP systems are not connected. How do you deploy currently updates of your application, how is administration over multiple servers done right now?