Our development standards

	Professional Database Developers
Aldex Software Ltd.

Introduction

This page is intended for in-house developers and is intended to give you a modest amount of guidance on the development of Access databases.

History
Microsoft Access appeared out of nowhere at the end of 1992 to rapidly become the database of choice for Windows based database development. Access is a RDBMS (Relational Database Management System) designed for the modern Windows environment. It does not carry the baggage and pre-Windows design history of earlier database systems such as FoxPro and Paradox. Access is now in its seventh major incarnation (versions 1.0, 2.0, 95, 97, 2000, XP, 2003) and apart from the confusing version names it just keeps on getting better.

Which Version To Use?
If you are unlucky enough to need to develop for Windows 3.x then you need Access 2.0 (as this is the last version to run under '16' bit Windows), but be aware that this version may have Year 2000 problems. However for most developers who will be writing for a minimum of Windows 98 then just choose the latest version. However if you're already using Access 97, or have standardised on Office 97 within your organisation, then Access 97 is usually okay provided that you do not need any of the later version's new features, such as Unicode support or improved Client/Server integration (but don't expect any support from Microsoft with any of the older versions of Access).

Some Basic Tips and Hints
We have seen some truly horrific database designs! This section contains a few suggestions and hints to try and prevent some of these and to make your database development work that much easier. Be warned however, Access is deceptively easy to get into. To use an analogy; Access is rather like a gently sloping sandy beach. You start to wade out and the water only just laps above your ankles, you keep wading out and the water very slowly rises up to mid-thigh. Then, suddenly, there is an abrupt drop-off and within a couple of paces you are in very deep water. For many simple database applications you can use the wizards and simply wade around in the shallows. If you need more complex systems then you will either have to learn how to swim or hire someone like us who has a powerful boat!

Design your database tables properly. Spend time and effort getting this right. If your underlying database structure is wrong then everything you do subsequently will be made much harder and may not even function at all. Make sure your foundations are solid, don't build a major database system on sand! If you don't understand about database design then hire someone who does! Table design issues to look out for include using the correct data types (eg don't use a numeric field for a telephone number and use the date type for dates or times rather than a string), use indexing correctly, make sure the design is 'normalized'.

Aside: Roughly speaking a normalized database is one where the data in each table is only related to the main ( = primary) key of the table and where any duplicate data definitions are removed. For example if you are taking orders then you would NOT have one table containing OrderNumber, OrderDate, ClientName, ClientAddress, OrderDesc1, OrderValue1, OrderDesc2, OrderValue2, ...etc but instead would have perhaps three tables; one containing OrderNumber and OrderDate, one for Client Details and one for the Order Details. This is a gross simplification but should give you the general idea. It is vitally important that your underlying database design is correct!

By all means use the wizards but don't rely on them to produce the full answer. The wizards are fine if you want a quick and simple database that happens to meet one of the supplied scenarios but don't try to take them too far. If you want something more advanced or that does not match up with one of the pre-defined templates then you must look at the underlying structures and make sure these will work with your proposed solution. The Query Design wizard however is great and you will find this immensely useful in making SQL more approachable (and if you are saying "what's SQL" then you need to do a lot more training before tackling any significant database work).

Learn and use VBA. With Access 97 and 2000 you can completely forget about macro's (with earlier versions of Access you still had to use macro's for a few functions). Macro's are OK (if you like that sort of thing) but they can be quite limiting; for example you cannot include any error trapping within a macro, so if a macro has a problem then your application just crashes without warning! If you want to do any serious development using Access you must use the proper programming language (all of our code is done using VBA - we hardly ever use macro's).

Split your database into two (at least). Keep all of your code, forms, reports, etc. in one 'code' database and all of your tables in another 'data' database which is then linked to the 'code' database. This makes modifications, updates and back-ups that much easier and allows you to work on a new version of your 'code' database without affecting existing users. It also makes it easier should you wish to convert a single user databases into a multi-user networked version.

Don't use spaces, quotes or other punctuation characters within your field names. Stick to A-Z, a-z, 0-9 and the underscore ( _ ). Access allows you to use most punctuation within field names but the use of single or double quotes can mess up SQL and cause your queries to fail - especially when you are constructing them in VBA based upon data in a variable (eg FieldName = '" & VarName & "'...). Spaces and other punctuation will work most of the time, but you must then remember to enclose these field names in square brackets. Additionally if you export data to another package, or if you later want to convert to client/server, you may find that the other software won't accept the field names. Best avoided - it saves heartache later on!

Make sure you put in adequate error trapping and that you test the final solution properly.

Don't be over enthusiastic with the color palette. A limited number of subtle colors are usually more effective than glaring purples and fluorescent greens.

Learn how to the use the debugging tools effectively. Access 97 and 2000 especially have got some cracking debugging aids. Make sure at the very least you know how to single step through code and display the contents of variables.

Be wary about using ocx's/Active-X add-in components with care unless you know what you are doing. These components (they keep on changing their name) can be highly useful but are often incompletely documented and may cause problems. For example they can work fine on your development machine but then refuse to work when you create a run-time and install it on another machine. Having said that they also offer an easy way to extend the power and flexibility of Access when you get them right! Just remember, we did warn you!

OK, that's about it for now - Is there anything else that you'd like to see added? What, some code samples! Well, ....... all right. But just a few,... we can't be giving all our secrets away!!!

Dates
Well, the Y2K thing was a bit of a non-event, certainly none of our customers reported any problems with any of our software (but then again we never expected them too). However dates do cause a lot of problems so perhaps a few words on the subject might be in order. All versions of Access and SQL Server internally store dates using four digit years. Hence valid dates stored in time/date fields in tables should not be affected when, for example, the century ticks over. The main problem lies in the initial entry of dates, either manually or from imported data. Access 2.0 (unless the Y2K patch has been applied to it) will interpret any two digit year as starting with an implicit 19. Access 95 varies depending upon which .dll's are on your system. Access 97 and 2000 have a date window for 2 digit years which should cope with most situations. SQL Server has a sliding date window that is user configurable.
We feel that it is best to subject all incoming dates to a custom date validation routine. This has the added advantage that you can modify a single function to change the rules for date validation easily as time progresses or your internal business rules alter.
Some of the built-in date functions, such as Now(), read the date from the Windows system clock. If this is correct, then the Now() function should work okay. However some older BIOS's will not tick over the century correctly, resulting in invalid dates being passed to the the Now() function. This is a system wide problem and, as such, we feel that it should not be corrected inside Access (since all sorts of other dates on your system will be wrong). Instead we include a test in the start-up routine of all of our Access databases that tests the Now() function and makes sure that it is later than 1 Jan 1998 (or whatever the date is when you program the routine). If it is less then this date then the BIOS is reporting an incorrect date, a message to that effect is shown and you are not allowed to progress in the database until the problem is resolved. This also solves problems caused by CMOS battery failure, so it's a good routine to use anyway.
A related matter is the display of date information. As a rule we advise that all date information is displayed using a custom format such as d mmm yyyy (move the fields around to suit your own country format) rather than using the Short or Medium Date formats which only show two digit years. The use of a four digit year display format gives added re-assurance to the user that the date information in the database is correct.

And Finally....
If you have any comments or suggestions, or if you need help with any development work, then please contact us.

Return to front page