Microsoft Access Small Business Solutions


Microsoft Access Small Business Solutions, published by Wiley, proves that Microsoft Access can be used to create valuable business solutions in theory and in practice by providing both the reasoning for the database design and the databases themselves in the CD that accompanies the book.

So much of our information comes from online sources these
days, but I still like books and I’m always on the lookout for a new technical
read. For Access developers, there have always been plenty to choose from and
with Office 2010 coming out, there will soon be even more. One new title
recently caught my eye: Microsoft Access Small Business Solutions published
by Wiley. It’s not about the wiz-bang features of Access 2010 but rather a
back-to-basics book written by a team of what I consider to be the most
talented Access programmers known to the community.

A few months ago I wrote about why Access
still rocks for developers
and I took a lot of criticism for it. Some
people just don’t like Access. But I also heard from many developers who have
proved, and continue to prove, that Microsoft Access can be used to create
valuable business solutions, especially for small businesses with
correspondingly modest IT budgets. This new book proves the precept in theory
and in practice by providing both the reasoning for the database design and the
databases themselves in the CD that accompanies the book.

Content Overview

First off, let me mention that the subtitle of the book is State-of-the-Art
Database Models for Sales, Marketing, Customer Management, and More Key
Business Activities
. This is an important distinction because the
“solutions” discussed are database solutions, not application solutions. While
the authors include some queries, forms and code, the emphasis is primarily on
building a good database foundation based on the models they prescribe.

Accordingly, the book begins with the basics, explaining Tables,
Fields, Data Types and Indexes. If you are familiar with
databases this will be boring, but a book like this could hardly neglect the
topic. Next it discusses the Relational Data Model
concepts
proposed by Edgar Codd in 1969. Normalization is described in
detail, as it should be in a book of this nature and while I’ve read it all
before many times, I must admit the refresher course was helpful. In fact, I
learned something new: The “relational” in Relational Data Model
refers, not to relationships between tables, but to the tables themselves.
According to the authors, the term “relation” was the original designation for
what we call a “table”.

Starting with chapter 4 the book reveals a series of parts:

Part II Dealing With Customers and Customer Data

Part III Producing and Tracking the Goods & Services

Part IV Tracking and Analyzing Financial Data

Part V Independent Areas (Memberships, Models & Other Data Sources)

Chapters 4 through 15 describe how to model a database on
the topic under consideration and the finished product MDB file(s) that
correspond to each chapter are available on the resource CD. The same is true
for topics in several of the appendixes. This sort of tangible, hands-on code
makes the book a real value. I hate to admit it but I’m basically lazy and I don’t
like to type too much so when I find a solution, I prefer to copy and paste.
Some of the resources in this book which may be leveraged in this way include:

1)      Related
table sets for various business objects

2)      Country
and state code tables

3)      Useful
Access VBA Code examples

4)      Comprehensive
links to additional online resources

What Was Done Well

The most common criticism against Microsoft Access Solutions
is that it’s too easy for people with limited experience to build bad
databases. While that may be true, the solution is hardly to ban Access entirely.
We believe this issue can be addressed with education and examples. That’s
what this book does best.

Below are a few of the questions addressed in the book:

1)      Should
I use an AutoNumber as the primary key?

2)      How
should I name my tables? ID fields?

3)      How
might one handle “deleted” data? Audit trails?

4)      What
is a “relationship triangle” and how should it be handled?

5)      When
does it make sense to “denormalize” the data?

The complexity gradually increases with each chapter as the
authors explain their reasons for constructing the solutions/models as they
have. Someone new to database design would do well to read the book all the
way through, even if the solution they seek is introduced in an early chapter.
Some things implemented early are not fully explained until later.

What Could Have Been Better

I believe the word “model” should have replaced the word
“solution” in the books title. Maybe it’s just me, but when I think of
solutions, I think of final products. While some of the sample MDB files
include forms that implement the database models, UI design is not the primary
focus. It is the tables, their structure, their relationships and the process
of arriving at each that is the thrust of this book.

This is, of course, an excellent objective but it was not
obvious to me until I delved into the book a little ways. The forms that are
included are not “pretty” and it’s not always easy to understand what they are
attempting to demonstrate. They contain no error handling and for the most
part are not among the things that may be copy-pasted into your applications.
In all fairness, this is also true of sample code I often supply with articles
though my “demos” would never be confused with “solutions”.

If you keep in mind that the purpose of this instruction is
to help you build a good foundation by modeling your database after one that is
solid and easy to maintain and expand, then you will not be disappointed. At
least that’s true for people new to database design. Old timers who are set in
their ways may take issue with some of the conventions proposed.

The newsgroups are filled with heated debates about the
advantages of ADPs over MDBs, whether an SSN can serve as a primary key and proper
standards for naming conventions. The authors represent a point-of-view that
is very solid, one you cannot go wrong following, but one that lends itself to
disagreement.

One example I found was the assignment of the size property
for text fields. In their sample databases, most field sizes defaulted to 50
characters, unless another value was obvious, like 2 characters for a state
code. I personally advocate using a larger variety of field sizes, following
either Binary or Fibonacci numbers. (2, 4, 8, 16, 32 etc. or 1, 2, 3, 5, 8,
13, 21 etc.) It’s a personal preference but one that would have impressed me had
the authors had included it.

In other cases there were simply mistakes in assigning types
to fields. In the People database the [LastUpdatedBy] text field was set to
255 characters, the maximum size allowed. I can’t imagine it needs to be that
big. In the Accounting database the [CreatedBy] field had the same data type
as the [CreatedDate] field, namely a Date/Time type. This appears to simply be
wrong. These are mistakes that should have been caught. Perhaps an updated
download will be available at the Wiley web site after such issues are found
and corrected.

Conclusion

As Luke Chung said in the forward to the book, “The strength
of Microsoft Access is that with the proper mastery of its features, you can
create database applications that meet the needs of your organization quickly
and cost effectively.” Building on a strong foundation is an important first
step and the models described in this book will help.

I’ve known and consulted with some of these authors for over
a decade on the Access newsgroups and I have great respect for their
expertise. I have no doubt that the many pro-bono hours they contributed to newsgroups
helped them to prepare this volume. I was one of those who benefited from the
online discussions years ago. In my opinion their book, Microsoft Access
Small Business Solutions
, boils down some of the best practices into a
single, handy reference.

Additional Resources

Microsoft Access: Customize Forms and Reports

How to Make Charts in Microsoft Access

»


See All Articles by Columnist

Danny Lesandrini

Danny Lesandrini
Danny Lesandrini
Danny J. Lesandrini currently works as the IT Director for Pharmatech Oncology Inc. at http://www.pharmatechoncology.com/. He holds Microsoft Certifications in Access, Visual Basic and SQL Server and has been programming with Microsoft development tools since 1995.

Latest Articles