[AccessD] Lookup Fields in Table Design

John Bartow john at winhaven.net
Thu Mar 25 20:40:14 CST 2004


Already gave points for the corruption issue but this certainly reinforces
it. To me doing everything you can to prevent corruption in an Access DB is
similar to doing tape backups - chances aren't real good that it will be
needed but when bad luck strikes - its worth the effort.

1 point for the second point though - and 1 point for the clever comeback
line!
:o)

-.5 for not changing the subject line

Score: nay-sayers 4 / sayers 7.5

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Ken Ismert
Sent: Thursday, March 25, 2004 8:19 PM
To: 'Access Developers discussion and problem solving'
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






More information about the AccessD mailing list