DWUTKA at marlow.com
DWUTKA at marlow.com
Thu Mar 25 16:21:45 CST 2004
My reputation must proceed me! ROTFLMAO! Drew -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Charlotte Foust Sent: Thursday, March 25, 2004 3:32 PM To: Access Developers discussion and problem solving Subject: RE: [AccessD] Lookup Fields in Table Design I think you added too soon. The discussion is far from over just because Drew weighed in on one side. <VBG> Charlotte Foust -----Original Message----- From: John Bartow [mailto:john at winhaven.net] Sent: Thursday, March 25, 2004 1:20 PM To: Access Developers discussion and problem solving Subject: RE: [AccessD] Lookup Fields in Table Design Slam and dunk! 5 point sayers! Score: 2 nay-sayers / 7.5 sayers -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of DWUTKA at marlow.com Sent: Thursday, March 25, 2004 3:10 PM To: AccessD at databaseadvisors.com Subject: [AccessD] Lookup Fields in Table Design Okay, the three reasons given so far for not using the Lookup feature in your table designs are: #1: Lazy developer tool, which can confuse users. #2: Performance Decrease. #3: Bloat Okay, for number one, I personally don't see a point in arguing against that, because it's too darn easy to confuse users. If we didn't use something that could confuse a user, we would have to kick our computers out the door! LOL (sorry, couldn't resist). Now for Number 2. Did a simple test. I created a database with a simple lookup table (with a list of states) and then added a table that had a 'State' field. Copied that database, and then added a lookup for the State field, using the tblStates table. Then I created a little code which added 200,000 records to tblCustomerInfo (my 'dummy' table). BOTH databases took about the same amount of time to enter records. (used an INSERT INTO statement.....that took a while, probably would have been faster to use the Add method of a recordset. Either way, both processes took about the same amount of time.). With 200,000 records in the table, in both databases, opening the table in datasheet view takes the same amount of time in either database. Thus, no performance decrease at all. Number 3. Well, I checked the size of both databases along the whole process. (I did put the lookup table in both databases, so they should have been the same 'size'). Guess what, they were. Started at 92,160 bytes (each) with no data other then the US States, and after 200,000 records, both databases were 5,869,568 bytes. I even manually added records (about a dozen), and both databases were still the same size (in bytes), though I didn't write those numbers down. However, and this is a BIG however. I found a distinct advantage to using a Lookup field. In the database with the Lookup field, I had set the Limit to List property to Yes. I then tweaked my code, and added "XX", instead of a random state, for 10 records. BOTH databases added 10 records with XX in it. Going to the Non-lookup database, I could still manually enter a record with XX as the State. Going to the Lookup database, I could NOT manually enter XX, because it wouldn't let me enter a state not on the list. Setting the field validation for <>"XX" in both tables, prevented the code from adding new records with that value. So that is a very interesting advantage. It allows you to enforce 'user' data entry 'rules', while allowing code inserted/modified data to bypass. That could be handy. One last note. The 'lookup' capability does NOT have to be a SQL statement. Combo boxes and listboxes can both use Callback functions. This DOES apply to Lookup fields. (did a test. Used a previously built Callback function that lists the reports in a db for list/combo box, and added it to a field's lookup property. sure enough, it listed the reports when entering that field.) Drew -- -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com