Microsoft Access Tips

Access seems quite easy to use, do you have any tips on the best approach when developing new software?


We do a lot of work with writing and supporting Microsoft Access based applications, either ones we write from scratch or mainly taking on applications written by others.

In the latter case the typical situation is that the application is ‘put together’ by an enthusiastic person who can get out of their depth when the application grows in complexity.

There are some golden rules for writing Access applications which we have learnt well over the many years we have been using it.

1. Understand exactly what you want the application to do. Don’t get seduced by the speed at which you can put together an Access application and assume you will be able to adjust it in the future when you work out what you really need. If there is uncertainty consider writing a mock up or prototype to demo and discuss in order to get the clarity you need.

2. Get the database design right. By this I mean the tables and the relationships. This is the very heart of the project – do a good job here and you are set for success. Get this wrong and you are building a rod for your back, it will never work well ! You can learn the fundamentals of database design in an hour – ideally from as a qualified DBA (Database Administrator) or just Google ‘Normalization’ – which is the simple process us database designers use. This is an area where it can take years of experience to get really good – but learning just a little about database design will mean that you do a much better job. (Tip – there are some very technical descriptions of Normalisation – skip them and find a simple one with pictures – this is a simple process, and stop after step 3 as that is far as you need to go.)

3. Sketch out the whole application. Decide what menus and forms it will need, what reports are essential? What will happen on each form, how will you navigate between the forms? Together with your database design this forms the design of your application.

4. Use other people's experience. If your don’t have an Access guru who can mentor you then use the online Access forums, ask questions. This can save you hours and hours.

5. Allocate time. If you are writing an Access application for your company and this isn’t your main job consider if you have time to do this. Even the professionals underestimate just how long it takes to write an Access application – and a pro will typically work 5-10 time faster than a novice (mainly because of point 4). People can make the mistake of visualising the first few forms, reports and how easily they can put them together with no clear vision of how much bigger the application will be, often assuming they can just ‘add those bits later when I’ve worked them out’. This is one of the main reasons for step 3 – get clear on the scale of what you are attempting.

Microsoft Access is an excellent tool for rapidly developing small database applications – follow the advice and don’t be afraid to get professional help to get you on the right track.