Microsoft Access as a website database
10 Dec 2010, 11:20
Subscribe to Richies blog (RSS)
This blog details how you can successfully use MS Access as the data store for your website.
In this blog, I explore the issues you will encounter and explain how to overcome them. A fairly high level of knowledge on web-site and database architecture is assumed. It is also assumed that asp.net is used for the website with ado.net / oledb used to talk to the database, though the techniques described could eaily be fitted to other technologies.
Why?
Access was never intended to be a multi-user high performance database, so why would you want to use it as such? The database behind a modern website needs to be tough – it needs to cope with high volumes, multiple concurrent accesses, transactions, backups. It also needs to be efficient so web page loading times are kept to a minimum. From these requirements, Access is not an obvious choice. It’s great as a single user database for relatively small amounts of data. And contrary to popular belief, the Jet engine is quite a robust and efficient SQL engine, and works nicely with the asp.net technology offerings. But its not SQL Server or Oracle. Its not even My SQL. So why try and use it as a website database?
Well, it all comes down to money. The Jet engine is free – free to use and free to host. This means so long as you have a legal copy of Access to develop the database, there will be no hosting costs. Other databases will incur, as a minimum, additional hosting costs (check out you hosting provider – chances are to add an SQL Server or My SQL database will cost extra). There may also be licencing costs, though MySQL is obviously free, and SQL Server Express is free and fine for most small to medium websites. Another consideration may be time and skill set. Many programmers know Access but not SQL Server or My SQL, and if you need to knock out a website by next Tuesday, you haven't got time to learn!
There are a lot of obstacles to overcome in using MS Access as a website database, but it can be made to work and provide a cost effective solution for lower-traffic websites. Below I go through the issues you will need to overcome to achieve this.
Connection pooling
Despite certain documentation indicating otherwise, there doesn’t seem to be any connection pooling implemented in the ADO.Net JET engine. Or if it is implemented, it is very poor, because implementing your own dramatically improves performance. In fact, I would say it is essential to use connection pooling in any pages that are heavy on database access, if you want your site to be responsive. It is also prudent to implement some page or fragment caching and/or data object caching (using the dotNet built-in cache), but that is not the focus of this blog.
Access connection pooling code
Its pretty simple to implement connection pooling – just have a class encapsulate your connection object. This class then controls when the connection is opened and closed. Below is some code for a very basic connection pool:
using System;
using System.Data.OleDb;
static public class AccessConnectionPool
{
static private OleDbConnection _connection = null;
static private object _lock = new object();
///
/// Returns an open connection from a connection pool
///
static public OleDbConnection GetConnection()
{
lock (_lock)
{
if (_connection == null)
{
_connection = new OleDbConnection();
}
if (_connection.State != System.Data.ConnectionState.Open)
{
_connection.Open();
}
return _connection;
}
}
///
/// Closes all open connections
///
static public void CloseAll()
{
if (_connection.State == System.Data.ConnectionState.Open)
{
_connection.Close();
}
}
}
- The code (above) is a very basic implementation to illustrate the concept.
- Use the GetConnection() method whenever you need an OleDbConnection object - the connection returned is already open so no need to call Open on it.
- When you have finished all your data access, use the CloseAll() method to close the connection. You might want to call this, for example, when all page processing is finished (in your Page_Unload event). It is important that this is always called – see below for warnings on what will happen if this is not called!
- Note that the ‘pool’ is just one connection. You should only maintain one connection per database – remember access is not designed for multiple concurrent access. The Jet OleDb engine does allow you to open multiple connections, but there’s no performance advantage. Also, I can tell you from bitter experience that it is a very bad idea to have more than one open. However exact you are at cleaning up your connections, Access doesn’t like attempted concurrent access in this way, and will eventually start throwing random errors and stop working completely, bringing your website down in the process. You have been warned!
- What works well for sites I have developed is to have a connection pool (of one connection) that is used only for database read operations. For updates, I create a new connection (i.e. don’t use pooling). This works where update operations are rare relative to read operations (which is pretty much all websites). However, you must always close the open read connection before you open the connection to perform the update – if you don’t, access can get confused and return incorrect data after the update has been committed.
- You will notice I have serialised access to the connection via a lock. I strongly suggest you serialise all database access – e.g. have all database access contained in a single class and use a common lock object around all methods.
- WARNING: Don’t keep the connection object open over page requests!! If you do keep connections open over page requests, your website will slowly die, as every now and then a connection will get orphaned. You can’t prevent this – if the website has some problem between page requests – for example if an administrator uploads a new file that forces an application re-start, your connection will get orphaned. More advanced databases like SQL Server can cope with this (in SQL Server the connection pooling is external to the application), but Access can’t cope - eventually you will start getting random error message returned by the JET engine and everything will stop working. The only way to recover is to re-set the application pool your website is running in, or re-start IIS. Even keeping a connection open for the length of a request can cause issues if the request dies and the connection doesn’t get closed. Your error handling needs to ensure that connections are closed as soon as there is the slightest hint that something is going wrong.
Always close your connections!
I have already mentioned this, but it really is vital if implementing Access connection pooling. You will hit major issues and waste hours trying to figure out what is going on if you don’t get this right. Never leave any orphaned connections. With more tolerant databases, the odd orphaned connection isn’t a big deal – the connection pooling will clean it up. With Access JET OleDb, orphaned connections build up over time and will eventually bring your website down. Your error handling should close all database connections. And take care when doing redirects from code, as these may prevent your connection closing code being called.
SQL limitations
The JET SQL engine has a few limitations in its implementation of SQL – for example you can only have one JOIN per SQL statement (for multiple joins you need to do it the hard way in the WHERE clause). It also does some things in a non-standard way, for example boolean and date syntax is a little strange. None of this is a major concern – there are always subtle differences in SQL syntax and functionality between providers. Just compare Oracle and SQL server!
No stored procedures
This is a far bigger issue, and will be a show-stopper for many. All your SQL will be in code rather than in the database. Personally, I don’t buy the whole ‘stored procedures are the best thing ever and woe betide anyone who runs SQL directly from code’ argument. Yes, they can be more efficient (depending largely on your software and hardware architecture), but its often only marginal, especially if you use SQL parameters in Ado.Net. Yes, security is better (but lets face it, its not that difficult to strip potential hacks from user input – just remove SQL comments, harmful keywords and sort out single quotes). I find stored procedures to be inflexible and arcane compared to a dedicated programming language. When I need to dynamically generate SQL, its so much easier to do it in C# than in T-SQL. For example, I have a whole library of re-usable code in C# that can easily and reliably generate complex SQL statements based on user input and search criteria. Building something similar in T-SQL is horrible.
There are, of course, other reasons for using stored procedures, and they are often quite clearly the right way to go. Just venting at the stored procedure police. Lets move on..
Multi concurrent access
This has already been covered to a large extent in the section on connection pooling. Access was designed as a single user database. There are ways for multiple users to access the same database, but they are not ideal and not suited for website access. For a web-based application, the only reliable option is to stick to single user access – the user being your website. I.e. serialise all access to the database.
In low traffic, or even medium traffic websites this shouldn’t cause major performance issues so long as the site and database are designed to make data access quick and efficient.
This obviously becomes more complex if you’re developing a multi-server website. But lets be honest, you shouldn’t be seriously considering using Access as the database in a server-farm scenario.
File size creep
There is a minor but niggling issue with Access (though it may have been fixed in more recent versions, this is based on Access 2003). Access database files (the mdb files) grow every time the database is opened for an update operation. Access adds a little free-space in anticipation of updates, but this free-space is never re-claimed. Thus, over time, the size of the mdb files grows out of proportion with the amount of data contained within them.
This doesn’t seem to cause any performance issues, but it is obviously a bug or design fault. There is an option to automatically shrink the database every time it is opened, but unfortunately this only applies when it is opened in the Access product, not when it is opened via ADO.Net. So, the only options are:
- Live with it – its not really a major issue.
- Regularly open up the database in MS Access and shrink it back down. This is not straightforward when your database is on a remote web server!
Summary
Its do-able!. For low-to-medium traffic, single server websites, so long as you follow the guidelines above, you can use MS Access as your website database. This can save the considerable time and/or expense of using a 'grown up' database such as SQL Server or My SQL. Any comments, questions, feel free to post using the comment box below.
The important thing is not to stop questioning. Albert Einstein
| Comments | 5 |
|---|
| Ratings | 217 |
|---|
| Average rating |  100% |
|---|
| Posted | 10 Dec 2010, 11:20 |
|---|
View blog |
Richies blog |
|---|
Previous blog entry:
How to reset an identity column seed in SQL server
Next blog entry:
C# double question mark (??) syntax