DWUTKA at marlow.com
DWUTKA at marlow.com
Fri Mar 26 01:12:29 CST 2004
Ken, can you send me your 'sample' database, where you see the performance degradation. Not that I don't believe you, but I have done everything you said, and have see NO degradation. I want to make sure we are on the same page before this gets uglier. As to stuff that is already ugly, I still don't understand your point with errors in the Front End. Practically ANYTHING in Access can produce an error for you, if done WRONG. So saying 'I'm not going to do something, because if it's done wrong, then I'll get errors', is basically saying you'll never do anything! As for the corruption issue, yes, I would definitely try using code to recover data, if possible. There's nothing complex about it. Looping through a recordset is one of the basic coding techniques! Drew -----Original Message----- From: accessd-bounces at databaseadvisors.com To: 'Access Developers discussion and problem solving' Sent: 3/25/04 8:18 PM Subject: RE: [AccessD] Framework Discussion - set up question Drew > No need to do what you described. Already knew that. So you acknowledge that errors in the lookup query or the underlying lookup data will cause errors in the front end. That appears to contradict your earlier statement: "But is that going to affect anything using that .mdb as a BE? No.". My point was to show that, in three minutes, you can construct a scenario where lookups in the BE cause FE errors. My errors were contrived, admittedly, but we all know what can happen in production environments. Access is not the most robust data server, even in ideal conditions. Why add elements to your tables that could cause additional errors in production? >If you could LINK the tables in a corrupted database, >you could have also used DAO/ADO to just pull the data. For those of you willing to try my example, you would see otherwise. You just can't simply link and open a query against it. Sure, you could get fancy and write code, under pressure, to try to get your data. Drew might be eager to do that, but I'm not. You do tacitly acknowledge that it "can make recovery difficult". So, why make your life more difficult, when you can get all the benefits of lookups, with none of these drawbacks, by using them in queries? >Second, still have yet to see proof of performance degradation. >Show me proof of that before the 'no lookup' side brings it up again! Oh, very well. Since you contrived the best of all possible examples to support your 'no performance degradation' claim, I wrote some code and contrived the worst: a 200 record main table with a field lookup defined against a 200,000 record lookup table, unindexed. The results were predictable: queries against the reference table, with no lookup defined, ran lightning fast. Queries against the main table with the lookup took about 7 seconds to reach the end of the recordset on my machine. A datasheet form based on the sluggard query was even worse: the underlying query took its 7 seconds, and the form's combo took an additional 3 seconds or so, for truly lousy performance! Nothing's free, Drew. Any form, using a lookup combo, which is based on a query pointing at a table, which is doing its own lookup for that same field, is doing double work. To be fair, opening the querydefs in code produced no noticeable degradation. So did direct SQL. But completely eliminating query-based forms and reports to avoid the performance hits for table lookups is kind of cutting of your nose off despite your face, no? To end, I am NOT opposed to lookups! Just put them in queries or forms, where they more appropriately belong! -Ken -----Original Message----- From: DWUTKA at marlow.com [mailto:DWUTKA at marlow.com] Sent: Thursday, March 25, 2004 4:37 PM To: accessd at databaseadvisors.com Subject: RE: [AccessD] Framework Discussion - set up question Two things. No need to do what you described. Already knew that. First thing. AND? If you could LINK the tables in a corrupted database, you could have also used DAO/ADO to just pull the data. Both of which would have completely ignored the Lookup information. OR, you could have just made dummy tables to represent the lookup tables. Lookup doesn't care if the old data is there or not, and it will look for a local table, whether it's linked, or it's native. It also doesn't perform data integrity checks. It doesn't care if old data fits the lookup stuff or not! Now, if you said that Lookups can cause corruption, completely different story. There are a lot of things that can make recovery difficult, that doesn't mean that you shouldn't use code, ban multiple users from your database, or prevent referential integrity! Second, still have yet to see proof of performance degradation. Show me proof of that before the 'no lookup' side brings it up again! Drew -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Ken Ismert Sent: Thursday, March 25, 2004 3:54 PM To: 'Access Developers discussion and problem solving' Subject: RE: [AccessD] Framework Discussion - set up question -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com