In a previous post, I spoke of splitting your database into a front end and a backend. In that example, I used the idea of separating one Access database into two Access databases, however that is not the only option you have.which will be explained in a lat
The truth is that Access can be a front end interface for a number of types of database backends. By utilizing cons called ODBC connections, which will be explained in a later post, you can link your Access database to just about any database on your computer, on your network, or accessible via the internet.
Recently, using this same technique to have a SQL Server database act as a backend. Like Access, it is a Microsoft product, but where Access was built for the consumer and small business needs, SQL Server was built to support the needs of commercial enterprises and large scale databases. It is also a much different experience to maintain a sql server database so it should only be undertaken if needed. Thus, the question becomes:
Do you need SQL Server?
The biggest advantage of SQL Server is size. An Access database is limited to 2 gb of disk space. Once it reaches that maximum, you will be unable to add any more data. This can be a great amount of data and depending on how much data you are storing and how often you are adding to it, this may be way more than enough. Plus, this is only the limit for a single Access database. Your front end interface can be linked to multiple back end data files. Indeed, even many corporate clients have chosen to go this route rather than make the switch to SQL Server. If you have reached the 2 gb limit, but it has taken 5 years or more of data entry to get to that point, you may want to just move your current data into an archive database and begin anew. If, however, you are finding that you can reach that amount in a month, it may be time to upgrade your backend to SQL Server.
SQL Server measures its maximum space differently. While Access is limited by the amount of data that can be stored in a single Access database, SQL Server is limited by the amount of data that the installation of SQL Server can hold, spread across all of the databases on the machine. This amount differs with each version of SQL Server and the most recent version, 2012, this amount is 524,272 terabytes, or the equivalent of 262 million Access databases.
The next biggest advantage of SQL Server is accessibility. SQL Server is designed to be web friendly. It can have its own port associated with it and has internal security settings to let users have access to the database. While similar measures can be set up within Access, they are significantly harder to put into place and not nearly as effective as those within SQL Server.
This accessibility also makes SQL Server ideal for tying a database into a web application. While not usually suitable for open source content management systems, such as WordPress, it still can be used with many other cms systems (a topic for the future). It can also be helpful for setting up e-commerce solutions and allowing you to view the data within your already set up Access interface.
If size or accessibility matter to you, then SQL Server may be the right way to go. Next week, I will go into the process of creating a SQL Server database and how to upgrade your current Access back end.