Tips’n’Tricks handout (A2K)

Topic

New Reply

I am creating a handout for my A-level students which contains some of the many pearls of wisdom gleaned from this august forum.

I would be grateful if anyone would care to glance over it and make any comments or suggest any additions.

The student’s are not at the level of VBA coding but need to deal with database design; forms; queries; reports and simple macros.

Reply | Quote

Replies

Steve:

I wish someone had given me this handout when I started with Access … very helpful.

IMHO since you have them disabling the “Auto Name Correct stuff” you might consider having them check the “Compact on Close” option. This has not caused any problems — at least in my efforts — and helps to control “bloat”.

Great job!

Reply | Quote

Steve,

Good work! You pointed out many of the problems that beginning Access developers typically run into with their first few projects.

Would you mind if I hang onto a copy of this document to use for future reference? Don’t worry, you’ll always get credit!

Thanks

Reply | Quote

Feel free.
I’ll post an update in the light of any suggestions.

Reply | Quote

What an excellent, well laid out, and useful document that is!

Your bit on “auto-numbers” and invoice numbers, cleared up a misunderstanding I have had since I started using Access.

I would suggest one addition to your list of items, this addition is an eccentricity of mine, so I would not be offended if you did not include it!

The correct convention for naming tables as you quite rightly point out is tblCompanyInfo, and I think one convention (this could be my eccentricity) is to name the fields in the table like this:

Table Name: tblCompanyInfo
Field Name: CompanyInfoCompanyName,
Field Name: CompanyInfoTel

Now this is the important bit, however you name your fields, whether you follow the convention or not, put a descriptive name of the field in the caption box.

Field Name: >>>>>>>>>>>>>>>>> Caption
CompanyInfoCompanyName >>>> Company Name:
CompanyInfoTel >>>>>>>>>>>>>> Telephone Number:

When you later create a form with the form wizard, based on this table the label adjacent to the field will contain the name in the caption box not the actual field name. This saves you ages of time in going through your form changing the contents of the labels. It takes a little longer in your table design, but saves time whenever you use the form wizard to create a form especially if it has numerous fields on it and keeps all your field descriptions the same.

Something else that has just occurred to me, now you’ve created your form with a wizard, for some reason the controls on a form are not named to any convention, however you can download a nice add-in from Helen Feddema

Reply | Quote

Rupert, (and the others, fyi… as I don’t want to mix this most useful thread with another discussion…)

when thinking about implementing this way of naming fields, I would suggest to use abbreviations instead of the full name, like this:
em_IDem = employee table key field
em_Name = employee name
em_IDcp = employee’s company (referring key)
cp_IDcp = company table key field
cp_Name = company name
This keeps field names into a reasonable length.
(Otherwise, Leszynski and others offer a list of possible ‘universal’ abbreviations of common names as comp(any),…)
Still, you keep the main advantage of your proposal: providing unique field names, which is handy for programming, designing queries, forms or reports.

For the ID’s you could consider writing out the ‘entitiy’ to the full, e.g.
cp_IDcompany, em_IDcompany
I just haven’t made up my mind yet if this is good for readability’s sake, or bad for (in)consistency’s sake…

(I caught this up from my boss at my first professional project… and it proved very useful. But more experienced programmers & developers of huge databases might know about some disadvantages…)

Hasse

Reply | Quote

For what it’s worth, the topic of naming fields in a table can get very rancorous because almost everyone has a system they swear by. Some people prefer some kind of table identifier in the field name (usually an abbreviation) , others insist on unique field names in the database (I lean in that direction myself, by preference, which means my key fields are usually the only ones with any kind of prefix), and still others use a prefix to indicate the kind of field or even use the prefix “fld”, which seems completely unnecessary to me.

I don’t like using datatype prefixes because I have yet to see a database so well designed that one or more fields doesn’t get changed from one datatype to another somewhere along the way. The problem then becomes one of either renaming every occurrence of the field, which can be done using a find and replace utility if absolutely necessary, or having a mismatch between the field name and the datatype. I prefer to avoid the problem by not naming fields things like strLastName.

As regards table identifiers in field names, I know several programmers who adhere to this religiously. The problem with this is related to the previous problem–what happens if you change the name of the table? Now suddenly your field names don’t agree with the table name any more. I also find that kind of field name hard to read and hard to type. If the field names become very long, they tend to make your SQL hard to read and debug. And since the tables keep track of what fields they contain, I’ve never been convinced that this is even remotely necessary.

I wouldn’t spend a lot of time on this issue, if I were you. The most important thing is to be consistent. Don’t use radically different field naming approaches in different tables and different databases because you’ll only wind up confusing yourself and making your apps harder to maintain.

Reply | Quote

Steve,

A most excellent set of guidelines! Thank you for sharing!

