Microsoft Access Team Blog

Use ideas of "Implementation Patterns" from Kent Beck to simplify your life :)

I use Reddick as described in Access Developer's Handbook with some variations.  Many folks are not using prefixes for tables and queries, but I find it more time consuming to find the objects in the db window without tbl and qry prefixes.  Some folks use a different prefix for forms and subforms, but I prefer to keep my dependant objects together in the list.  frmCustomer and frmCustomerAddress_SF for example.  I always use CamelCapitalization and often use underscores.  Sometimes I use suffixes on action queries such as qryName_APP for append queries and qryName_MT for make table.  This tells me more about the operation when I see the qry executed in code.

I supose naming conventions are silly for Access 2007 because there is no db window so it's difficult to find any object, even by name.  Let alone try to find the objects by date modified or created date which is impossible.  Please bring back the database window.  Please Please Please.

Don't get me wrong, I do like 2007 (though 'classic menus' option and db window would have been nice.) and I'm excited for v14.

Keep up the good work.

Josh

I use RVBA (Reddick)...

Greetings,

Ronald.

I use reddick with variations too. I do not follow naming conventions for form and report textboxes, unless they are unbound, but do use cbo lst etc.

I also use a mildly formed set of rules for naming fields, something like the approach shown on Tony's webpage. tblInvoice fields all start with Inv so InvID InvNumber InvDate.

These practices add to complexity in one sense, but once you have the hang of it, it's golden...any expression is well illuminated. Good for one's own use, and also aids communication with other developers.

Note others that have posted re wanting the old db window back, I do too but here are also some good add ins that offer workable replacements.

I use MCS. Life is too short to type out str.

srsly

Similar to Josh I use I use Reddick with some variations.  I always use CamelCapitalisation when naming objects and variables - I type my code in lower case and if VBA doesn't autocorrect the case to Camel I know I have made a typo.

For bound objects on forms and reports I use the field name with a prefix to indicate the object type (even labels).

The beauty of having used a consistent naming convention over the years is that I can copy objects between databases and they work virtually unchanged, so I have standard setup forms, customer forms, etc.

As a consultant that is often called in to troubleshoot database problems, finding systems that lack any sort of naming system wastes the most amount of my time. As many programming newbies cut their teeth on Access getting them into good naming habits from the start would be brilliant.

I also think that Microsoft should 'impose' a standard rather than let users customise the convention that they use. Whilst this will initially be a pain to people like me that have evolved their own schema, the long term beneift of encouraging all developers to use the same system would outweigh this - and if you had 'proper' Search & Replace  functionality that searched ALL objects in the database it wouldn't be a huge job for us to update our systems to conform to the new standard.

I like Access 2007, but I echo Josh, please, please, please bring back the database window so we can sort by Date created, date modified, etc.

No Spaces.  Word caps.  Aim to make primary object appear first in sorts  ie Form the sub1 sub2.

Dont like reddick becuase if you change an object like a text to a combo, you have to change all sorts for stuff.  Better to call it what it does than what it is.  ie call a combo for Company choice. CompanyRequired than cboCompany.  Always call bound controls the name of the bound item (just easier)

To make my life easier I've written a module that looks at the forms control type and then adds the appropriate prefix (txt, btn (button), cmb (combo)  etc.

I think these comments are all very interesting. They're all sensible approaches. There seems to be a fair degree of commonality and thus maybe the Access Team will be able to derive a nomenclature for the templates that is not to alien to most Access devs.

I use Reddick as described in Access Developer's Handbook

I use Leszynski-Reddick like. I found that naming convention is quite important when you work in a team, so everybody have to write same style. Else you get a mess...

And when reading others' code naming convention also helps, especially for tables/queries/fields. For example I am trying to have field name unique, this helps to identify field's table in query sql.

For Tables I use the singular - Customer

For Views (aka queries) I use the plural - CustomersDelinquent

Thus avoiding tbl and qry for the actual objects but use MCS when in code. (except the Qry suffix).

This approach allows the Tables and associated Views to be listed together (which is the same reason that MCS use the Qry suffix).

This approach has been used by me over the last 20 years on databases with up to 200 tables.

BTW I still use field names without prefixes however a field is generally not called Date but DateInvoiceRaised, DateInvoicePaid etc will be the sort of names used for fields in an Invoice table.

I would recommend avoiding the use of Access ghetto-style naming for the database objects as the implemented database could well end up on an IBM DB2 Server (if it is a really lucky database (vbg)).

I use a slightly modified Microsoft standards for coding. I don't add a cls prefix to classes though. Methods use SentenceCase, variables use camelCase.

I tend to suffix databases objects with their type rather than prefix them, to make them easier to sort.

