Building Microsoft® Access Applications (Bpg Other) Paperback – 27 Feb 2005
Customers who bought this item also bought
Enter your mobile number or email address below and we'll send you a link to download the free Kindle App. Then you can start reading Kindle books on your smartphone, tablet, or computer - no Kindle device required.
To get the free app, enter your mobile phone number.
Would you like to tell us about a lower price?
If you are a seller for this product, would you like to suggest updates through seller support?
From the Back Cover
About the Author: John L. Viescas is the author of Microsoft Office Access 2003 Inside Out and the top-selling Running Microsoft Access books from Microsoft Press. He is president of Viescas Consulting, Inc., a respected provider of database application design and editorial consulting services. He has been recognized by Microsoft Product Support Services as a Most Valuable Professional (MVP) every year since 1993 for his contributions to the community of Access users. Product Description: Get expert design guidance and fully functional sample databases from John L. Viescas- popular author, consultant, and Microsoft Most Valuable Professional (MVP) for Access. Designed for everyone from Access power users to application developers to beginners, this book focuses on five of the most common database types: Inventory Management, Membership, Customer Support/Event Tracking, Reservation Management, and Questionnaire/Statistical Analysis. For each, you'll walk through usage scenarios, design considerations, typical problems, and multiple solutions, along with complete sample databases. You'll examine the logic behind the table design for the application type and see how to use it as a template for quickly creating your own Access solutions. You get everything you need to design with confidence, avoid common pitfalls, and hit the ground running!
About the Author
John L. Viescas is a database expert who provides consulting and training services to companies around the globe. He is the author of numerous books, including Running Microsoft Access 97, Running Microsoft Access 2000, Microsoft Office Access 2003 Inside Out, Building Microsoft Access Applications, and Microsoft Office Access 2007 Inside Out. John has been recognized as a Microsoft MVP since 1993.
Home Page: Viescas Consulting, Inc.
Top customer reviews
Most helpful customer reviews on Amazon.com
Each of the sample databases has more than enough explanation and code to fully understand the how and why of the application.
After reading only the first three chapters, I have learned much that I was never aware of. I thought I understood Single and Double number types, but was re-educated by the information on page 18. Pages 32 and 33 taught me some new things about the difference between format and input mask.
I am now much better at my development, and I haven't yet finished the book. Excellent work, Mr. Viescas!
One proposition, repeated in several contexts, is that somehow an Autonumber field, being an "artificial key", is a bad idea. This is an egregiously wrong-headed view. A primary key in a table should ideally tell you nothing about the data on the row except the value you need to retrieve the row. On this score, an Autonumber field is ideal.
The assertion that some designers just "give up" and "throw in" an Autonumber shows a profound misunderstanding of normalization and should not be given a moment's credence.
An Autonumber field is, strictly speaking, an "artificial key", as the author says. To imply that this makes it also wrong is at the very least a misreading of a phrase, and suggests a cursory level of understanding of the subject at best.
Incidentally, another artificial key, widely used, is the GUID (Globally Unique Identifier), typically handled and presented as a long string of hexadecimal characters. The only difference between the GUID and Autonumber is that Autonumber values are sequential numbers (GUIDs can be, but normally aren't, and for a reason) and they are limited to a smaller set of values (2 billion and some for Autonumber versus 3.4 x 10^38 for GUIDs).
The differences are not germane to this discussion. Both GUIDs and Autonumber fields provide primary keys that tell you nothing about the row they identify, except the value you use to retrieve the row. Far from being somehow "wrong", versus human-readable keys, this is exactly what you SHOULD be using.
This is not the place for this level of detail, given the number of issues that arise in the first chapter, but a summary is surely merited.
He warns against "oversimplifying tables", which is to say, normalizing fully. Why he would see it as a problem that you must normally use queries to view the data is beyond me. Table design for relational data is not, repeat NOT, about human readability. It is simply not a factor. For spreadsheets, yes, but we're not talking about Excel.
Table design is about correct relations between lists of things that you need to represent, nothing else. The payoff for normalizing properly ("oversimplifying tables") is that your applications can handle the stuff in your lists easily and correctly, and there is even a performance benefit. (The performance argument is less compelling partly because gains from proper representation can be offset by the larger number of joins required when data is fully normalized. When that becomes a genuine problem, you start creating data warehouse repositories where everything is human readable and nothing is updatable anyway. Human readability of keys is not a consideration in live data.)
Using "artificial keys", according to the author, "masks the value for sorts and searches". Again, not an issue. Use queries. That's what they're for, to restore human readability to information that has been purposely placed in a logically compelling arrangement (multiple tables). If you don't think that queries are the answer for all normalization problems, use Excel, and stay out of relational design.
Don't whine because you can't read a single table and tell what's happening at levels above it. (The author could not read a stock record and tell what product was in stock in a design using "artificial keys", meaning autonumber field.) Expect human readability loss. It's an all but inevitable product of normalizing fully and using proper primary keys, keys which tell you nothing at all about a row of data except what value is needed to retrieve the row.
He observes that "you cannot join or link tables on a null value". It's true, in a trivial sense. You cannot literally use the JOIN operator to match fields on null values and get what you were hoping for. You can however add two fields to your query and use "IS NULL" as a criterion for both of them. There's your (INNER) JOIN.
More NULL silliness: Does A = B? Well, you can't answer that question "if A or B or both contain a null". Yes, you can. See above for doing it with criteria. Or, use the NZ or IsNull function in Access.
He recounts a sad example of some row being deleted from an upper level table, and having "no way to put it back ... with the original number in it", except by using "a brute-force insert query". Just for starters, use of relational integrity would have prevented the problem altogether, by blocking removal of an upper level table row whose primary key was in use as a foreign key elsewhere. It just wouldn't have happened. The author may not have had this option, since his application is described as working between a home office and remote users. Put referential integrity aside for this one, for lack of information. (I half suspect that use of built-in synchronization, instead of custom synchronizing code, as he describes it, would have solved the problem anyway, but put that aside, too, also for lack of information.)
Still, the answer in this case is exactly to run the one query you need to restore the row. There is nothing brute force about this. It's how you recover. Use LEFT JOIN from lower level to upper level table to identify the missing key that must go in the upper table. Replace the row in the upper table to restore that primary key. Then edit the remaining fields, and you are done.
In fact, you don't even have to edit the fields (though you would, to be nice). Since the data is now healthy again as a result of running exactly one append query, you could let the user edit the upper level record in whatever form would normally be used for that purpose. Just fill some fields with "**edit me**" as a signal that the record needs attention. It's trivial.
But not for this author. Get this: Rather than simply run one query and then edit some fields to solve the problem, the author "ended up writing a lot of unnecessary code and some special data entry forms to solve the problem". Unnecessary hardly describes it.
"In the correct design on the left..." Sigh. This is another jab at using "artificial keys", whereas his design (the correct one on the left) uses human readable and sometimes compound keys. I'm not such purist that I would never use a human readable key. I do it occasionally for lookup tables, so that I can store a short, but still readable, key, but also provide a full name and even a description. I do it mainly when I'm pressed for time and I know that the application at hand has no big future anyway.
It's practical in some cases, and causes no great harm, but it is technically a violation of the injunction that the primary key should tell you nothing about the row, and text joins are certainly somewhat slower than numeric joins. In the universe of Access applications, however, this consideration is typically not a major factor. Still, to call his definitely compromised solution the "correct design" is just beyond boneheaded. It's naive.
As for composite keys, which the author encourages: Just say no. Don't use them. They are annoying in every way imagineable. Maybe, MAYBE in the lowest level tables you could possibly have, EVER, in your data, they might be a slightly effort-saving compromise. But the first time you need to add a level below that level, you'll wish you hadn't.
However interesting the rest of the book may be, and it could be, approach it with great caution, knowing that the author shows no great understanding (it is tempting to say no understanding at all) of data design. He could be great at forms and reports and VBA. I don't plan to bother finding out.
My own personal choice is still Litwin/Getz/Gunderloy series of Access Versionwhatever Developer's Handbook, expanded to two volumes several years ago.
Look for similar items by category
- Books > Computing & Internet > Computer Science > Information Systems
- Books > Computing & Internet > Databases
- Books > Computing & Internet > Digital Lifestyle > Online Shopping > Amazon
- Books > Computing & Internet > Microsoft Windows > Applications > Access
- Books > Computing & Internet > Programming > Software Design, Testing & Engineering > Functional Programming
- Books > Computing & Internet > Programming > Software Design, Testing & Engineering > Software Architecture
- Books > Computing & Internet > Software & Graphics > Business & Home Office > Home Office Databases > Microsoft Access