I need advice on setting up an Access front end with an SQL backend

Hello All

We have an Access database that is split into a front end and back end.  Both are in Access.  I want to migrate the back end into SQL.  I was going to move the tables and leave the rest in the front end (I do know that it is better to have stored procedures in SQL but I am just not that sophisticated as a programmer yet).  

What do I need to do to make this work?  Do I have to purchase SQL and SQL management studio?  Do I have to purchase a new server?  They actually have an older server upstairs that I believe has server 2008 on it, can I use that?

Also after I move the backend data up onto SQL I would assume I have to reset all my queries because in SQL they get names like dbo.Customers instead of Customers.

Additionally, will this help with speed?  The entire database seems to be slowing down because we have about 15 people on it.  Each have their own front end.

I have never set up a server with SQL, in fact I have only just programmed in Access.

ANy advice is really appreciated!

Thanks!
alevin16Asked:
Who is Participating?
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.

lcohanDatabase AnalystCommented:
I believe the link below will offer all details about how to do it:
"SQL Server Migration Assistant for Access (AccessToSQL)"
https://msdn.microsoft.com/en-us/library/hh313039(v=sql.110).aspx

In particular the three listed below even though they talk about 2012 version you could use the existing SQL 2008 you have in house to migrate the database:
Preparing Access Databases for Migration
Describes how to prepare your Access databases for conversion to SQL Server /SQL Azure.
Migrating Access Databases to SQL Server
Provides an overview of the conversion process and detailed information about each step in the process.
Linking Access Applications to SQL Server
Describes how to use your existing Access applications with SQL Server.

At link below few more details in 15 easy steps to follow:
http://sqlmag.com/database-administration/15-steps-convert-access-data-sql-server

Although it seems complicated it is not really and the benefit as you said will be performance improvement against SQL assuming that server is in good shape.
You don't need to buy Management studio as it comes with the SQL Server so you should be able to install it on your client computer from that same install. If not available then just download it and you can find instructions at link below:
http://www.sqlshack.com/sql-server-management-studio-step-step-installation-guide/
0

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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can use the 2008 machine to host your database. The SSMA linked above is the best way to move your tables to SQL Server, in my opinion. Once you do that, you'll  need to relink the FE to those new tables, and your application should work as-is, with the exception of your queries (as you noted). What I generally do in those cases is rename the LINKS to remove the "dbo." in front of the names. So when I link, my table might be named "dbo.Customers", and I'd rename that link to be just "Customers", as it was before. If you do that, your queries should work as-is.

Regarding performance, moving tables to SQL Server is NOT a magic bullet. In many cases (if not most), your performance will not improve, and in fact may get worse. If you're a drag-and-drop type programmer using macros, wizards, etc, then you'll very likely find that your performance is worse.

Optimizing your database for use against SQL Server involves many things. For example, you should NOT open forms with a full recordset, and instead should filter for logical datasets - for example, instead of opening a Customers form bound to the Customers table, you should instead open that form bound to a Recordset like "SELECT * FROM Customers WHERE 1=0", and then allow the user to search for a Customer and reset the Recordset to show that Customer.

Also, you have to determine what processing can be moved to the server (in the form of Stored Procedures and such), and modify your FE to take advantage of those.

There are lots of writeups about this. I recommend starting with these:

https://www.fmsinc.com/MicrosoftAccess/SQLServerUpsizing/index.html
http://www.jstreettech.com/downloads.aspx - The Best of Both Worlds

The JStreet link also has a relink download that may be very helpful as well.
0
alevin16Author Commented:
Thank you both!  This is terrific and will give me a lot of reading.  

Andy
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I do know that it is better to have stored procedures in SQL but I am just not that sophisticated as a programmer yet).  
When you start going down that road this would be an excellent read
Migrating your Access Queries to SQL Server Transact-SQL
0
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
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.