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:

  • 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).
Andy BrownDeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
So you have multi-tenant system, currently separating tenants by using different servers. And you want to switch to one SQL Server backend
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?
John TsioumprisSoftware & Systems EngineerCommented:
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...
Andy BrownDeveloperAuthor Commented:
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.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

ste5anSenior DeveloperCommented:
So step 1 would be deciding the tenant architecture for your SQL Server. There are different possibilities:

1) One SQL Server instance per tenant.
2) One database per tenant, one SQL Server instance for all tenants.
3) One database for all tenants, using schema separation.
4) One database for all tenants, using (logical) partitioned tables and data access procedures and views based on login/schema.

The hard part is to pick the correct solution, cause it depends on a lot of external aspects.

The thing about temporary tables: You don't need that much in normal RDBMS like SQL Server. Cause you can do a lot with derived tables without performance lost. And we have table variables for many small tasks.
And from my experience, influenced by Oracle session temporary tables, using normal tables and implement your own temporary mechanism using the user login and some maintenance in the front and back-end allows to use these temporary tables as linked tables in Access.
John TsioumprisSoftware & Systems EngineerCommented:
You must change your philosophy a bit...when we migrate to SQL the concept of linked tables - in my opinion - is not what you should have...a linked table - although the ODBC driver does its best - means that you continue to use Access as you did but you just switched the tables location....something that personally i have seen and tested its a performance nightmare....maybe for a few thousand records it would be OK but you query millions....well that's another story....what is needed is to force the SQL to do all the nasty work and present us then minimum needed processed data...nothing else.... (this means that scrolling tables with million of rows......forget it....)
What you have is passthrough queries for selection and for actions...some (few) temp tables that will hold data that are needed constantly and it would a performance hit if you constantly re query the SQL server for a handful of entries and that's all..

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
I have several applications that use a combination of linked tables, stored procedures and views, even when they deal with millions of records. Its a matter of using the right tool for the right job. E.g. don't open a form that is bound to a million records, WITHOUT applying a filter. One approach I use is to set the design time recordsource to "Select ... from myTable where Record_ID=0" and then in the load event I might modify the recordsource to the ID I need.

This is really no different than when you are in SSMS, you usually do a Select top 1000 and not a full select.

I see no reason for a blanket statement that you should only use passthrough queries. The ODBC client as actually pretty smart unless you force it do something stupid, like "Select * from sql server table where LocalAccessFuntion()", because this means forcing ALL data to the client, which will be a performance issue for big databases.

Its a general rule, that as data volume grows, we need to be more conscious of our design decisions. If you only ever have a thousand records you can pretty much get away with the worst design ever, and still have reasonable performance.
Andy BrownDeveloperAuthor Commented:
Superb - thank you everyone.  That gives me lots of food for thought.

Massively appreciated - thanks again.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.