In this post we will be giving some thought to designing an Access application with SQL Server in mind.
Hopefully, throughout this series, we have demonstrated that SQL Server makes a very suitable companion for MS Access. It can help to add dynamism, security and functionality to your databases and if you use a web based hosting provider, you will be able to use your application anywhere you have an internet connection. There are, alas, costs (financially and functionally) and we will be discussing the best way to deal these potential outlays.
Local vs. Web Based
An immediate point that demands our attention is whether you use a web based host or you host locally. If you have SQL Server installed on your computer, you have a local installation. The connection will be extremely fast and won’t have to deal with the internet and Round Trip Times. You will, however, be required to make your own back-ups to protect your data from corruption and you will be responsible for the security of your database. In truth, most users will not be designing a database for themselves. The databases will either be commissions or for work (ever agreed to make a database for your boss, only wishing you hadn’t 6 months later?)
If you are creating a database for your workplace, it is possible that they will have a SQL Server installation on their own server that you can utilise. If not, you may be able to contact the I.T. department and ask them to provide the necessary installation. If you do get SQL Server installed on a work based server, you may not be required to make back-ups as it would generally be something the I.T. department take care of. If the server in question is on your Local Area Network, there will be no Round Trip Time to worry about. If the company server is hosted at its head office, you will need to use the internet to gain access to the database but unless the server is located in another country, the Round Trips Times will be insignificant.
You may, however, not have access to a SQL Server installation at all. The company you work for may not authorise it or you may be working on a commission and prefer not to have to log in to your client’s network to make changes to the database. In either of those cases, a web based application is required. There are several providers out there offering SQL Server for differing amounts. Let us take a look at some of the pros and cons of a couple.
SQL Server providers
SQL Server Mart
One of the cheapest providers around (+/- $7.99/month). Based in the United States so if you are in Europe, it may not be the right option. The servers are shared and there does tend to be a lot of traffic so load times will be slow but if the database is built with these considerations in mind (see below), SQL Server mart can be a very cost effective solution. Allows monthly payment.
Storm Internet
Not dissimilar to SQL Server Mart but based in the UK. The load times for the databases are about the same but if you are based in Europe, you don’t need to worry about the Round Trip Times. You will have to pay for the year up front but it isn’t too expensive (+/- £79.99/year).
Both of the above use shared servers, which means that there is already a significant amount of traffic on the server. If you want a dedicated server, you will pay more ($75+ /month) but the load times will be significantly faster. A couple of sites that offer this are RackSpace and Peer1.
The simple truth is that the more you pay, the better connection speed you will get. But sites like SQL Server Mart and Storm Internet still have a lot to offer. We here at AccessAllInOne.com have numerous SQL Server installations with shared hosting providers and they work fine. You just need to make sure you build with SQL Server in mind.
Shared Hosting
If you are going to use a shared hosting provider, there are some simple things you can do to ensure that your applications run as fast and smoothly as possible. Here are some simple tips and tricks:
Only use one sub-form per Data Entry Form
It is currently possible to create a data entry form that contains multiple related sub-forms. Sub-forms dramatically decrease the load time of a form in Access and if the database has to get its information from the internet, this will decrease further. One sub-form can be handled no problem but more than one will cause your forms to take 5 seconds+ to load.
Don’t use split forms
Along a very similar line, do not use split forms in your database if you intend on using SQL Server as a back-end. They are just too heavy and will take far too long to load.
Normalise
Most shared SQL Server providers have a limit of about 500mb for hosting with the option to purchase more if the database requires it. 500mb is more than enough space for most applications provided that the applications have been normalised properly. One of the benefits of a normalised database is that there will be limited data redundancy. In other words, we don’t want repeated data. If you have the same fields appearing in different tables, your database is not normalised properly and the data model will need some work. Lack of normalisation is the number 1 issue that affects users new to Access development and if you want to avoid repeating data and a bloated database, you need to get normalising!
Don’t store files and photos in your SQL Server
Although it is possible to store files and photos in Access as an attachment data type, SQL Server doesn’t possess that native functionality. It is possible to write a script that will aid the storing of these types of files but why would you want to? Use a dropbox and save the names of the items in the dropbox in the database. Then just open them using a hyperlink. Ok, so it is more complicated than it sounds but trust us, it is worth the effort.
Use Views Where Possible
Views act like multi-table queries and when created will be treated like tables by Access. Although there is some debate about whether it is faster to use SQL Server to provide the views or whether it is better to let Access create the queries, it is certainly more manageable having a few views on your SQL Server rather than a multitude of queries on your Access application.
Use Triggers Where Possible
Triggers respond to table events such as inserting or updating a record. When fired, as triggers perform their actions at the server level, the user won’t have any idea that an action is being performed by the SQL Server. This will result in an increase in perceived speed and keep your users happy.
Use Stored Procedures Where Possible
Just as triggers perform actions in the background, so to do Stored Procedures. Stored Procedures contain code that can be called from Access or even from triggers but please don’t use Stored Procedures to return data sets! There is nothing intrinsically wrong with doing this but there will always be a simpler way to get the data set you required and we want Stored Procedures to work in the background like Triggers.
OK, so we’re done! This series has been intended as an introduction to SQL Server and has covered some fundamental concepts that you need to know to create a SQL Server based database. SQL Server in itself is a huge topic and should be taken one step at a time but if you use this series as a jump-off point, you will be able to build the application that you desire.
