WHY THE EASTER BUNNY PREFERS SQL SERVER TO ACCESS

The Easter Bunny stores his distribution data in SQL Server, not Access; read why!

Posted by Andy Brown on 30 March 2012 | 1 comment

Why the Easter Bunny uses SQL Server, not Access

I've blogged previously on why Santa doesn't use Microsoft Project. Continuing the seasonal theme, here's why the Easter Bunny doesn't use Access, preferring to store his data in SQL Server instead.

The Easter Bunny holding two of his many deliveries.

Speed of Access

There are a lot of children in the world, and a lot of Easter Eggs to deliver. The Easter Bunny tried using Access, but found that once he got beyond about 100,000 records queries started to run fairly slowly.

Robustness

The Easter Bunny got a bit tired of having to solve problems with Access databases. He found that repairing a database rarely solved the problem, and instead ended up having to:

Create a new database; then
Import all of the objects from the old database into the new one.

By contrast, SQL Server is ultra-reliable!

Multi-User Access

Whisper it, but the EB doesn't work alone: he has an army of helpers (primarily pixies and elves) to help out. When they all go online at once, Access just can't cope; Access is fine for a small number of simultaneous users, but not for large groups. Search for When to Migrate from Microsoft Access to Microsoft SQL Server for a Microsoft article on this.

What next in this series? Why the Abominable Snowman doesn't use PowerPoint?

Comments on this blog

This blog has one comment:

Comment added by smhn on 19 April 2012 at 15:18 GMT

hi... long time reader....

I have an Access db with a few million records in it .. alright, it's just a table with a few million records in it and I just did that because Excel couldn't handle it .. up to now, I was OK with the whole thing because its sole function was filtering data based on simple criteria ..

But now, I need to do functions like sumif, countif and average on it ...i.e. the number of time a code has been repeated in a column, or (considering that each code has a corresponding amount) the sum of every occurance of the code... how can I achive that in Access? ...

I'm a beginner in Access, but fairly good with Excel .. so, bear with me .. thx...

Reply from Andy Brown (blog author)

There are 3 ways to do this. Firstly, use functions like DSUM and DCOUNT - however, with a few million records these notoriously slow functions will be unusable. A second method might be to create grouping and totalling queries (see http://www.fontstuff.com/access/acctut04.htm for example for how to do this) - but with that many records, it will run slowly. For a few millions records, the only practical solution I would have thought is to upsize the database to SQL Server, the write SQL statements using GROUP BY clauses to interrogate the table - but this will mean learning a bit of SQL. Alternatively, you could use the view designer in SQL Server Management Studio, which is like the query designer in Access. Good luck!

WHY THE EASTER BUNNY PREFERS SQL SERVER TO ACCESS

Why the Easter Bunny uses SQL Server, not Access

Speed of Access

Robustness

Multi-User Access

BLOGS BY TOPIC

BY AUTHOR

BY YEAR