Access 2007: The Mssing Manual by Matthew MacDonald The unconfirmed error reports are from readers. They have not yet been approved or disproved by the author or editor and represent solely the opinion of the reader. Here's a key to the markup: [page-number]: serious technical mistake {page-number}: minor technical mistake : important language/formatting problem (page-number): language change or minor formatting problem ?page-number?: reader question or request for clarification This page was updated February 14, 2008. UNCONFIRMED errors and comments from readers: (42) 4th line from bottom; even attempt do anything vs. even attempt to do anything (67) Bottom of the page, first bullet; The date is listed as year-day-month (2008-23-2) but it is called "the international year-month-day standard" {77} Next-to-last paragraph; Says "Every table must have a primary key." Access doesn't require that a table have a primary key, so "must" is a bit strong. Certainly every Access table should have a primary key by the time a database designer is done, but the lack of a primary key requirement is helpful at times early in the design process. (77) Figure 2-20; This figure shows 10 records vs. This figure shows 4 records (79) Second paragraph, last sentence; should be "is more work tha[n] is seems" {79} Note in middle of page; The described procedure (using "drag the mouse to select more than one field") implies that only adjacent fields can be joined to create a composite primary key. Using the Ctrl key to multi-select fields (and keeping it pressed while right-clicking), you can also select non-adjacent fields. {79} 1; The text says that using a more descriptive name than ID for an autonumber primary key is unnecessary. But that's not true with Access. There are numerous situations when you are using Access as a front-end to a complex client-server database with tables linked via ODBC. (Administrator-level types do this, especially with Oracle databases, because Access provides a civilized interface to the data, especially for composing one-time queries and reports quickly. Also, for medium sized operations where scalability is less of an issue, it is drmatically faster to use Access as a RAD environment to build front-ends to high-end Databases than develop Web front-ends; in this situation, you initially develop the application as an Access database/app - steering clear of pitfalls such as attachments, value lists for lookup or multiple-value lookup)- and then upsize your schema, so that the forms, queries, reports and code modules continue to work as your Access tables get replaced with ODBC-linked tables with the same name. In this situation, you will not have set up most or all of your telationships in Access, because you would simply be duplicating what you have already set up in your server database. (It doesn't help much anyway, since Referential Integrity doesn't kick in for ODBC-linked tables.) In this case, when you start composing a new join query, Access tries to be helpful and link ID fields in different tables together, as you note on pg. 205; you have to break these links and set up the links that matter. If you had named the primary key column CustomerID to begin with, and used the name CustomerID consistently as a foreign key, Access would actually be helpful instead of forcing you to fight against it. {84} Paragraph under heading "6. Include an ID Field"; This is only a suggestion... Database experts choose different approaches for this topic. Personally, I use an AutoNumber ID field occasionally, but I avoid it when I have a solid data-centric candidate key such as an account number, a login ID, a product number, etc. I appreciate the author's perspective, but I think it would be good to acknowledge that this sixth principle is less universally accepted than the other principles. (If you want to take the concept further, principle #3 on page 82 also leads to disagreement of opinion. I don't happen to mind nulls in database fields, but some database experts do, and they suggest creating multiple tables with 1:1 relationships, intentionally NOT including all details in one place.) (93) 3rd paragraph; The sentence states "Frozen columns must always be positioned at the left size of the datasheet." It seems like it should say "Frozen columns must always be positioned at the left SIDE of the datasheet." [96] 1st paragraph; Says "In an unsorted table, records are ordered according to when they were created, so that the oldest records are at the top of the datasheet..." Records in Access are ordered according to the primary key. When a non-random ID field is used as the primary key (as the author recommends elsewhere in the book), the records do appear to return in chronologically-entered order because the chosen primary key increases in chronologically-entered order. However, in other cases - and they are very common - the pattern described in the book is inaccurate. (Note: I'm aware that a true database expert would say that the order of records in a database should be considered indeterminate, but that's a bit picky.) (98) 1st sentence; Missing word (likely "a"): "In order to filter records, you specify a condition that [missing word here] record must meet ..." (102) Step 1 at bottom of page; Says "Choose Home --> Sort and Filter --> Find". Should be "Choose Home --> Find --> Find." {119} Next-to-last paragraph; Says "Any table's first rule is that each record it contains must be unique." It certainly is important to do so for relational database integrity, and it's always a good idea, but Access doesn't require that a table have a primary key; Access does allow duplicate records. The wording "rule" implies (to me, anyway) a rigorously enforced constraint. I find this behavior very helpful when importing data from less-than-optimal external data sources. I create Access tables, use Find Duplicates queries to isolate the rogue data, manually fix the issues, then implement primary keys. (121) First sentence, continuing from p. 120; Says "a field," should say "an index.": "... which means Access doesn't create [an index]." [130] last paragraph; In the last sentence it says "it stores literals (in this case, two parentheses, a space, and a dash)". It should say "it stores only the characters typed in, not the literals". [154/5] Both pages; I have tried to create the relationship exactly as shown, but get the following error message "Relationship must be on the same number of fields with the same data types" (165) Sentence above Figure 5-14; Says "if you switch to the design view of the Dolls table..." Should say "if you switch to the datasheet view of the Dolls table..." (192) In Tip:, 3rd paragraph from bottom; The cited page number should be 229, not 119. (198) In Note:, 3rd paragraph from bottom; Misspelled word(s): in the second set of parentheses, "SQL Server" is incorrectly spelled "SQL Sever." Also, just prior to that, the word "name" should probably be either "named" or it should be deleted. (206) "Note:" section; It is printed, "... then what do you end up with: a list of classes or a list of instructors?" This should be, "...then what do you end up with: a list of orders or a list of customers?" You had mixed up the school example. [230] In POWER USERS' CLINIC; The first right parenthesis in the example formula is in the wrong place - it's too far to the right, and the result appears as an error in Access. The formula should be FirstWordProduct: Left([ProductName],InStr([ProductName]," ")-1) (Minor point: The "S" should be capitalized in the middle of "InStr" too - it's incorrectly shown as "Instr" in at least nine places in the POWER USERS' CLINIC block.) (231) 1st paragraph in WORD TO THE WISE; Misspelled word - says "... for you date field," should be "... four your date field." {233} Bottom of page; The formula should include a space: [FirstName] & " " & [LastName], ... (473) Find a Record section, GoToControl bullet; field name is mentioned as "Description", but I believe it should be "Diet" - see table at bottom of page. Note: if this is a true erratum, then it repeats on page 486, 1st paragraph. [593] Note near middle of page, the paragraph above it, and the next-to-last bullet on the page; The Note says, in part, "In order to successfully use a password with a back-end database, you must apply the password before you split the database." The second paragraph of Microsoft's Database Splitter wizard window disagrees: "If your database is protected with a password, the new back-end database will be created without a password and will be accessible to all users. You will need to add a password to the back-end database after it is split." When I use the book's recommended approach, I can open the back-end database directly without a password - it isn't secure at all, just as Microsoft's warning states. The front-end remains protected with the pre- entered password. When I use Microsoft's recommendation to password-protect the back-end after a split, my wizard-created front-end database no longer can access the linked tables, even when the passwords are identical.