Naming Conventions for Microsoft Access

Error Saving Reaction

Naming Conventions for Microsoft Access

Software development in Microsoft Access is relatively simple and almost anyone can do it but if you want to be taken seriously by professional database developers then there are some mandatory rules that you must follow whilst developing your database. One of the first aspects professional developers will look at when reviewing your work will be the naming standards you are using for your Microsoft Access Objects. Microsoft Access allows you to use, what is considered in the professional database development world, poor naming standards. For example in your field names, you can have spaces however in the professional world that is a major no no.

One of the reasons we don't use spaces in Microsoft Access is that when you start creating complex queries and functions that refer to fields, if you have spaces in the field names it is possible to put two spaces into the field name but it only appears that there one space. What this means is that your query won't work and can sometimes take many days to find the mistake. The key issue is that you must never use spaces in your field names, database names or any of the seven different object types. There are in fact two ways that you should be naming your fields, database names and so on.

Naming Fields

Let us say for instance you wanted to create a field to store the Postcodes for the suburbs your customers live in. We could write this field in two ways, the first thing you must do for fields is to add the prefix fld at the start of the field. Then add the field name as required -

fldPostcode or fldPost_code

Either technique is quite acceptable. If you were developing a field for Post Codes, that is codes on posts, then the recommended way for writing the field name would be -

fldPostCode or fldPost_Code

You will notice that in the second example the second word is in capitals rather than in lower case. The use of capital letters signifies that each word represents a separate aspect of the field, so in this case the code in capitals means you are referring to codes on posts. The same naming principles apply to database names, and each of the seven Microsoft Access object types.

Naming Tables

When naming tables, there are three prefixes that you can use. The first prefix tbl is used for the core tables you will be storing your good data in. The tmp prefix is used for tables that will be storing temporary data. It is also recommended that you import your data into a temporary table before inserting the data into your good data tables. We do this for two core reasons; the first is that testing your data in a temporary table is much easier than doing it on the fly while you are trying to import your data.

The third table prefix I recommend being used is bck for backup tables. The key advantage of using these prefix's on your tables is that Microsoft Access will automatically group them by the prefix, which means you will keep all your good tables together, your temporary tables together and your backup tables together.

Naming the Seven Access Object Types

When you are working with the seven different object types that make up Microsoft Access, each of the objects has its own prefixes. A list of those prefix names you should be using for the Access Object Types are shown below -

db - Database

tbl - Tables

qry - Queries

frm - Forms

rpt - Reports

mcr - Macros

mdl - Modules

One of the key reasons we name our tables and queries with a prefix is that when you are working with these two object types in the query window, Microsoft Access simply lists all tables and queries together without differentiating them. By using the tbl and qry prefixes on these two objects, it always ensures that the tables are shown first and then the queries because t comes before q.

Other objects that need to be named include the bound and unbound controls found on forms and reports. When you create a form or report using the wizard or AutoForm or AutoReport each of the controls are named the same as the field names. Now whilst this is by default, it is not really an acceptable way of naming controls. One of the key reasons we don't want to do this is that sometimes when we are working with forms and reports we want to refer to the control rather than the field. By having the field names the same as the object names, you can often have a situation where the wrong control is referred to. The way we overcome this, is by naming our controls based on the control type. For example if our field was called fldPostcode and the control we are using for this field is a text box, then we would name the text box txtPostcode. Below is a list of prefixes for the forms and report objects.

frm - Forms

rpt - Reports

lbl - Lables

txt - text boxes

cmd - command buttons

lst - List Boxes

cmb - combo boxes

opt - option buttons

ole - ole objects

chd - Child Objects (Subforms or subreports)

For a full list of all prefix names that you should be using in Microsoft Access Development is shown below:

Microsoft Access Objects

db - Database

tbl - Tables

qry - Queries

frm - Forms

rpt - Reports

mcr - Macros

mdl - Modules

Table Objects -

tbl - Core Data Store

tmp - For temporary Tables

bck - Tables that you have backed up

Forms & Reports Objects -

frm - Forms

rpt - Reports

lbl - Lables

txt - text boxes

cmd - command buttons

lst - List Boxes

cmb - combo boxes

opt - option buttons

ole - ole objects

chd - Child Objects (Subforms or subreports)

Report Names -

rpt - For General Reports

cht - For Chart Reports

lbl - For Label Reports

Variables -

str - strings

ole - ole object types

int - integers

dec - decimal

dte - dates

Module Objects -

sub - Subroutines

fn - Private Functions

pfn - Public Functions

Using the naming standards shown above will assure that if you do require assistance from a professional database developer that they will take you far more seriously than before because you have shown a professional understanding on how to name your database object. This will certainly give you credibility in the eyes of professional database developers.

 

