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 termuser 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.
Source: techrepublic
No comments:
Post a Comment