Additionaly, my query suffix is dependent on query type, e.g. _qsl, _qup, _qin, _qun, _qdl, etc

eg:

DepartmentStaff_qsl

ClearDownTempData_qdl

AllInvoices_qun

I don't bother suffixing tables with _tbl. However I prefix all tables with a three letter abbreviation and underscore, eg:

EMP_Employees

DPT_Departments

INV_Invoices

etc

Within the table, all fields then also have the prefix, eg:

EMP_FirstName

EMP_StartDate

My primary key (unless composite) is always tableprefix_id:

EMP_ID

DPT_ID

INV_ID

Foreign keys use the syntax master-table-prefix_foreign-table-prefix_ID,

eg:

EMP_DPT_ID   would be the employee's department

When I have multiple FKs to the same table, I add a decriptive:

EMP_Original_DPT_ID

EMP_Current_DPT_ID

(In the above example I'd actually move the data to an EDT_EmployeeDepartments table, but you get the idea)

Self joins follow the same rule:

EMP_Manager_EMP_ID

This syntax ensures that all field names within the database are unique, minimising the need for aliases, and removing ambiguity altogether.

I don't do a lot of VBA, so my naming convention is targeted at relating tables and queries. I do use a lot of stacked queries to process data. To keep all the steps of each process easily visible I include the process description, step number (in double digits), and step description in the object name. I know it makes them long, but it also makes them very clear and the expression builder UI means I don't have to retype everything.

So my object names look like:

Find_Duplicate_Users_05_View_Duplicates

I find I end up using similar step descriptions, so this keeps objects from being named the same things.

For field names, I try to be more precise rather than less to avoid bumping into reserved words like "Name" and others. For example, even if there are no other name-like fields in the entire database, I would still use:

Employee_Name

My extra-specific approach typically keeps me from running into duplicate naming and also keeps the purpose of the object/field clear to all users. For example, I would name a field:

Value_Minus_Reduction_Amount

just for clarity. Again, I use the builder to bring all these names in and avoid re-typing them a million times.

Also, I never reference a field name alone. I always include the full reference. This may seem like a lot of typing, but it works for me.

By the way, in just looking at some of the naming conventions out there, there does seem to be quite a bit of "extraneous verbosity!"

I don't think a name that is "as concise as possible" is a relevant goal any more. The few characters saved in object names is not worth the time it takes to learn all the convention rules in order to decipher what a database does.

My opinion: just make it obvious!

I use the commonly used prefixes suggested by all the naming methods quoted except that I don't prefix table names or column names.  Since queries always have prefixes, they are easy to distinguish from tables.  And in code, column names are always prefixed by their RecordSet designator (I don't like using With).  The reason I don't use prefixes for these two types is because I frequently use DB2 or SQL Server back ends and I need to keep the names as short and meaningful as possible since both RDBMS' are more restrictive than Jet.

For column names, I use ID as the suffix for autonumbers or user generated sequence numbers.  So primary keys and foreign keys almost always end in ID and are always the same except in self- or multiple referencing situations such as SupervisorID as a foreign key to an EmployeeID.  DT as the suffix for dates (I never store time without date), CD as the suffix for text codes which are usually from a legacy application.  I also have a list of commonly used words that I always abbreviate which saves typing and eliminates the need for me to think about what I might have named something when I am in the middle of writing code.

I also avoid the underscore but that is just a typing preference.  I'd rather not use the top row of the keyboard since it seems to slow me down.

In most cases I use Czech names for VBA variables, constansts, etc. so any naming convention is out of the question. ;-) In many cases I do use prefixes for specific data types, eg. int, lng, dbl, str.

I use the following prefixes for DB-objects:

tables: no prefix

queries: qry

forms: frm

reports: rpt

modules: mod

I don't use macros.

IMHO, no naming convention is necessary. But Access developers might be in trouble when they don't use prefixes, especially for global constants/variables. Access desperately needs a professional tool to inspect DB objects and source-code (plus a find/replace feature), see:

V-Tools: http://www.skrol29.com/us/vtools.php

MZ Tools: http://www.mztools.com/v3/mztools3.aspx

FMS Tools: http://www.fmsinc.com/Products/CodeTools/NewFeatures.asp

I use a Leszynski-Reddick like. But a A simplified version as in I dont use all the sub types for queries it is qry for all. I like to see all ralivent objects together. Which... is why I use singuler for objects e.g. frmCustomer, frmCustomerDetail, frmCustomerContact etc So when you look at the object browser you see all related forms together... I hate sfrm

But yes MS SHOULD use naming conventions in their templates, Northwind's object naming is terrible!

Thanks for finally doing this