Tag Archives: access

Access Work

What’s so wrong with Access??

Be prepared for a slightly ranty post, but with a twist (i.e this time not about infographics).

I’ve been developing applications using Microsoft Access for over ten years now, and I still find it an extremely easy to use, versatile and powerful application development platform.  I still regularly build systems with it today, although my time is now somewhat split between Access, Excel and ASP.

About a year ago I met with a colleague from another organisation to discuss a regular annual return we send to report on our organisations progress.  In these talks we discussed data quality and how we as an organisation store and analyse our data for export.  The bulk of the data I access for reporting either comes from information stored in an old and somewhat clunky Access DB, and the rest from a number of linked SQL server views containing information from our primary MIS.  It works well, and the queries/forms I’ve created provide all the information and reporting that we need.

This ‘data expert’ spent a great deal of time explaining how we shouldn’t use Access for systems development as it is ‘clunky’, ‘unsecure’ and ‘unreliable’ as a platform.  At the time this bugged me somewhat but I let it go.

Earlier this week at a conference in London I bumped into the expert again, and the first thing she asked was whether we were still using Access to develop some of our business tools.  I confirmed and I got a ‘look’.  The ‘look’ said ‘you need to move with the times, mate’.  We went our own separate ways and I started thinking about things..

Over the past couple of days I’ve come to the conclusion that she is wrong, and has probably only ever seen the ‘bad’ side of Access usage (and believe me, I’ve seen some really bad Access databases in my time).

I do agree that, at enterprise-level it’s certainly not the best platform as a standalone DBMS – you’d be much better served to have your data stored in one of the enterprise-level DBMS systems, such as SQL server.  That’s pretty much a given, especially from a multi-user perspective.

Where I disagree with her most is in terms of using Access to develop a reasonably sophisticated front-end, with data stored in a separate and scalable database – in fact this in particular is one of the areas in which I think Access still holds its own.  With the use of careful planning, a decent, reliable, secure and versatile application can easily be put together, but I think only as long as the application is developed properly and sensibly.

What do I mean by properly?  Here’s a brief list:-

  • Controls and forms should be unbound.  Creating, deleting and updating records should be carried out using VBA to ensure reliability and properly defined control.  Access wizards and bound controls don’t provide enough data validation or error-checking.  Any decisions on how to deal with data shouldn’t be left entirely to the user, and the system should have adequate business logic inbuilt to make those decisions and act accordingly.  With a decent grasp of VBA it’s actually very easy to implement such logic seamlessly and transparently to your application.
  • Data shouldn’t be stored locally in an MDB file if possible – any data used by the application should be stored in separate tables, linked for example using ODBC and whatever necessary authentication.
  • Access applications should be locally installed for each user, and not stored on shared network areas, where files are at risk of corruption or accidental deletion.
  • Security needs to be implemented properly.

I’m sure there are more examples of ‘proper’ development I could highlight, but I’m tired, and my main point is that I don’t feel that there’s anything wrong with using Access as long as the application is developed properly, securely and using VBA and robust programing practices rather than macros, bound controls or wizards.

Before anybody feels the need to respond negatively to my comments, I want to clarify that I’m aware that there are many better systems to develop in than Access.  I merely find it irritating when people dismiss it out of hand, especially if they aren’t aware of what the tool is capable of if used sensibly.

Another similar view on this can be found here (I even felt the need to use the same post image!)

Any thoughts or comments?  Keep them constructive, please.


Changing the theme of form controls in MS-Access

I converted a 2000 mdb file to 2002-2003 format today and noticed that the form controls still looked a bit square and old fashioned, and generally the overall theme did not match the look of my current windows version.

After a bit of digging around in the Options I found this in the Forms/Reports tab.

Windows theme options

Selecting ‘Use Windows Themed Controls on Forms’ and then clicking ‘Apply’ will mean that instead of your controls looking like this:-

Weird Controls

They’ll take on the current windows theme and look like this instead:-

Much better!


Changing MS-Access Field Behaviour

One of the more annoying features of MS-Access (I’m using 2003) is how, when you enter a field, either by clicking or tabbing into it, Access selects all the field contents as a default.  Then, when you try to press your arrow keys left or right to move your way through the text in that field, the focus shifts to the previous or next field instead.

It is pretty annoying, and like I did, I expect people just put up with it without realising that the behaviour can be changed..

Go to Tools>Options and select the ‘Keyboard’ tab:-


There are a few options you can select here to change keyboard behaviours in Access:-

Move after enter

The radio options here dictate what happens after you press the enter key in any field on your database form or table.  Personally I like to keep it the way it is, I think I’d find it extremely irritating if I moved to the next record when pressing enter.

Arrow Key Behaviour

This one I changed immediately the first time I found it.  This means that, from now on, whenever I press the arrow left/right keys, the cursor moves to the previous/next character in the field rather than moving through the fields.  Much better.

Behavior entering field

Another useful one to change for me.  If you click on any field containing data (do it now), you’ll see that the default behaviour is for the entire contents of the field to be selected.  I personally don’t like that, so alway change to ‘Go to end of field’, because usually the only reason I click on a field is to make a change or add additional information.

Cursor stops at first/last field

If you position your cursor in the last field of a record and press enter (assuming enter moves the focus to the next field, that is), the default is for access to move to the first field of the next record.  Put a tick in the box to ensure that this doesn’t happen, and that Access stays within the same record until you tell it otherwise.



2toria Access Downloadable content Visual Basic For Applications (VBA)

A very basic chat program in MS-Access

I‘ve just come across this little plaything I developed a year or so ago and thought I’d share it with you guys.  It’s a very basic chat program that you can use with your colleagues if you all have access to a shared network.  If you all open the MDB file you can chat with each other.

The code uses your network log-in name to define who you are and saves the ‘chat’ to a table within the database.  I’ve left everything open so you can look at the code etc, although there isn’t a great deal really – I’m actually surprised in hindsight just how simple it really is!

You will need to clear ‘tblConversation’ each time you’re done with the application, otherwise it will show the whole previous conversation.

I might someday develop this further, but for now as I said just thought I’d share with you.  If you can think of any uses for it, or if you want to have a go at amending it yourself please do and let me know how you get on.