Good practices when working with Access

Good practices when working with Access

This is just a list of good practices to get into when working with Access. Having been bitten by some of these before, I know how much of a pain they can be!

Don't use reserved words as names in your database. For example, it is quite tempting to call a Date field 'Date'. This may cause Access to through up an error. You can see what words are reserved by using help on "Reserved Words". This brings up a SQL-centric list of reserved words. A more Access-centric list may be found at Q109312 on the MS Knowledge Base. I normally use a concatenation of two or more words as a field name, e.g. 'InvoiceDate'. As well as not having problems with a reserved word, it also tells me a bit about what the date relates to.
Don't have spaces in your field names. If you are using descriptive field names, then 'Invoice Date' will cause problems later on. It is better to just join the two together, as 'InvoiceDate'. As well as not using spaces, you shouldn't use special characters (./*:!#&-) in the names of objects.
Use a naming convention to name objects, such as controls on forms and other objects. It doesn't have to be one of the main ones. Just use it, and use it consistently. As well as making your code more readable (either to yourself at a later date, or to somebody else), it can also stop Access getting confused by circular references (i.e. having a control called 'Address' on a form, and setting the ControlSource to'=Address & PostCode').
Always ensure that you have a backup of your database before you start making changes to it. This backup should preferably be kept off-site, and you should make sure that it actually works. This is doubly important when you are creating an mde from your database, as this is a one way process. Lose the backup, and you'll have to start from scratch with your forms and reports.
When writing code that loops, for example a Do...Loop, write the 'Do' line, then the 'Loop' line, and then the incrementer line (i.e. rs.MoveNext) to stop the infinite loop. Also, when writing code that loops, it improves the readability of the code if you indent it.
Save your code often, especially before running it - if for any reason you have a problem with the code and your computer IPFs or similar, then if you haven't saved the code, then you will have lost any changes.
Always comment your code. Ideally, at the start of each procedure you should have a few comment lines, describing what the procedure does, what arguments it accepts and returns, when it was rewritten and any revisions. In addition, you should comment any lines of code within the procedure that perform any actions that might not be clear. Not only will this make life easier for anybody else who look sat the code, but it should also help you when you come to maintain your code in the future.
When writing code, write little, and test often. There is nothing more daunting than having written 400 lines of code, only for it to start failing when run. Instead, split your code up into stages, and test each stage as it is completed.

Top

Last modified at 06/06/2006 14:55:21