Before attempting to create your Microsoft Access database you might want to consider some of the points we have compiled below, these are only suggestions. There are no hard and fast rules, but bitter experience has taught us that following these rules can make application development much easier
- Do split your database into a front and backend to make maintenance and distribution easier. Better still start by designing your application on two halves as trying to run the ‘Splitter Wizard’ on a complicated database can be fraught with problems.
- Don’t use spaces in the names of your tables, reports, forms, or any other object within your database, it may cause confusion when programming in VBA, and will cause you problems if you ever decide to upsize to SQL Server.
- Do prefix the names of your reports, forms tables etc with a descriptive reference, e.g. use ‘tbl’ to prefix tables names, ‘qry’ for queries, ‘frm’ for forms, rpt for reports, ‘mcr’ for macros, and ‘mdl’ for code modules. This will allow you to easily understand what you’re referencing when de-bugging and reading code.
- Don’t allow your users direct access to data tables, this is what your forms are for!
- Do read as much as possible about Access, VBA, and database design. There are many very good books on the subject, which can be obtained at reasonable prices.
- Don’t expect your users to input the correct data every time, validate ALL data entries before committing them to your data tables.
- Do comment any code you write, not forgetting to describe the use for any variables you define.
- In table design view, make the effort to use the description field, it will make future maintenance a whole lot easier.
- Use indexing sparingly, whilst it can make searches etc. much quicker there is an overall performance deterioration issue to consider
- Use the correct data type for your fields. It can be tempting to just leave everything set to ‘Text’ as a catch all, but this will cause you problems later if you try to manipulate numbers entered into the text fields.
- Create a unique index for every table, even if it has to be an autonumber field that you never refer to, this will prevent problems if you ever upsize to SQL at a later date. (Your tables will become read only!)
- Clean your database before release – After development is finished, your database will almost certainly have a whole host of forms, tables, queries etc used for testing & debugging – get rid of them and compact your database to reclaim the used space.
- Know exactly what you want your finished application to achieve, and read as much as you can about Access, VBA and programming, and of course check this website regularly!