DWUTKA at marlow.com
DWUTKA at marlow.com
Fri Mar 26 14:11:24 CST 2004
What's the TableID limit? (value and explanation) Drew -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Charlotte Foust Sent: Friday, March 26, 2004 11:39 AM To: Access Developers discussion and problem solving Subject: RE: [AccessD] Lookup Fields in Table Design There is also the issue of TableID limits, which I believe include all the tables involved as lookups. Charlotte Foust -----Original Message----- From: Gustav Brock [mailto:gustav at cactus.dk] Sent: Friday, March 26, 2004 9:04 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Lookup Fields in Table Design Hi all lookuppers Maybe I'm dense but - as mentioned by Ken - if you really need the feature of lookup fields (and I did once for some superuser administrative tasks performed once a year or never, thus not justifying design of eight forms) you simply - in a separate frontend database - create some queries retrieving exactly the fields you need (which excludes the ID), readable aliases for the fields, and lookups as needed. Then everybody is happy. You avoid fiddling with the table design and you can easily redesign the queries if and when needed and you take advantage of a feature of Access which can be handy. And this discussion becomes moot (isn't that the wording for such a situation you use "over there"?) /gustav > Here are "The Evils of Lookup Fields in Tables" from Dev's website: > A Lookup field in a table displays the looked-up value. For instance, > if a user opens a table datasheet and sees a column of company names, > what is in the table is, in fact, a numeric CompanyID, and the table > is linked with a select statement to the company table by that ID. > Any query that uses that lookup field to sort by that company name > won't work. Nor will a query that uses a company name in that field as > a criteria. If a user creates a combobox to select the company using a > value list, the data in the table can be over-written. > Another relationship is created which then creates another set of > indexes when a Lookup field is created, thus bloating the database > unnecessarily. > If a combobox based on the lookup is used in a form, and a filter is > applied, the persistent filter effect of Access often saves the filter > and the next time the form is opened, there will be a prompt for the > value (which cannot be provided, thus creating an error). > Reports based on the lookup field need a combobox to display the data, > causing them to run more slowly. The underlying recordsource can also > be modified to include the table, however the index, (unless it was > set up within a proper relationship) may not be optimized. > Lookup fields mask what is really happening, and hide good relational > methodology from the user. > The database cannot be properly upsized to, or queried by, another > engine (without removing all the lookup fields) because no other > engines use or understand them. > If security is implemented, permissions to tables is usually denied, > and RWOP queries are used for data access. There will often be errors > that there are no permissions on a specific table that isn't even > being used in a query (because the lookup field is). If the queries > are nested or complex, it can take some time to track down the lookup > that's causing the error (that is, if it occurs to you). > http://www.mvps.org/access/tencommandments.htm -- _______________________________________________ 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