The Difference Between Microsoft Access and SQL Server

[Music] [Laughter] [Music] hey this is Stella Miller today I'm Sam IT I'm gonna tackle the question of what's the difference between Microsoft Access and sequel server this comes up a lot especially in the SMB because a lot of small businesses like to use access because it's well it's very accessible it's relatively low cost it's easy to get up and running it comes as part of the extended Microsoft Office suite almost everybody has it or at least it's very common it's easy to acquire it's well understood and so forth it is targeted pretty heavily at small businesses everyone knows it owners know it managers know it non-technical people can often delve into it pretty simply because it looks and feels and acts pretty much like other members of the Microsoft Office suite so if you're an Excel expert you use word all the time access it's pretty easy to start using now what exactly is access access is an application builder it is a tool that looks and acts a lot like a Microsoft Office tool that we use to build relatively simple applications that are driven by data it is common to believe that access is a database and even Microsoft sometimes will use the term Access database but there is no such thing as an Access database itself there are databases built by access that we might refer to that way casually but access itself is not a database engine and has no database format that is where people tend to start getting confused access itself is simply a very specific application building platform one that requires if you want to use an act an application built with access that you must also have access licensed and installed on your machine so access is not as flexible as say writing your own application in your own programming language like PHP or asp.net or something of that nature it requires that you have access installed so it's extremely limited in its utility but it is pretty easy to get up and running quickly everything that people actually associate with access which is the databases it's not actually part of access access may configure those things for you it may be what installs them as part of something else but it is not the database there are two main databases that are used for access the first the one that people commonly miss identify as being access itself is the jet DB database that's is named jet DB I'm not using database redundantly jet DB is an embedded database for those who know it it's similar to ones like SQL Lite or C polite what that means is it's not a relational database management system there's no jet DB server this is what makes it easy for people to get confused because there's no jet DB running on their system there's nothing to look at jet DB is a format and the files and the driver for communicating with them this is what MEC makes jet DB very cumbersome to use once you have more than a single user because each of the users who want to access it must have the correct driver and connect to the shared file and then the file has to just hope that the drivers honor its information so that they don't step on each other there's no there's no official arbitration and things like that it's relatively common to use embedded databases for small applications because they're very very easy to use and don't require anyone to install or manage a server because the application can build in the drivers or get them directly from the operating system and then talk directly to the files on disk so jet DB is very popular for really small things it's very very simple for you to deal with you never have to worry is my database running or something of that nature have I missed and figured it there's nothing to configure it's just a file format so and jet DB works really well right people give it a lot of grief because it doesn't scale up and you can't use with multiple users well mostly that's because you're using it inappropriately access if jet DB is used appropriately it can scale very large it can handle a large number of users the thing that it can't handle well is being shared which no database can this is not the fault of jet DB no embedded database will work well when you have multiple people connecting to it directly there should be an application arbitrating between end users and the application access does not do this it puts its multi user interface between the access layer the application made by access layer and the file itself which means that every person using jet DB is using a separate application they may be copies of the same one but they are copies running in different places they do not talk to each other they only look at this one shared file that is an access problem that is not a jet DB problem if you were to use jet DB behind for example a web application and all of the users access it through that web application say PHP or asp.net or JavaScript like nodejs something like that gen TV would be able to handle thousands or millions of users without any problem it would just not provide some of the features you may want now stepping back that's one of the database options for access the other is Microsoft sequel server so when you're working with access it's not access or sequel server it's access plus jet DB or access plus sequel server sequel server is never an alternative to access they are not competing products sequel server is the more robust more business oriented real core database for accesses front-end applications sequel server is an extremely high-end incredibly powerful and robust relational database management system unlike jet DB which is an embedded database sequel server runs as a service you can see it running on your system you need to configure it you need to monitor it you need to take care of it you have to feed it you have to update it and all those things but it is massively powerful it is one of the best relational databases on the market today and has been for a very long time and it provides its own security and arbitration layers and caching and all these things it does not require the driver in access to do those things the driver in access only provides a tiny tiny tiny bit of just enough to be able to talk the protocol a sequel server so that they're able to converse and pull data out of that so when using jet DB all of the logical the power of the database has to be kept in a driver that's used by by access but when working with sequel server all of that or almost all of that is moved out to sequel server itself allowing sequel server to do the heavy work and access to be just a very light front-end displaying the data from it so very important to understand sequel server and access are not competing products there are different kinds of things and sequel server is the expected business class store in which your data goes from access the use of jet DB with access is really intended only for learning or for single user applications they do allow you to use it for more than that but it is not effective it is not work well and they do provide they being Microsoft a free version of Microsoft sequel server which can be used up to a pretty good size so you're not stuck having to get a really expensive sequel server for a normal small business chances are you would fit into the free or at least a very low cost tier of sequel server but generally free it goes pretty big for free so it's it's it's it's interesting how these two things get over lapped in people's minds but they really are not competitors so if you ever hear someone say I want to move from access to sequel server you gotta be like whoa what are you talking about because sequel server does not provide an application front end and access doesn't provide a database they are two complementary but completely different kinds of products one can never replace the other they do not compete they cooperate they are part of the same ecosystem and Jeff DB that competes with sequel server in a weird kind of way that they're from the same vendor and Microsoft provides the one just for people who don't want to go through the effort of installing and setting up sequel server so if you want to just throw access on your desktop and you're not you don't want to be overseen by a IT you don't have licensing rights for whatever reason to put on a sequel server or you're unable to install it for whatever reason or you don't have privileges to open the ports you want whatever you can just use jet DB it doesn't require all those special rights so it does have good purposes there is reasons why Microsoft has chosen to make jet DB included with access but it is not how is intended to be used when you're using shared applications and Microsoft has gone extremely out of their way to provide all of the tools for very low cost or free for access so you can still use access with sequel server to power on the back end and at which point you can go to a really large scale if you want you can still have an organization with ten thousand users everyone has to access that at once it will work and handle that pretty elegantly that doesn't mean that using a client-server application like Access and sequel server is really the way to build enterprise applications that's a separate discussion but this is really just understanding how access and sequel server relate and interoperate with each other thanks for joining me remember to Like and subscribe to get questions put in below and as always you can sponsor us on patreon

Channel: samit

Views: 34,059

Keywords: access, sql server, database, information technology, scott alan miller, rdbms

Id: kNOgN9pb7R4

Length: 9min 57sec (597 seconds)

Published: Fri Dec 08 2017