Views: 238
0
0
0
Author: Regular Articles

Chris Le Roy is a veteran of the software development having worked in the field for over 20 years and has been developing systems using Visual Basic 6, VB.Net, Microsoft Access and SQL Server, ASP, VB Script and Java Script. He currently runs a specialist Computer Training Centre in Townsville Australia, where people from all over the world, train to learn advanced techniques in programming in these applications. Many of his clients specifically focus on Microsoft Access development training. Chris has written a number of training programs, including a Microsoft Access Course. You can find more great Microsoft Access articles at his business articles repository.




Tell a friend
Average rating:
(0 votes)
Average rating from Reviews:
(0 votes)

Write a review

Reviews

Zacks Industry Outlook Highlights: Freeport-McMoRan, Newmont Mining, Golden Star Resources, Randgold Resources and Kinross Gold

Zacks Industry Outlook Highlights: Freeport-McMoRan, Newmont Mining, Golden Star Resources, Randgold Resources and Kinross Gold Read More

Gold Mining Stock Outlook - Sept. 2017

Gold Mining Stock Outlook - Sept. 2017 Read More

Why Freeport-McMoran (FCX) Stock Might be a Great Pick

Freeport-McMoran (FCX) is seeing solid earnings estimate revision activity, and is a great company from a Zacks Industry Rank perspective. Read More

Friend of slain Georgia Tech student: 'If Scout was more gender-conforming, would it have been different?'

Scout Schultz, a Georgia Tech student, was shot by campus police on Saturday night, and a protest two days later led to arrests. In the aftermath of those events, Yahoo Lifestyle talked to a friend... Read More

How to Make Healthy Leftover Turkey Tacos

These tacos get a healthy makeover by using turkey as the main lean protein. It all comes together with some beans, avocado, and a little cheese. Watch the video to learn how to make this easy... Read More

Little People, Big World's Audrey Roloff Just Gave Birth to Her First Child

It's a girl! Read More

Mel Brooks: ‘Blazing Saddles’ Would Never Be Made in Today’s ‘Stupidly Politically Correct’ Culture

“Blazing Saddles” may be a groundbreaking comedy, but director Mel Brooks doesn’t think Hollywood would make the iconic Western parody in this current “stupidly politically correct” climate. The... Read More

Box Office: 'Kingsman: The Golden Circle' Unseats 'It' as 'Lego Ninjago' Disappoints

Kingsman: The Golden Circle is the new ruler of the box office. The sequel from Fox is expected to earn $39 million this weekend from 4,003 locations. That’s slightly below where tracking had... Read More

Bradley Cooper-Lady Gaga’s ‘Star Is Born’ Moves to May 2018

Warner Bros.’ “A Star Is Born” remake starring Stefani Germanotta (Lady Gaga) and Bradley Cooper, who is also directing, is moving up its release to May 18, 2018. The pic was originally set to open... Read More

White House officials defend Trump, say athletes 'can do free speech on their own time'

Treasury Secretary Steve Mnuchin defended Trump’s call for NFL players who kneel during the national anthem to be suspended or fired. “It’s not about free speech,” Mnucin said Sunday. “They can do... Read More

In wake of U.N. speech, Kim calls Trump a ‘dotard.’ Trump fires back at ‘madman.’

President Trump and North Korean leader Kim Jong Un have unleashed personal attacks on one another after the U.S. commander in chief’s speech at the United Nations. “Kim Jong Un of North Korea,... Read More

As Russia probe turns to fake Facebook ads, Trump follows with a tweet

President Trump continued his efforts to belittle accusations of Russian meddling in the 2016 election on Friday, turning to the latest set of charges, involving targeted political ads placed on... Read More

Reluctant champion: How Nadia Murad has become the international face of Yazidi suffering – and resilience

Weeks earlier, Nadia Murad had been ripped from her village by Islamic State (ISIS) fighters who murdered her family and took her captive. Along with other young Yazidi women, she was transported... Read More

Readers write: The work of downsizing, evidence of climate change, hopeful coverage on famine

Regarding the Aug. 21 Monitor Daily story “Home prices, and a thought shift, give ‘small living’ a boost”: Interesting concept. My friend has done it, downsizing and living in a tiny apartment.... Read More

Time for the world to step up on Rohingya issue, Aung San Suu Kyi’s astounding hypocrisy, Irma’s destruction in Britain’s Caribbean islands, The US should stop saber rattling, On the Nadal-Federer comeback

“It is high time the world made an all-out effort to stop the ongoing pogrom against Myanmar’s minority Rohingya...,” writes Mohammad Amjad Hossain. “The pogrom resembles those crimes perpetrated... Read More