Access Tips for Newbies

#1 - Before you begin

The first and most crucial step in building an Access application is learning the basic principles of relational design - often referred to as data modeling, or normalization. Using these principles, you can construct a solid foundation of tables and relationships that will allow you to fully leverage the power of Access. (Skip this step, and your development process will become a nightmare of "fixes" and "work-arounds", as you struggle to build a workable structure on top of a flawed base.)

To learn the the principles of data modeling, I often recommend Database Design for Mere Mortals by Michael Hernandez. You can also find information through numerous links at The Access Junkie's Resources webpage, beginning with the Database Design 101 and Starting Out links.

back to top

#2 - When you begin

If you don't have a good basic Access reference book, I urge you to invest in one. Access is a complex program with a steep learning curve, especially if you have no background in database development. Learning how to use the software is the natural "next step" after learning the basics of data modeling, so you can translate your data model into actual database structures.

One good text is Microsoft Access <version> Bible by Prague and Irwin. It will guide you through creating all the objects needed for a working database, and give you a solid foundation on which to learn more advanced development techniques.

back to top

#3 - Creating a new database

When you create a new, blank database in Access version 2000 or later, the very first thing you should do is turn OFF the Name Autocorrect feature. This feature causes numerous problems that far outweigh its' benefits. For more details, take a look at Access MVP Allen Browne's website.
See illustrated instructions

back to top

#4 - Some basic definitions

Tables store data.: Tables contain fields and records (not cells, columns, or rows).
Tables and fields have properties.
Tables should not be used for data entry.

Forms do NOT store data.: Forms are used to enter/edit/review/delete data that is stored in tables.
Forms have controls.
A form may be bound to a table, a query, a SQL statement, or a recordset (see the RecordSource topic in Help) - or it may be unbound.
A control on a form may be bound to a field contained in the form's RecordSource (see the ControlSource topic in Help), or it may be unbound.
Forms and controls have properties and events.

Queries do NOT store data.: A query is a set of instructions (SQL) that tells the system what records and fields, from one or more tables, to display (Select queries) or manipulate (Action queries).

Reports do NOT store data.: Reports are used to display and print data that is stored in tables.
Reports have controls.
A report may be bound to a table, a query, a SQL statement, or a recordset (see the RecordSource topic in Help) - or it may be unbound.
A control on a report may be bound to a field contained in the report's RecordSource (see the ControlSource topic in Help), or it may be unbound..
Reports and controls have properties and events

back to top

#5 - Naming standards and conventions

Do NOT use spaces or special characters (such as "/", "-") in the name of anything that you name in Access - tables, fields, queries, forms, reports, controls, etc. I usually avoid numeric characters as well; if I do use one, I put it at the end of a name - not at the beginning.

A common newbie mistake is giving fields names such as Name, Date, Month, Year, and so on. These are Access Reserved words, which you should not use to name anything in your database. See The Access Junkie's Resources webpage for more information. (Hint: you can add another word, or a prefix, to a reserved word, and use that to name a field. For example, "FirstName" "LastName" "DateOfBirth".)

Using standard prefixes such as tbl for Table, qry for Query, etc, can be helpful down the line - when you're troubleshooting SQL statements, expressions, and code; it's easier to identify the objects you're working with. See the Access MVPs website and/or The Access Junkie's Resources webpage for more information on naming conventions, and keep one thing in mind: which naming convention you choose is less important than being consistent in how you name objects throughout your database.

back to top

#6 - Creating a table

When you create a new table, set the Subdatasheet Name to [None]. For detailed information, go to Access MVP Allen Browne's website and scroll down to the section titled Tables: SubdatasheetName.
See illustrated instructions

back to top

#7 - What tables are NOT

Access tables are NOT like Excel spreadsheets. When you look at a table in Datasheet view, it may look similar to a spreadsheet, but it is very different.

Access tables are NOT tools for data entry or review. Tables are used to store data; with perhaps very limited data entry done during development for testing purposes. But a user should not enter data directly into tables, or review data by opening a table. Forms are the proper tools for adding, editing, deleting, and reviewing data.

back to top

#8 - Lookup Alert!

I strongly recommend that you do NOT use Lookup fields in your tables. Lookup fields are unnecessary in tables, because the user should be entering and reviewing data in forms, and they cause problems in other database objects such as queries and forms. Many experienced developers never, ever use them. See the Access MVPs website for more information on the perils of Lookup fields.
(Note: lookup controls in forms are called combo boxes. They are very useful, and will not cause any problems in forms or elsewhere in your database.)

back to top

#9 - Choosing field data types

Lookup fields are covered in Tip #8. For other data types, and generally speaking:

