{"id":665,"date":"2016-06-05T01:27:25","date_gmt":"2016-06-05T08:27:25","guid":{"rendered":"https:\/\/database.guide\/?p=665"},"modified":"2016-06-10T09:54:31","modified_gmt":"2016-06-10T16:54:31","slug":"microsoft-access-vs-sql-server","status":"publish","type":"post","link":"https:\/\/database.guide\/microsoft-access-vs-sql-server\/","title":{"rendered":"Microsoft Access vs SQL Server"},"content":{"rendered":"<p>This article looks at some of the differences\u00a0between <a href=\"https:\/\/database.guide\/what-is-microsoft-access\/\">Microsoft\u00a0Access<\/a> and <a href=\"https:\/\/database.guide\/what-is-sql-server\/\">SQL Server<\/a>.<\/p>\n<p>Both Access and SQL Server\u00a0are developed by Microsoft. Both are <a href=\"https:\/\/database.guide\/what-is-an-rdbms\/\">relational database management systems<\/a>. And both\u00a0have a large presence in organisations across the world.<\/p>\n<p>But between\u00a0Access and SQL Server, there are some significant differences.<\/p>\n<p><!--more--><\/p>\n<h2>A Technical Comparison<\/h2>\n<p>First of all, there&#8217;s the technical side. SQL Server has <a href=\"https:\/\/database.guide\/technical-comparison-microsoft-access-2016-vs-sql-server-2016\/\">much higher specifications<\/a> than Access.<\/p>\n<p>For example:<\/p>\n<ul>\n<li><strong>Database size:<\/strong>\u00a0Access\u00a0<a href=\"https:\/\/database.guide\/what-is-a-database\/\">databases<\/a> can have a maximum size of <strong>2 gigabytes<\/strong>\u00a0(minus the space needed for system objects).\u00a0SQL Server databases can have a maximum size\u00a0a maximum size of\u00a0<strong>524,272 terabytes<\/strong>\u00a0with a maximum data file size of 16 terabytes.<\/li>\n<li><strong>Simultaneous users:<\/strong>\u00a0An Access\u00a02016 database has a limit of <strong>255 concurrent\u00a0users<\/strong>, whereas\u00a0each instance of SQL Server 2016 allows a maximum of <strong>32,767 simultaneous user connections<\/strong>.<\/li>\n<li><strong>Number of objects:<\/strong> Access can have a maximum of\u00a0<strong>32,768 objects per database<\/strong>. SQL Server can have up to\u00a0<strong>2,147,483,647<\/strong>.<\/li>\n<\/ul>\n<p>For any medium to large business, this makes SQL Server the obvious\u00a0choice for enterprise level solutions and\u00a0other mission-critical applications. Medium to large organisations can have some pretty large requirements when it comes to storing data. They can also have a lot of people trying to access the database at the same time.\u00a0So at that level, SQL Server will win every time.<\/p>\n<h2>I Need a Database&#8230; QUICK!<\/h2>\n<p>While SQL Sever is a much more powerful system than Access, there are many cases where it makes more sense to use Access instead of SQL Server.<\/p>\n<p>There are times where a more modest\u00a0database might be all that is required. In these cases, Access will often fit the bill perfectly. SQL Server would be overkill.<\/p>\n<p>One thing\u00a0that makes Access attractive to individuals and small business is that a relatively non-technical person can whip up a database in no time using Access.<\/p>\n<p>Many business professionals and subject matter experts\u00a0know how to use Access but have no idea where to start with SQL Server.<\/p>\n<p>Even if they have no Access skills, the learning barrier is much smaller than with SQL Server. In many cases, Access is already installed on their computer (via the Microsoft Office suite of products) and so they can just fire it up and fumble their way through.<\/p>\n<p>So such\u00a0individuals\u00a0can create\u00a0an Access database themselves\u00a0without any outside help. They can do this to keep track of projects, or for inventory or bookkeeping purposes, etc. If they had to use\u00a0SQL Server, they&#8217;d have to\u00a0pay\u00a0someone else to build the application.<\/p>\n<h2>Features<\/h2>\n<p>Access includes\u00a0features that make it easy for non-technical users to perform tasks (such as its <a href=\"https:\/\/database.guide\/how-to-create-a-form-in-access\/\">forms<\/a>, its wizards, it&#8217;s <a href=\"https:\/\/database.guide\/how-to-create-a-macro-in-access\/\">macros<\/a>, etc).<\/p>\n<p>Again, this makes Access the ideal choice for those who don&#8217;t have the time, money, or inclination to use an enterprise level RDBMS.<\/p>\n<p>A non technical person can not only <a href=\"https:\/\/database.guide\/how-to-create-a-database-in-access\/\">create a database<\/a>, but they can also build an interface that allows them (or others) to enter data and run reports. And it can be done using wizards to guide them through the process. To build such an interface\u00a0with SQL Server would require another technology, not to mention the skills related\u00a0that technology.<\/p>\n<p>On the other hand, SQL Sever includes advanced features that aren&#8217;t available in Access (such as <a href=\"https:\/\/database.guide\/what-is-a-stored-procedure\/\">stored procedures<\/a>, more granular security features, etc). For those with the skill set, features such as these are\u00a0often\u00a0viewed as crucial elements in\u00a0developing any database solution.<\/p>\n<h2>It&#8217;s either Access&#8230; or back to Excel<\/h2>\n<p>Access is not\u00a0intended to be an enterprise level solution. Its purpose is to fill the gap between using an Excel spreadsheet and building an elaborate\u00a0SQL Server\u00a0driven application.<\/p>\n<p>Access is a step up from Excel as far as storing and retrieving structured data. Many times it&#8217;s a decision between;<\/p>\n<ul>\n<li>Keeping\u00a0data in the\u00a0Excel spreadsheets, and trying to reorganise them\u00a0as the data\u00a0grows\u00a0 or<\/li>\n<li>Whipping together an Access database and storing it in there.<\/li>\n<\/ul>\n<p>If Access wasn&#8217;t an option, it would be back to the spreadsheet.<\/p>\n<p>Also, in the corporate world and government departments, many administrators, managers, assistants, and clerks use Access without any help from IT. If they were to get IT involved every time they needed a database, they probably wouldn&#8217;t bother and just resort to Excel.<\/p>\n<h2>Scalability<\/h2>\n<p>SQL Server\u00a0has much better scalability than Access.<\/p>\n<p>Over time,\u00a0businesses can grow. Sometimes very quickly. All of a sudden their data storage requirements go through the roof. Their database is approaching 2 gigabytes in size&#8230; only problem&#8230;\u00a0It&#8217;s an Access database!<\/p>\n<p>They also have more users and they&#8217;re getting funny errors due to having too many people using the database at the same time.<\/p>\n<p>Now they have to rethink (and re-implement) their whole database setup&#8230; fast.<\/p>\n<p>If they&#8217;d\u00a0used SQL Server, there wouldn&#8217;t be an issue.<\/p>\n<p>Sometimes,\u00a0starting out with\u00a0an\u00a0Access based solution makes sense. Then if the database requirements grow over time, upgrade to a more sophisticated system like SQL Server.<\/p>\n<p>There are other times where a\u00a0system\u00a0like SQL Server should be used right from the start. Either the requirements, or the projected growth, dictate that a more robust solution needs to be in place right from the start. It can be quite time consuming and costly to switch database systems at a later date, so starting with the right solution is usually better.<\/p>\n<p>However, you don&#8217;t always know how the requirements are going to grow. In\u00a0many cases, there will never be a\u00a0need to upgrade from Access. Often a project might start off small, as a side project &#8211; perhaps even by a junior staff member\u00a0of a an\u00a0organisation.\u00a0In many\u00a0cases the project will either stay small or disappear altogether.<\/p>\n<p>Every now and then, such a project\u00a0might\u00a0grow to the point where it has\u00a0become a top priority\u00a0for\u00a0the department or even the whole organisation. In this case, a\u00a0decision might be made to\u00a0upgrade the database to an enterprise level solution.<\/p>\n<p>However, if an organisation already has the skills and equipment, it&#8217;s usually better to use SQL Server whenever possible, even if you don&#8217;t anticipate a lot of growth.<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>This article looks at some of the differences\u00a0between Microsoft\u00a0Access and SQL Server. Both Access and SQL Server\u00a0are developed by Microsoft. Both are relational database management systems. And both\u00a0have a large presence in organisations across the world. But between\u00a0Access and SQL Server, there are some significant differences.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,5],"tags":[8,12],"class_list":["post-665","post","type-post","status-publish","format-standard","hentry","category-microsoft-access","category-sql-server","tag-ms-access","tag-sql-server"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/665","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=665"}],"version-history":[{"count":4,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/665\/revisions"}],"predecessor-version":[{"id":811,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/665\/revisions\/811"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=665"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=665"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=665"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}