SOLVED

Build sql server backend and Microsoft Access as the frontend?

%3CLINGO-SUB%20id%3D%22lingo-sub-2206462%22%20slang%3D%22en-US%22%3EBuild%20sql%20server%20backend%20and%20Microsoft%20Access%20as%20the%20frontend%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2206462%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello%20team%2C%3C%2FP%3E%3CP%3EI%20need%20to%20build%20a%20database%20to%20build%20sql%20server%20backend%20and%20Microsoft%20Access%20as%20the%20frontend.%3C%2FP%3E%3CP%3EHow%20to%20get%20started%20on%20it%3F%3C%2FP%3E%3CP%3EI%20built%20a%20database%20in%20the%20past%20both%20backend%20and%20frontend%20were%20in%20Microsoft%20Access.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F584077%22%20target%3D%22_blank%22%3E%40CloudsInTheSky%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2206462%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2206652%22%20slang%3D%22en-US%22%3ERe%3A%20Build%20sql%20server%20backend%20and%20Microsoft%20Access%20as%20the%20frontend%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2206652%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F584077%22%20target%3D%22_blank%22%3E%40CloudsInTheSky%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhere%20will%20you%20deploy%20the%20SQL%20Server%20instance%20you'll%20use%3F%20Do%20you%20already%20have%20one%20available%3F%26nbsp%3B%3CBR%20%2F%3EYou%20can%20download%20the%20FREE%20%3CA%20href%3D%22https%3A%2F%2Fwww.microsoft.com%2Fen-us%2FDownload%2Fdetails.aspx%3Fid%3D101064%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3ESQL%20Server%20Express%3C%2FA%3E%20and%20install%20it%20on%20your%20computer%20or%20a%20network%20server%20available%20to%20you.%3C%2FP%3E%3CP%3EDownload%20and%20install%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Fssma%2Faccess%2Fsql-server-migration-assistant-for-access-accesstosql%3Fview%3Dsql-server-ver15%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3ESQL%20Server%20Migration%20Assistant%20for%20Access.%26nbsp%3B%3C%2FA%3E%3C%2FP%3E%3CP%3EIt%20will%20allow%20you%20to%20migrate%20your%20data%20from%20the%20Access%20accdb%20to%20the%20SQL%20Server%20where%20your%20database%20will%20reside.%3C%2FP%3E%3CP%3EBe%20sure%20to%20take%20plenty%20of%20time%20to%20configure%20the%20datatypes%20for%20your%20new%20SQL%20Server%20tables%20to%20be%20sure%20they%20are%20compatible%20and%20appropriate%20for%20YOUR%20specific%20database.%3C%2FP%3E%3CP%3EAlso%2C%20download%20and%20install%20the%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Fconnect%2Fodbc%2Fdownload-odbc-driver-for-sql-server%3Fview%3Dsql-server-ver15%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3Elatest%20ODBC%20driver%20for%20SQL%20Server%3C%2FA%3E.%20You'll%20need%20this%20to%20establish%20the%20links%20from%20Access%20to%20the%20new%20SQL%20Server%20database.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EFinally%2C%20invest%20plenty%20of%20time%20reviewing%20some%20basic%20information%20about%20designing%20your%20Access%20front%20end%20to%20work%20well%20with%20SQL%20Server.%20There%20is%20a%20great%20deal%20to%20say%20along%20those%20lines%2C%20so%20I'll%20just%20point%20to%20a%20%3CA%20href%3D%22https%3A%2F%2Fwww.jstreettech.com%2Ffiles%2FBest-of-Both-Worlds.zip%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Egood%20starting%20point.%3C%2FA%3E%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EGood%20luck%20with%20your%20project.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2206660%22%20slang%3D%22en-US%22%3ERe%3A%20Build%20sql%20server%20backend%20and%20Microsoft%20Access%20as%20the%20frontend%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2206660%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F584077%22%20target%3D%22_blank%22%3E%40CloudsInTheSky%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOne%20other%20consideration%20occurs%20to%20me%20re-reading%20your%20post.%20If%20you've%20only%20built%20one%20previous%20relational%20database%20application%2C%20then%20I%20STRONGLY%2C%20STRONGLY%20urge%20you%20to%20learn%20as%20much%20as%20you%20can%20about%20the%20process%20of%20designing%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Foffice%2Ftroubleshoot%2Faccess%2Fdatabase-normalization-description%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3Eproperly%20normalized%20relational%20database%20applications%3C%2FA%3E%20before%20you%20tackle%20this%20project.%20Table%20design%20is%20crucial.%20If%20done%20right%2C%20things%20go%20well%20from%20the%20beginning.%20If%20not%20done%20properly%2C%20you're%20database%20will%20be%20inflexible%2C%20unreliable%20and%20hard%20to%20maintain%2C%20even%20more%20so%20with%20a%20SQL%20Server%20back%20end.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2206687%22%20slang%3D%22en-US%22%3ERe%3A%20Build%20sql%20server%20backend%20and%20Microsoft%20Access%20as%20the%20frontend%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2206687%22%20slang%3D%22en-US%22%3EMS%20Access%20%26amp%3B%20SQL%20Server%20are%20a%20perfect%20combination%20because%20you%20can%20develop%20very%20super%20fast.%20But%3A%3CBR%20%2F%3E1.%20MS%20Access%20is%20very%20hard%20to%20learn.%3CBR%20%2F%3E2.%20SQL%20Server%20require%20data%20relationship%20knowledgement.%3CBR%20%2F%3EWe%20have%20a%20help%20group%2C%20registered%20at%20Microsoft%2C%20to%20help%20people%20to%20start%3A%20Microsoft%20Access%20PR%20DBA%20(%3CA%20href%3D%22http%3A%2F%2Fwww.MSAccessPR.com%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ewww.MSAccessPR.com%3C%2FA%3E).%3CBR%20%2F%3EContact%20us%20at%20Help%40MSAccessPR.com%20or%20Jose.Cardona%40MSAccess.net%20(English%20%26amp%3B%20Spanish).%3CBR%20%2F%3ECall%20787-236-0695%20(Spanish%20only%2C%20sorry).%3C%2FLINGO-BODY%3E
Occasional Contributor

 