Data Type	Data
Date/Time	Dates, with or without times.
Currency	Money values. The currency data type is preferable for this data, rather than using the Number data type with field size Double.
Number	Numbers on which you will perform math calculations, or numbers by which you will sort. Note: if the number is a "fixed-length" number - for instance, a ten-digit policy number - that will need to include leading zeros (0) where appropriate, then usually these values should be stored in a field with a Text data type. Because the values are fixed-length, they will sort correctly.
Text*	Numbers that will not be sorted or used in calculations, and fixed-length numbers, and most text values such as first name, last name, street address, city, state, brief notes or comments, etc.
Memo*	Longer text values such as extensive notes, comments, or descriptions, etc.

*Additionally, in all fields with Text, Memo, or Hyperlink data type, you should set the field's Allow Zero Length property to No. For more information, go to Access MVP Allen Browne's website and scroll down to the section titled Fields: Allow Zero Length.

#10 - Multi-posting in Access newsgroups

Please don't multi-post (that's starting multiple threads with the same question, in one or more newsgroups). It wastes server space, and wastes the time of people who may work on answering the question in one thread, after the same question has already been answered in another thread.

If you feel you must post to more than one newsgroup (seldom necessary), then please cross-post; that is, add both newsgroup addresses to a single message - so that everyone can see what's going on in both threads. If you post in one newsgroup, then decide another group would be a better choice, recommend you wait at least a day before abandoning the first thread and re-posting to another group.

#11 - Posting email addresses in Access newsgroups

If you post a question in an Access newsgroup or other public forum, think twice before including your real email address. These forums are frequently swept by automated data miners which collect email addresses for spamming activities. A better solution is to use a fake email address, or a free email address that you can afford to abandon if it gets flooded by spam.

If you deliberately post your email address in the text of a post, "disguise" it so that the data miners won't pick it up - and make sure you also explain how to "decode" it so it can be used by the intended reader. Example:

tREtacMcOessVE2AaLtyaLhoCAodPotITcAoLSm
(remove all the capital letters and convert the "at" and "dot" to symbols)

#12 - "Better-looking" forms

Awhile back, a newsgroup post-er asked about making the user interface (forms) look better. Here, in essence, is my response:

"Better looking" is in the eye of the beholder (or user, in this case). There's the POV (point of view) of you and I, who like to program the form to do cool tricks. And the POV of a manager, who may be impressed by cool tricks, and who sees the form for a few minutes when you demonstrate the database to him/her. And then there's the POV of the daily/frequent user, who has to stare at the form for sometimes very long periods of time, and do his/her work quickly and efficiently, with as little eye strain as possible. You and I, and the manager, may have a very different perception of "better-looking" than the daily/frequent user.

I've found, generally speaking, that hard bright colors, fancy type, shadowed or color-outlined controls, and a crowded "busy" window, can all contribute to eye-strain - and make it unpleasant and even difficult for the user to look at and use a form for extended periods. Though I love color and love to make forms "pretty" (or impressive!), I've learned to keep the eye-catching formatting confined mostly to introductory or "main menu" windows.

For data entry/display forms, in Single Form and Continuous Form view, I generally stick with the default color (which is usually gray or some other neutral color, depending on the Windows color scheme on the user's PC). While boring, it is easy on the eyes, and doesn't distract attention from the data. I use color sparingly, judiciously, and usually in a consistent manner in all the forms in the database. For instance: Red (198, not 255) on all Quit buttons; Green on all Save or Continue buttons; Dark Blue, bold, for all data entry controls' labels; Black, bold, for all "read" only data. This can have a subliminal effect in helping users to quickly grasp the context of what they're seeing, and subtly drawing their attention where you want it to go.

The above remarks are based on my own development experiences, including feedback from my databases' users, and also from my own data entry experiences - I've spent considerable time on "that side of the fence", as well. You'll get differing opinions from other developers, whether in the newsgroups or on various websites. All the ideas you read will be useful to you, because they will probably raise points that you hadn't considered. Developing a user interface is an art, and we all bring our own experiences and creativity into it; so consider all the ideas, opinions, and suggestions you read/see - and then use those you consider helpful to guide you in your own development process.

Note that the above comments address only the "look" of a form, not interactive behavior. Don't get so wrapped up on how a form looks, that you forget to attend to the small but important details that make any form easier to use. For example: setting the controls' Tab Order, so the cursor doesn't jump around the form, but flows through it logically; setting the TabStop property to No for a) command buttons that you don't want the user to accidentally activate with the Enter button, and b) textbox controls that are "read-only", so the user doesn't waste time tabbing into those controls and perhaps trying to enter data into them.

back to top

Tips to Get You Going Basic information that applies to all databases...

Tips to Get You Going
Basic information that applies to all databases...