For those who have a fair bit of experience, what's your top 5 best practices for developing databases in MS Access?

Warmest Regards,

Zahid Saddique

Hi Zahid,
Thanks for the post, sorry been off for a while. I think that my best practices are;
1. Ensure Database Normalisation; at least to Third Normal Form.
2. Apply Referential Integrity; to ensure that records cannot be created or deleted until associated records have been created or deleted. Although not necessarily required for RI it is usually regarded as best practice not to physically delete records but instead flag them as inactive, this has the added advantage that it protects the historic data and preserves it for future analysis.
3. Security; this is an area which has many connotations, you want to allow easy access for your authorised employees but prevent others from viewing, or even worse altering or deleting your data. Additionally you want to reduce the possibility of authorised users making a mistake and accidentally changing data they did not mean to. I recommend using the databases own built in security using the wizard to ensure authentication and authorisation, using roles and groups and applying permissions accurately. Use views (Forms rather than tables to access data)
4. Use a naming convention; to establish a standard for the names given to the objects in your database. I use the Lysnsky naming convention (tbl, frm, rpt, qry, mcr etc).
5. Use Data validation where possible, remember the acronym; GIGO, Garbage In - Garbage Out. A Database is only as good as the information it holds so it is important to trap as many mistakes as you can during data entry.
I hope that lot helps, best regards Pete