Hello team,

I need to build a database to build sql server backend and Microsoft Access as the frontend.

How to get started on it?

I built a database in the past both backend and frontend were in Microsoft Access. 

@CloudsInTheSky

 

12 Replies
best response confirmed by CloudsInTheSky (Occasional Contributor)
Solution

@CloudsInTheSky 

Where will you deploy the SQL Server instance you'll use? Do you already have one available? 
You can download the FREE SQL Server Express and install it on your computer or a network server available to you.

Download and install SQL Server Migration Assistant for Access. 

It will allow you to migrate your data from the Access accdb to the SQL Server where your database will reside.

Be sure to take plenty of time to configure the datatypes for your new SQL Server tables to be sure they are compatible and appropriate for YOUR specific database.

Also, download and install the latest ODBC driver for SQL Server. You'll need this to establish the links from Access to the new SQL Server database.


Finally, invest plenty of time reviewing some basic information about designing your Access front end to work well with SQL Server. There is a great deal to say along those lines, so I'll just point to a good starting point.  

Good luck with your project.

 

@CloudsInTheSky 

 

One other consideration occurs to me re-reading your post. If you've only built one previous relational database application, then I STRONGLY, STRONGLY urge you to learn as much as you can about the process of designing properly normalized relational database applications before you tackle this project. Table design is crucial. If done right, things go well from the beginning. If not done properly, you're database will be inflexible, unreliable and hard to maintain, even more so with a SQL Server back end. 

 

 

MS Access & SQL Server are a perfect combination because you can develop very super fast. But:
1. MS Access is very hard to learn.
2. SQL Server require data relationship knowledgement.
We have a help group, registered at Microsoft, to help people to start: Microsoft Access PR DBA (www.MSAccessPR.com).
Contact us at Help@MSAccessPR.com or Jose.Cardona@MSAccess.net (English & Spanish).
Call 787-236-0695 (Spanish only, sorry).
Hello George,
Thanks for the response.
I have clear understanding of RDBM system. I followed all the rules and they were running successfully. Just want to know how to take SQL server as a backend.
I appreciate your response.
CloudsInTheSky
Hello Papo_Cardona_msn,
I already know Access, relational database management and t-sql. I created relation database management applications before. Now, I want to get started on having SQL Server as a backend.
Respectfully,
CloudsInTheSky
What is totoally free? help?
I know the basics and RDBM system very well. I write sql and t-sql.
Regards,
CloudsInTheSky

@CloudsInTheSky 
That's good. You mentioned one previous Access relational database application, which could be taken to suggest limited experience. One thing I've learned from many years of participating in on-line forums like this is not to make too many assumptions. I take it that you have extensive experience with other RDBM systems like SQL Server, Postgres, Oracle or MySQL, then?

 

The SQL Server Express version is a free download. You can install it on your local computer or on a network. It's not intended to be an enterprise database, but for smaller applications it is quite adequate. 

I know of a number of small organizations which have successfully implemented it for applications supporting more than 5 or 10 individuals. It is a great way to get started without investing hundreds of dollars in licenses for one of the other versions. Of course, if your organization already has a SQL Server available to you, you could work with the DBAs to obtain permissions for it.

 

 

Are these two tools SQL Server Migration Assistant for Access and latest ODBC driver for SQL Server complicated to install?
I have SQL Server and Microsoft Access on my computer.
Regards,
CloudsInTheSky

@CloudsInTheSky 

Not at all complicated. I've done it numerous times with no problems. The only thing that comes to mind is that you must match the bitness of the ODBC driver you select (32 or 64) to match the bitness of your MS Office applications. That means downloading that appropriate version.

I have experience with Microsoft Access, SQL Server and Relational Database Management System.
Regards,
CloudsInTheSky
It sounds like you are well on the way then.
Good luck with the project.