Data Management optimize

The 10 most important things to teach your Access users

Access is a powerful tool -- and for many users, it's a confusing one. This list of basic concepts will help them develop their database skills.

Access can be a bit of an enigma to casual users, who might understand the relationship between tables and queries but know nothing about its architecture and underlying technology. Although you might think that doesn't matter, users who hope to create a sound database should be aware of the concepts and functionality that developers and support personnel take for granted.

You don't need me to explain the following 10 items. And how much detail you share with your users is up to you. But I hope to persuade you that a bit of knowledge on these subjects will benefit your casual Access users, and therefore, you. For the purposes of this article, the term user refers to someone who's creating or maintaining an Access database (usually for their own use).

1: How to be a wizard-pusher

While I'm loath to mention this first, reality seems to demand it. When users are woefully unskilled in database technology and terminology, and there's no time to learn, teach them to rely on wizards. There's a wizard for just about everything. They should then run Table Analyzer and Analyze Performance to fine tune the results. The database won't be perfect, but wizards will usually produce a useable product. In addition, a wizard-generated database is easier to troubleshoot than one milled by a true novice.

2: What an RDBMS is

We're going to assume that your users know what a database is: a collection of related data. However, Access isn't just a database; Access is a relational database management system (RDBMS). An RDBMS is an integrated system of related data and the tools necessary to support the management of that data. In other words, Access allows users to store and manipulate data. It's a distinction that a well-informed user should know.

3: About Ace and Jet

Users who interact with developers should know what Ace and Jet are, just so they can keep up with the discussions. Developers usually refer to the set development tools as Access and its database engine as Ace (Microsoft Access Engine). Ace debuted with the first Ribbon version (2007). It's backward-compatible with its predecessor, Jet (Joint Engine Technology). Users don't need to use the term Ace or Jet themselves, but they should recognize those terms when others do.

The simplest explanation is that Access provides tools to access and interact with the data, but Ace (Jet) actually stores and manipulates the data. Knowing this distinction won't help your users create a better database. But as they gain experience, this knowledge will be helpful, whether they're working ahead of your training or interacting with developers. Once they start using code to access and manipulate data, this construct will make more sense to them. If they hope to be anything more than a wizard-pusher, it's important.

4: The file formats

Access 2007 introduced a new default file format, .accdb. This format supports several new features, such as multivalue fields and the Attachment data type. Most likely, its real purpose is to better integrate with SharePoint. The new format is backward-compatible with the earlier standard, .mdb. Your users might run into both formats and they should recognize them as such.

5: The difference between desktop and client/server

Most of your users will create a stand-alone database, storing all the data and interface tools on their local drive. Users who share their data need to know how to split the database, storing the user front end on the local systems and then linking to the backend stored elsewhere on your network. This arrangement isn't a true client/server, but it's in the ballpark. Using ADO (ActiveX Data Objects) to manipulate data on the server end, you get close to a true client/server. Most casual Access users will never create a client/server database, but a passing knowledge of the possibilities would be helpful. Some of them might eventually graduate to this level.

6: About Structured Query Language (SQL)

SQL is the standard relational database language; Access uses a dialect known as Ace or Jet SQL (see #3). (Frankly, I don't know how the industry is referring to the SQL dialect used by Ace, but Ace SQL seems appropriate.) Similarly, SQL Server uses Transact-SQL (T-SQL). Casual users don't need to be fluent in SQL to create an average query, but not knowing the basics might limit them. Some tasks require a pass-thru query or ADO, and both require SQL. At the very least, users should know when to call someone who does speak SQL.

7: How to normalize data

Using a relational database requires specialized knowledge beyond the system's architecture. The first skill is normalization, the process of creating table schemes according to a fixed set of rules. Try to teach your Access users to apply the first three normal forms competently. Remember, the Table Analyzer can help users in this process, so they don't have to be experts.

8: How to choose the right key

First normal form (#7) allows for no duplicate records, which for our discussion, equates to a unique key. Your users need to know how to choose the best primary key from the possible candidate keys for each table. You might find it easier to teach users to use an AutoNumber data type as the primary key and then apply an index to catch duplicates. Again, Table Analyzer can help.

9: Null is not nothing

Null values confound experts and novices alike -- it isn't the easiest concept to share with casual users. A common misconception is that an empty field or 0 equates to a null value, but that's incorrect (although an empty field can be null). A null value indicates that the data is currently unknown. Accommodating nulls consistently is the key to handling them. Users without this knowledge are prone to creating queries that return erroneous data, because they fail to account for the nulls correctly.

10: How to ensure the validity of data

Even the best database will render erroneous data if the data itself is invalid. Data integrity is a huge topic, and Access provides a number of tools for ensuring data validity. Field properties, such as data type and field size, restrict values. Setting the Required property to Yes eliminates the need to deal with null values (although null values are acceptable). Use a validation rule to express conditions a value must meet. Data entry controls (forms) can limit users to a list of possible values, preventing some data entry errors and typos. These are all skills the average user can and should acquire.

Additional resources

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

4 comments
oldbaritone
oldbaritone

A very handy tool for data integrity is indexes. Construct additional compound indices (using multiple fields, not just one) and make the index unique. (Duplicates not allowed) For example, a names list might have several [lastname] = Smith and several [firstname] = John, but with a compound index you can make sure there is only one "John Q. Smith" using [firstname] [middlename] [lastname] as an additional unique index.

TsarNikky
TsarNikky

I can see why the confusions arises. Just how does one express an "empty column/field" instead of a column/field with an unknown value. Does "unknown" mean that the field has something in it, but just not known? I am sure the Access online help(less) file totally ignores this problem and, certainly, does not give examples in an attempt to clarify all the verbiage that is given.

ManoaHI
ManoaHI

Think of it this way, all fields have a value. NULL is not an unknown value. It has the value of NULL which is an exact value. This is where #6 is very important. SQL is an industry standard (each RDBMS has their own dialect) and it is clearer if you can understand it. e.g. select * from stuff where order_quantity is null Depending upon the DBMS the "where" clause might be: where order_quantity = null What this is saying is retrieve all fields in the table named "stuff" where the order quantity is null. This is not 0 (zero) it has an exact value of null. 0 as a value has an exact mathematical value of 0 and therefore not null. A 0 (zero) as a character has an code value depending upon which character set you are using. e.g. ASCII "0" has a value of hexadecimal 30, decimal 48. A " " (space) has an ASCII value of 20 hex. An empty field is the only one that has what is close to "unknown value" but it really has not yet been determined but for some RDBMSes, you can select where a particular field is empty. Perhaps the only confusing thing is that ASCII code 0 which is defined as the NULL character, is not the same as the value of NULL. The easiest way to remember is that an ASCII NULL (code 0) can be printed (it will print nothing and I think this is where the confusion lies), but an ASCII NULL is not the RDBMS NULL. So if you insert an ASCII NULL (code 0) into a previously NULL or empty field, it is no longer NULL nor is it empty. It will have a value of 0.

ssharkins
ssharkins

You have to adopt a strategy and employ it consistently. Either don't permit nulls from the get-go or be sure to accommodate the possibility of null values in all expressions and queries. I'm sure there are pros and cons to both strategies.