DWUTKA at marlow.com
DWUTKA at marlow.com
Thu Mar 25 16:21:17 CST 2004
What's the difference? The Lookup field has NO effect on a Form, other then initial design. When you design a form, using the Wizards or the Field chooser, it will use the Lookup information to determine what kind of default control to use. However, you can certainly use a regular textbox, and bind that to the lookup field, and you just don't have any 'lookup' capabilities. (Including the Limit To list). Be my guest, AutoForm a table with a lookup field that is set to Limit to List. Try to enter 'unlisted' data in that field. Can't. Go into the design of the form. Switch the combo/list box to a textbox. Try to enter 'unlisted' data (which isn't listed anymore...). Sure can. So what's your point? All that the Lookup field is doing, is providing easy data entry from a datasheet view (which is a view of forms too), and a reference for automatic/wizard stuff in Access. No effect at all. Show me a performance decrease. It's easy to SAY it decreases performance. But I haven't seen any yet. I created a continous form on that table with 200,000 records. It opened in a split second, and I could skim through all of the records with ease. No different then the 'copy' database without the Lookup. Jet isn't going to run 200,000 querries on a field, if you have Lookup set! It is going to run it once, and only once, when you ENTER that field. Are you saying that using a combo box that querries something is a performance decrease? I feel sorry for your users! Charlotte, this is starting to look more like a developer's hoax. No performance issue, no bloat. I can't argue if you PREFER not to use something, however, there has yet to be anything brought up (which has been proven true), as far as a downside to lookups. Also, how do you prevent your users from coming within a mile of your tables? If they have access to the data within your table, all they have to do, is open a blank database, link your tables to the blank database, and they now have direct access to your tables. They can do anything they could do in your forms. Now, granted, someone confused by a lookup field in datasheet view may not be able to figure out how to link tables, but then again, I use Access as a BE, and even in BACK ENDS I have used Lookup fields. Want to know why? Because I normalize my data structure. However, if I want to go into the tables, and manually mess with the data, I'd be a GOD if I could remember what every User/Product/Request/Customer/Any other IDed field's ID was. So, if I go into a Many to Many table, and want to look for a possible 'issue', either I have to build a query, to look at the relavent fields (which mean more then just the ID's), OR, if the table design used lookups, I can see what I need right then and there! 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:29 PM To: Access Developers discussion and problem solving Subject: RE: [AccessD] Lookup Fields in Table Design And did you try it using *forms*, Drew. That's what users tend to deal with, isn't it? And that's where my experience says they're a bad idea (well, users are often a bad idea, but I'm talking about lookups.) As far as your "advantage", I don't let my users come within a mile of my tables, so where is the advantage? They *can't* manually enter records, period. Charlotte Foust -----Original Message----- From: DWUTKA at marlow.com [mailto:DWUTKA at marlow.com] Sent: Thursday, March 25, 2004 1: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