In this post we will be discussing Microsoft SQL Server and how it interacts with Microsoft Access.
Microsoft SQL Server is a Relational Database Management System developed by Microsoft and first released as SQL Server 2005 in (you guessed it) 2006. Only joking, it was 2005. Further versions were released in 2008 and 2012. SQL Server only performs one real function. It stores data. The data that is 
stored is accessible through other applications such as MS Access, Excel, PHP, etc. but these other applications are required in order to make use of SQL Server. SQL Server is a genuine back-end technology with a specialist duty that it performs with aplomb.
As with a lot of Microsoft products there is a free edition (express) along with more advanced editions for the serious developer (Enterprise). So, what can you do with a SQL Server (and more importantly what can you do that Access can’t)?
The traditional set up would be to have SQL Server on a separate server to the applications that run it.
In the above example, a local area network (LAN) could house the SQL Server on the main server (File Server in picture above) and the application that requires interaction with the SQL Server could go on the client computers. This is a standard set-up across multiple PCs that allows the data to be stored in one single location and therefore avoids data redundancy.
This set-up will also help to keep data more secure. Companies that have internal servers that store sensitive data need only secure the one location (the physical server). The applications that are able to communicate with the SQL Server will have their own permissions with regards to the data so the data is not less secure by their existence.
Not all companies have internal servers so does this mean that SQL is only for the medium sized company with an IT department and a significant budget? No! Thanks to the wonders of the internet companies offer SQL Server hosting that can be accessible through a web connection. This means that a client application (such as MS Access) could sit on a computer in, say, New York, storing data in a SQL Server application sitting on a server in China! Because of Round Trip Times, this is not necessarily the most optimal solution but it can be done.
MS Access is also a Relational Database Management System but is file based. This means that Access consists of files that sit on your computer which interact with other files that sit on your computer and querying and data retrieval are done locally. This set up is perfectly fine for most situations but you can take Access to a new level if you combine the reporting capabilities of Access with the data storage capabilities of SQL Server. The concept is the same as splitting an Access database but instead of having a group of tables sat on a server, you have a full Database Management System.
Although SQL Server has a primary goal which it achieves very well, there are other features that can really supe-up your applications. Stored Procedures, Functions, Views and Triggers are some features that really start to demonstrate the difference between SQL Server and simple tables.
Stored Procedures are similar to sub procedures in that they perform an action but they are particularly powerful in a Relational Database System as they enable you to keep all your business logic in one place. They work very well with Triggers.
Triggers are similar to Macros that respond to events. A Trigger will sit on a table and respond whenever a record is added, edited or deleted (according to your wishes). A classic example would be a trigger on a sales table that updates an inventory table when items are sold.
Functions are, again, a similar concept to functions in Access in that they return a value. At the table level they become particularly powerful as they can perform functions on a powerful server (speed) and send that information to the client server.
Views are similar to queries in MS Access. You may have two tables Orders and Customers with the CustomerID appearing in the Order table. What do you do if you want to view the orders and see the customer names (rather than the IDs)? Use a view. This would be a simple enough process in a front-end application but it is even better in SQL Server because the speed at which the views are generated is considerably faster than at the database level.
SQL Server with an Access front-end is also a developers dream when it comes to creating professional databases to be sold.
Transact-SQL
Transact-SQL or T-SQL is the language that SQL Server uses to retrieve, insert, update and delete data. T-SQL is based off of Structured Query Language (SQL) with some modifications that make it a more complete language for programmers.
*We will be using the terminology SQL for the rest of this section as the code we will be referring to is valid in both T-SQL and SQL.
Structured Query Language is a language that is written similar to the way we speak and can return data sets and perform actions on that data. Here is an example:
SELECT `ID`, `CustomerName` FROM `Customers`;
In this example we are asking to see all data associated with the fields “ID” and “CustomerName” from “Customers”. If you read the above SELECT statement out loud you will realise that it is very logically structured.
We can use the wildcard character * in place of column names if we want to select all the columns from the table:
SELECT * FROM `Customers`;
We are also able to filter the datasets we want. We simply ask SQL to show us all the results “where a certain condition” is met:
SELECT `ID`, `CustomerName` FROM `Customers` WHERE `ID`=3;
This statement is asking to see all data associated with the fields “ID” and “CustomerName” from “Customers” where the “ID”=3.
We can also perform data entry operations using the “INSERT” command:
INSERT INTO `Customers` (`CustomerName`) VALUES (“Charles Smith”);
This statement is asking to insert a new record into “Customers” and for the “CustomerName” of that record to be “Charles Smith”. Please note that the “ID” field of the “Customers” table can and often is an auto-generated, incrementing number so it doesn’t need to be entered here.
What about if we want to update the already entered data? We use the “UPDATE” command:
UPDATE `Customers` SET (`CustomerName`=’Steve’) WHERE `ID`= 5;
This statement is asking to update the “CustomerName” field to “Steve” in “Customers” where the “ID” field is equal to 5.
And what about deleting a record:
DELETE FROM `Customers` WHERE `ID`= 5;
This statement is asking to delete a record from “Customers” where the “ID” is equal to 5. Note that we do not supply a field as every field will get deleted.
SQL Server is an excellent tool for managing data and can be used as the back-end for a multitude of applications. It does take some time to learn but is well worth the effort and can represent the first step into a career as a Database Administrator!
