DWUTKA at marlow.com
DWUTKA at marlow.com
Fri Mar 26 13:27:58 CST 2004
I'm shocked. I used Dev's site quite a bit, when I started out with Access. I really admire a lot of what he has done. However, I think a lot of what he has about Lookup Fields on there are wrong. I just changed my test, from a simple State lookup, to a StateID --> StateName setup. Added an ID (autonumber) field to tblStates, and then updated the data in my 'main' table to have the ID's, instead of the abbreviation. Then I switched it to a number field. I then copied the database again, and switched the copy to be a lookup field for the StateID. Compacted both (a little over 200,000 records in both), and NO difference in size. Without indexing... querying is the same speed, with indexing, the querying is still the same speed. However, I now noticed a difference in size, with a lookup field, when the field is indexed. That is definitely interesting. However, that goes away, if you use a callback function. VERY interesting. Have to give them half a point back, because there is a small amount of 'bloat', if you use a query/table for the lookup. But it's negligible, and also, both databases grow when you index the field, just grows a bit more. I would never really call that 'bloat'. Not trying to back track, but to me, bloat is when a database grows in size over time, but when compacted, it gets smaller. The portion that is 'reduced' is bloat. The portion left after the compact is 'overhead'. That's my definition. Admittedly, if you are going to run close to max db size, then lookups should be removed, to get that much more space. But we are talking overhead space, not really 'bloat'. As far as the compacting issue, that's another story. Microsoft has several good reasons for not releasing the inner workings of JET. First of all, there is the licensing issue. If you developed a software package you wanted people to buy, would you give anyone the source? Of course not. If MS gave out the inner workings of JET, it wouldn't be long until Access was unnecessary, because people would just roll their own JET engine. Another reason is support. Jet does a lot of stuff, so even if they did hand out the detailed specs, one little slip, and even a good developer could seriously fry an .mdb. There is nothing mysterious about corruption. It's usually easy to explain, and in most cases is fixable. What you must remember, is that file corruption is NOT limited to .mdb's. It can happen in any type of file. The difference between Access and other file types, however, is what makes it seem like more of an issue. A few years ago, our network was in a serious state of disrepair. We had file corruptions all over the place. That included Word and Excel files, and definitely included .mdb's. A file is nothing more then a series of bytes. With a bad network, flaky hard drive, corrupted OS, (and a million other reasons), it certainly is not impossible to goof a few bytes here and there. Now, depending on what kind of file it is, and what that file does, is going to determine how the corruption affects things. Take, for example, an .exe or .dll. Those files contain CPU instructions. Have you ever had a program start crashing on you? After an Uninstall and reinstall, everything is working again. A lot of times that is due to a file corruption. One corrupted (changed) byte in a .dll or .exe, and you are now changing how the CPU deals with that code. All sorts of things can happen. However, in something like and HTML file, HTML is designed to ignore what it doesn't understand, so it's nearly impossible to have normal file corruption do any real 'damage' to an HMTL file. The more common issues with Word and Excel are the 'locking' files. Something will goof in them, and then that file is locked out. Since Word/Excel is saving changes in those temp files, actual corruption in the original document is more rare (but not unheard of...excel and word have to update the original document when you save it, so that is when corruption can occur there). With an .mdb file, JET is monitoring who has what locked, is reading/updating indexes, reading/updating data, etc. A lot of activity in one file. The .ldb file is only storing who's logged in (and it is also used for locks within the .mdb, but other then the who's logged in, no other data is being changed in the .ldb, only locking bytes). Get a goofed byte in the mix, and things can either continue, go seriously awry, or somewhere in between. just depends what was affected. A lot of time, the corruption is simply someone's machine not 'closing' out of the database correctly. That is easily repaired. But if bytes are corrupted in the indexes, the table definitions, or in the code, that is something the system doesn't know how to 'replace', because it doesn't have a 'template' or 'starting point' to replace it with. As far as I know, the ONLY design issue KNOWN to corrupt a database, happens to lie with a problem between JET and Foxpro. Something about the differences between how JET deals with Memo fields, and Foxpro deals with them. Access does NOT corrupt .mdb's, it is an outside influence that does that. That is a VERY important concept to remember. Because of that, there are NO features within Access that will corrupt a database. (Yes, a feature could be programmed wrong, but Microsoft would eventually fix that with a service pack, because a self corrupting database system won't sell....). Recovering from a corruption all depends on where the corruption actually lies, which is not entirely blind luck. Since most corruption issues are only put into affect when a database is being read/locked/written, what you are reading/writing is usually going to be the place where the corruption lies. So it could be data within a table, table defs, indexes, code, etc. Practically everything used within a database. So not only are all database features designed to NOT corrupt the database, but they are also all prone to fail, if file corruption does occur. On top of that, all features have their own quirks as to the difficulties of recovering. Corruption in the data isn't too bad, since you'll just get a bad record or two. Corruption in code is more difficult, because it will probably lock up quite a bit of the code, but that can be returned. Corruption within a tabledef, or index can affect whether the data is easily retrievable, since JET uses them when retrieving it. Hope that clears up some of the mystery for you. Drew -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of John Bartow Sent: Friday, March 26, 2004 10:39 AM To: Access Developers discussion and problem solving Subject: RE: [AccessD] Lookup Fields in Table Design Hi Drew, Don't misunderstand me, your points are well taken, and you have my utmost respect as a developer/programmer. Corruption is just a hot spot on my psyche. If I knew HOW it was caused it would be different and that's the issue - apparently no one (including M$) does or they would prevent it. Even when considering that M$ allows some things to happen/not happen as a bone thrown to its third party developers, no one has a corruption fixing utility that works and is making money off of. "Data goes into black box - gets corrupted - data doesn't come out of black box." That's actually my biggest concern with Access. The absolute (publicly) undocumented proprietary nature of it. Sometimes I think the (publicly) can be left out of the statement. I'm actually quite surprised by the results of this little debate. Ken, Charlotte, Dev, Lebans and others all warn against it. I think the question I posed here to Susan concerning lookups originally came to mind about 5 years ago when I saw it on Dev's website under the "ten rules". I don't follow "rules" - when I see the word "rule" my mind automatically translates it to "strong guideline", but it made me curious. Like I said I don't generally use lookups, I have, and I will if the situation calls for them again. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of DWUTKA at marlow.com Sent: Friday, March 26, 2004 1:17 AM To: accessd-bounces at databaseadvisors.com; accessd at databaseadvisors.com Subject: RE: [AccessD] Lookup Fields in Table Design John, the points are cute, but the problem is that Lookup fields do NOT cause corruption. He said they made it more difficult to fix. There are a TON of things (that everyone uses) that make repairing corruption more difficult. Essentially, if you make a blank database, and put it on CD, you stand the best chances of recovering that database when it's completely blank, and never used. Once you start adding data, code, objecs, etc, you are adding things which can complicate a recovery. So it's a no pointer / moot point, to say that lookup's made a particular recovery difficult. If there had been a bit of error handling code, which got in the way of recovering a database, would you say 'I'll never use Error Handling code, because it made my life difficult once'? I didn't think so. Drew -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com