Customer Reviews

Building Microsoft® Access Applications (Bpg Other)

5 star:		(7)
4 star:		(4)
3 star:		(0)
2 star:		(0)
1 star:		(3)

 

 

As a self-taught Access developer for the past eight years, I have an extensive library of books dealing with beginneing to advanced areas of Access. I bought this book to add to my library, figuring it to be another reference item. However, in a casual browsing of the content, I discovered this book has much more to offer. I was pleasantly surprised to see information on levels of magnitude for linked tables, never having known that the number of sub folders for linked tables can affect the speed of reading from them.

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!

Easy to read first few chapters with basic design info, remainder of the book focuses on complete applications that can serve as a template/best practice for almost any other MS Access application.

Viescas clearly understands/communicates an in depth knowledge of Access Applications that I have not seen anywhere else.

Microsoft Access is of course a database. But it's really much more than that. With its front end forms design capability and the integral report generating system, it is well suited for quite a number of small applications. I say small, because you aren't going to use Access to handle the operations of a Fortune 500 company.

There are a lot of books that teach about the basics of Access. They however tend to talk strictly about how to do the programming portion only. They do not go into the practical aspects of database, forms, or report design.

This book takes you deeply into the design of four small and rather simple applications. This way you can see what is needed to actually make an application that is really useful. It is unlikely that you'd actually run one of these applications as is, even if they were applicable to your business, you or your boss would want something changed. But with the knowledge you would get from the rest of the book, this should be a doable task.

There is one combined plus and minus about this book. The plus, it has an excellent introduction to SQL programming, one of the best I've ever seen. The minus is that the SQL is based on the JET engine database. Access now ships with both JET and MSDE which is a much better engine. I suspect that Microsoft will make MSDE the standard engine in the future. Even with this negative, this is an excellent book.

It's very possible that the remaining chapters have some useful examples of application building, but the first chapter contains a number of just plain bone-headed propositions that will seriously mislead the naive developer.

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 artificially 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 show no great understanding 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.

Definitely not for the newbie. This is for the seasoned access programmers. I you are new to programming (at least in access) and are looking for a book on how to program with access, this is not for you. This is a great book for those seasoned programmers who are looking to make their applications better.

This book has 4 complete database applications done in a manner which Codd and Date would be proud of. It's not another book of code or a book of dry procedural language. It's more than that. It takes the reader through the steps required to build a credible application.

As a Microsoft MVP, it has been my pleasure to have known John personally for 5 years. He continues to impress me with his knowledge of databases and SQL. His books are required reading for new Access users and certain to teach even this old dog some new tricks.
--
Arvin Meyer, MVP
Microsoft Access

This book is a good start toward understanding how to build Access applications. Some of the details are missing. An example would include the proper syntax for referencing fields in a form.

The book is well written with real world application samples and practical tips for how to design a database that works instead of trying to mimic a template sample that could be missing key elements needed to run a functioning program.

This was a very good book - concise and helpful - hopefully new ones like this will be available for newer versions of MS Access

This book is written with the intermediate to advanced programmer in mind. There is much to learn from this book on options and techniques to use when building business applications in Access. There is explanations of every sample application and it's function. I would give it five stars if it were not for the title. The title maybe a bit misleading since it talks about building applications since it kind of sways you into thinking that it is a book on the step by step process of "Building Microsoft Access Applications." If you are seasoned in Access programming it is a gem to have in your bookshelf.