Jumbabox

The other day I was handed an Access database, after waiting a couple of minutes for it to open I was dumbfounded with the monster it had grown into (check out the screen shots below). The funny thing was that this database somehow produced a report which a lot of ‘important’ people used and after the builder departed it was left up to me to get it back up and running.

So to prevent any future mishaps here are some tips that I use to keep my Access database organized…

Frankenstein’s Masterpiece

This is what you don’t want your Access database turning into! Hopefully the tips below can help you in developing and maintaining a much more organized Access database.

Naming

From the minute you start building it’s always good to follow same naming conventions for your Database Object.

Tables

With tables I tend to:

• Avoid abbreviations
• Avoid labeling Tables with unnecessary prefixes, such as Tbl_ or TBL_.
• Keep to the Pascal convention of naming i.e. ThisIsATable, not Thisisatable

I also like to categorize my Tables in accordance to which department they belong to, for example:

• SL_Customers - Sales
• HR_Staff - Human Resources
• MK_Campaigns - Marketing
• CS_IncomingCalls - Customer Service

Also, I have also seen tables which distinguish the difference between linked and static tables, why? Because there is an already built in option that performs this in Access.

Clicking on the Details button will provide you with the Name, Description, Modified, Created and Type of each database object.

I recommend using the same naming convention for all other database objects in Access (Queries, Forms, Reports etc.)

Fields

Keep your fields in the same Pascal convention of naming. For example:

• CustomerId
• Location
• MailingAddress
• ShippingAddress

The sum it up, you should label your database objects with meaningful names - this way anyone and everyone can understand your database!

Organize

There is a reason why Access has the option to Group your database objects - it comes in very handy when your database starts becoming very complex.

Note: By default there is a Favorites group already created, you can not delete this Group

To add a New Group, right click in the Groups area and click the New Group option.

To add a database object to a Group just click, drag and drop it into your Group, this will create a shortcut to that database object.

I tend to create groups for the different types of Tables I use. For example:

• If my database contains a lot of Linked Tables, I would create a group just for these
• If a lot of tables are created via a Create Table query in my database I would also place them in their own group

Note: Once you create a shortcut to your Database Object it is static. If you decided to rename your Database Object your shortcut will no longer be valid.

Descriptions

This is one thing a lot of people don’t know about, in Access you can provide Descriptions for Columns in Tables or for all your Database Objects (Tables, Queries, Macros etc.)

Descriptions for Columns

To add a Description for a Tables’ Columns, firstly open up your Table in Design View.

You should be able to see a Description column, as you can see I have already filled out mine.

Close the Design View and when you have finished, double click on your Table and click in any of the fields. The Description for the field you just added should be displayed in the Status Bar at the bottom of Access (seen below).

Descriptions for all Database Objects

Another function of Descriptions is that you can provide them for all Database Objects within Access. To do this right click on any object and click the Properties option.

A Properties dialog box will pop up, you can see that there is a text box available to enter a Description. Click OK when you are done.

Now change your view into the Detailed View you should be able to see your Description in the Description column for your Database Object.

Remember: Use these tips from the minute you start building your database, and keep using them!

If you have any other tips please comment!

Enjoy!