Field Naming Conventions

[Cosmos75]

Are there any rules to naming fields in tables?

I've seen naming conventions for tables (tblNAME), forms (frmName, fsubNAME) and reports (rptNAME).

Is there such a naming convention for fields names?
Can I just use "natural" field names? First Name instead of FName or FirstName?

Are there drawbacks to not using a tblNAME type convention?

 

[Pat Hartman]

Even though Access allows it, you should not use embedded spaces or any characters other than A-Z, a-z, and the underscore (_). If you get to a point of having to upsize your db, you will be very happy that you used names that will be acceptable to RDBMS' other than Access. VB does not support names with spaces or special characters either. It does allow you to use them however if you surround the names with square brackets - [Last Name]. I have trouble with that because I see them as parentheses and when you're looking at a nested function or IIf() that contains a lot of parentheses and names surrounded with square brackets, it can get very confusing.

Therefore FName and FirstName would be acceptable but First Name would not. CustomerNum, CustNum, and CustomerNumber would be acceptable but Cust# would not.

If your shop does not have naming standards for column names, try making some. For example, create a list of acceptable abbreviations and add to them as necessary. Most people don't like working with really long names because of the amount of typing required. But if you decide on abbreviations, then it is best to always use abbreviations. It's less memory work when you're writing code. So, don't mix names like CustomerId and CustName. Either always spell out customer or never spell it out.

 

[Cosmos75]

Pat,

Thanks!! I couldn't find anything about that in any of the books I have.

What do I do about an existing database where I have all the fields named with spaces?? I know you can compact a repair the database when you've changed form/query/report/field names but I understand it doesn't fix everything. Is there anything that can help out with going back into a database and revamping a naming convention?

 

[Pat Hartman]

Changing object names is a real pain. Even the name autocorrect in A2K won't fix everything. If you decide to embark on a massive cleanup, get a product like Speed Ferret that will do search-and-replace. There are also shareware products available. If you search the archives, you can probably find some references to them. Before you start this type of project make sure you have made a backup. Global changes can have unforseen side effects if you have happened to use a VBA keyword as the name of one of your columns.