If you’re still accepting comments, I offer a couple. Under Disk Space, you might want to include something about having enough disk space for both an original and a copy, for Backing Up. Though today’s hard drives are usually large enough to handle this, one does not know what someone may face in the future.

Perhaps under Restore a Backup – If you copy a database from CD to hard drive, you may need to change the file’s Read Only property. The file will normally paste in Read Only mode. (Sure, it’s true for all file types copied from a CD to a hard drive, but might be worth noting. I believe there are ways to get around this, but none come to mind at the moment. Perhaps other Loungers can help on this.)

Again, thanks for sharing!

Tom

Reply | Quote

Your hand out is very good. I think I would add something about relational theory and designing a database schema on paper before heading off into table design. Also your students should know about normalization in table design to ensure good results.

Reply | Quote

Here is the updated version. Thanks to those who made suggestions.

You may use it how you wish but please leave the acknowledgement intact.

Reply | Quote

I have given a copy to my son.

Are you going to post the handouts on Normalisation and Dependency?

Many Thanks

Peter

Reply | Quote

Seeing as you asked so nicely…..

If ever I’m in Maidstone a couple of pints of Shepherd Neame’s finest wouldn’t go amiss….

Reply | Quote

Steve:

Bravo and bravissimo!!

That’s the best normalization outline I’ve encountered. You have the ability to take arcane concepts, simplify them, and make them understandable.

Thanks again.

Reply | Quote

Great job Steve! I have a poster on my wall that I got in the late 80’s (yes, I’m that old) dealing with normalization using puppy kennels as an example. I love this puppy poster and have never been able to find it again as I wanted to buy some for friends since it was so useful. Recently I found it as a PDF. Hope you all enjoy it and find it as useful as I have.

Reply | Quote

Don
Your link sends my Ad-Watch loopy about pop-ups!

I need to Ctrl-Alt-Del to get out.

What gives?

Reply | Quote

It’s a link to a PDF file. Are PDFs configured to open in a separate window on your PC? They open in a new tab on mine, and I have no pop-up problem.

Reply | Quote

Don’t know – I have no problem with it. It opens a PDF. Instead of following the link, try right clicking and “save target” to a folder then open the PDF manually.

Reply | Quote

Steve
great job!

One possible addition to front-end-back-end: converting the front-end into a mde reduces multi-user Access corruption on a network, especially in Access 97 (other versions: no experience). Be careful not to delete the original, as you’ll need it for further updates!!!! (more info about this topic can be found at another thread at the Lounge- I’m sorry for not having to provide more information on this)

Greetings,
Hasse

Reply | Quote

For any who are interested – I have added a couple of points so here is the updated version.

Reply | Quote

Hi Steve

Thanks for that I wil be most useful to me

Braddy

Reply | Quote

I’ve tweaked a couple of points in this handout and added a little on Access 2003 in response to a PM from a fellow Lounger.

It is free to use but please keep the copyright and acknowledgements intact.

Reply | Quote

Replacement post-Lounge crash (and an unashamed bump as well!)

Note by HansV: the original attachment higher up in this thread was lost when the Lounge server crashed in August, 2007. SteveH graciously reposted it. It is a very useful compendium of tips and tricks for people who want to design a database.

Reply | Quote

And the normalisation stuff as well.

Reply | Quote

The key, the whole key and nothing but the key. So help me Codd.

Nice summary and examples Steve.

Alan

Reply | Quote

In comparing your excellent write up with an older version that I had saved I notice that there is a new golden rule – #14 Look-up fields in tables – with the admonishment: “Don’t use them.” I had not heard this advice before, and with the fact that it was not in your older Golden Rules list does this indicate a new concern involving table look-up fields?

Would a reasonable approach be the following?
• Initially, include table look-up fields
• Create referential integrity between the table’s field and the looked-up table?
• Design the application’s forms that involve the table(s) with the look-up fields (having table-defined look-up fields helps ensure that the same combo box properties are used on all forms that include such fields)
• Reports should always include looked-up values as part of their RecordSources?
• Before application deployment remove the look-ups from the tables

Does this make sense, or am I missing other considerations?

Reply | Quote

There’s still the problem that Access may create superfluous indexes.

But apart from that, I admit to using the strategy you propose, because it considerably speeds up development.

Reply | Quote

When trying to teach relational databases to students, anything (like look-up fields) that hides/obfuscates the underlying workings, is not a good thing.

This page on the Access Web sums it up quite nicely.

Reply | Quote

I am getting a malicious script, when I try to download this file. Whats up with that?

Reply | Quote

I don’t think there is anything wrong with the file. Do you get a specific message, or just the general message that some files may damage your computer?

Anyway, Steve has posted updates to his wonderful handout. the most recent is in post 195604. My virus scanner has no objections to it.

Reply | Quote