Hmm, point taken, i'll have to re-visit some of my tables and the lookup fields. I always thought that lookup field makes it easy for users to select values.

Vladimir

Hi again.
I've been monitoring the discussion on "lookup fields" and I have to put in my two cents.

I love "lookup fields" - perhaps more specifically "combo boxes" on forms.

From the discussion below - it seemed like it was stated that lookup fields only displayed a list of created information - not data from a table - and that you should create a table of the data and enforce the RI (referential integrity).....I wholy agree but the lookup fields that you create in tables or combo boxes you create on forms can display data from an existing table - one you are related to with RI enforced etc.....but the combo box allows the user to pick the required data from the list rather than have to type it in. That's the beauty of them.

In addition - I've created my "lookup fields" in the table because then when I created my many forms the combo boxes appeared automatically and I didn't have to create them on each form. I've never understood all the hoopla against lookup fields (combo boxes?).

However, further to information from Bob or Sal (sorry, can't remember who) it seems that the database performance is compromised with the additional work the database has to do to gather the list for the combo box every time it opens the table. This does make sense I hadn't thought about.

So my new approach will be to create the lookup lists (combo boxes) in my tables so the combo boxes come onto my forms, then when I've completed the design of the database - simply remove them from the tables - they're not needed there anymore anyway because no user should ever be allowed directly in a table ;)

Now I know there is also some performance loss due to combo boxes on forms - but if you want a "user friendly" database - I don't know how you manage without them!

There's my two cents :D
Correct me if I'm wrong
Lisa

Hi again.

Two more cents for Bob.

When you go through the lookup wizard in the table, the last question is something like "The table must be saved before creating the relationships".

If you just say no to this message it does NOT create the additional relationship.

FYI - totally onboard with that stupid clippy guy - I've installed all my versions of Office WITHOUT him :D

Later
Lisa

that Clippy,
Lookup fields are OK for databases that will never be upgraded, but for me, there is no way I will ever use them.
Some things to consider, Users, we all love them, should not have access to any table, therefore, the lookup field is useless. If the database is ever upgraded, or even just referenced by another program, such as SQL Server with DTS, the lookup field will somehow not allow for SQL Server to view the table. If you have referential integrity on your lookup, you can put a combo box in a form and this will do the same.
The tables that get created with the lookup wizard are the big problem. Like Bob said, they create some type of nested view or nested table within the table. For most users of access that is OK, for me personally, it is not.
If you give a user full control of a table, and, let's say, the lookup table is used to lookup the 5 stages for your companies implementation of a product, and this 5 stages have been hard coded into a VB module. Imagine what would happen if a user either deletes or adds a record to that table.
Also you have to think forward and remember that you will not be the last developer to work on this application and the next person may not be able to do their job because of a lookup table.

Combo boxes should only be used during data entry, for edit/add purposes. That is why your Primary key and foreign key should be a meaningful field instead of just an autonumber. that is another performance issue.



Sal

My favorite John Vinson (MVP) rants on the evils of lookup fields from assorted microsoft forums (my apologies to anyone who may have read this in a previous post on the old Wrox forum):

"My biggest objection to Lookup fields is that they conceal the relational nature of the data. When you look at a table - you
should *see what is in the table*. That's not what you see when you put combo boxes in the table datasheet! I've just seen far
too many questions on this forum from people who simply don't understand tables and relationships, *because* they have been
seduced by this misfeature."

"In my opinion - and I have no experience with the performance issue - the use of Lookup fields in table datasheet view has far
more problems than benefits, and I *never* use them and try to persuade everyone to avoid them too. Zap them, is my advice."

>Does anyone have an opinion as to whether or not it is a good or bad idea to create lookup fields directly in your tables rather >than setting them up in forms?

"Have you ever dangled a tasty sardine in front of a hungry cat? Yes. I have a vigorous opinion of putting lookup fields in
tables. Unless you have a *rock solid* understanding of relational theory and the detailed structure of your database, it is A Very Bad Idea. It gets you very little - it makes your table datasheet view more human readable, at the cost of concealing the actual contents of the table, and making sorting and searching the table more difficult; it also makes it a tiny bit easier to put combo boxes on forms instead of textboxes. But I advise people never to use this misfeature; just put the combo boxes on Forms, which is what you should be using to look at your data anyway. Table datasheets are for debugging; and as such they should display what they actually contain rather than concealing it."

>Using a lookup field is it possible to get Access2000 to use the values in the field on is doing the lookup in?

"Well, I *intensely* dislike the so-called "Lookup" field type. It merely conceals the data in your table behind a value from
another table. I recommend that it NEVER be used; you can very easily store your tables with the actual data that they contain visible, and use a Query or a combo box or other control on a Form or Report to retrieve data from the linked ("lookup", if that's what you want to call it) table. What I object to is the perverse idea that you should define a lookup *FIELD* in your main table, and use it in your table datasheet. This accomplishes absolutely nothing other than to conceal the actual contents of your table, cause confusion about the relational structure of your data, and mislead users into trying to do data entry and searching in table datasheets instead of properly doing so on Forms."

"It's perfectly easy to put a Combo Box - a "lookup", if you want to call it that - on a Form. The table lookup wizard makes this the default, but at the cost of concealing the actual contents of your table; encouraging you to use table datasheets for data entry, almost never a good idea; creating a new relationship between the main table and the lookup table even if such a relationship already exists; and adding redundant indexes to your table. If you leave the tables alone, and use a Form to do your data entry, you get the best of both worlds. Use table datasheets for debugging and design ONLY, and use the Form and the rich kit of tools that the Form provides for your data entry."

"You're yet another victim of Microsoft's misleading, misdesigned, and all but useless so-called "Lookup" feature. The DealID *IS* what's in your table; the Lookup is merely concealing that fact from you. Base your Report, not directly on the Table, but on a query joining your DealID table to the main table by DealID. This gives you the DealName as a field in the query, and you can display it on your report."

"Joan has one of the major ones - when you look at a table containing Lookup fields, you do not see what is actually in the table. It's disguised. This causes sorting, searching and linking to be VERY confusing and usually wrong. Another issue is that every Lookup field creates a new, hidden Relationship between the tables involved, including creating new indices in both tables - *even if a relationship and indices already exist*. This wastes space and slows updates and provides absolutely NO benefit. A subtle problem is that it discourages people just learning Access from dealing with and understanding the relational nature of their tables. It "magically" brings in linked fields - in a black-box manner - so that the user has no incentive to understand the relational linkages. I've seen many, many messages here from people who put in lookup fields and ended up getting thoroughly
mixed up about the structure of their own database."

"The modest but real savings in convenience in creating forms is, in my opinion, heavily outweighed by the confusion caused to people just trying to learn Access. In my experience, using a lookup field in a table encourages the bad habit of using table datasheets for data entry and viewing, and is a barrier to learning how relational databases work."

One suggestion that was given to me..
  Open the back end mdb file minimized in the background. I was told that opening the backend from the frontend increases performance (and overhead a bit)..

~Edofin

I've looked briefly at the posts regarding this topic, and I have this comment about the splitting problem.
I'm not sure what version of Access you are using, and whether you are using DAO and ADO to access the back-end.
I think this issue should be expolored more closely with MS Access knowledge base, and with recordset tinkering.

I suggest you try to load a table or a query using DAO/ADO even compare the two, and cycle through all the records.
See how long this takes. Start off simple with only tables.
If you can load a table with everything on your local computer that is fast, and on the network that is fast, then you are closer to knowing what the issue is.
Make sure nothing else is running on the machine.
Then this would allow you more of a precise determination of where the problem really is.
Why am I suggesting you do this?
Well, for example, in my experience, a form can be created, and you can ask for a specific type of recordset, i.e. dbSnapShot, or dbDynaset, and without even knowing it, your asking Access to take **ALL** the records from the back-end and into your local hard-drive (this may be with Snapshot, or forwardonly).
I'm not an expert on ADO as much as I am on DAO.
In the form you are dealing with sometimes MS Access does some things you may not expect, and its because of what you have unknowingly set or let Access do for you.
Then if you have bound queries on the form, what those queries have as their options also can have a great deal of effect.
My first impression from reading your first post is that there is something seriously wrong with the database set up and recordsets you are asking for.
You will have to run the multi-join queries with local and server hosting of the back-end to see what the problem is.
Therefore, I am prone to persuade you to investigate this problem more thoroughly because I feel that you will found the real culprit of the problem may not be the network.
I've worked in a company with Novel Networks and it was damn fast.
So, my experience is saying that if you look for more details and do some more test, you will find the real source of the problem is not the network.

Salvatore

Hi!

A member in the MS_Access_Pros group on Yahoo send me a link to this thread, and I find this discussion very interesting.

First, it is VERY possible to create a successful client/server application using linked tables for up to 20 concurrent users. But there are lots of details you must fix to be successful. To get good performance, it's all about keeping the "wire" cool -- that is, making sure you minimize the amount of data you ask JET to fetch and the number of threads you have open to the file server.

One of the most insidious "wire" eaters in Access 2000 and later is the silly Subdatasheet feature. If you leave SubdatasheetName set to [Auto], Access opens an extra thread AND fetches the rows for any recordset built on the table. You'll see a dramatic improvement in performance if you go to all the tables in the data file on the server and set this property for each table to [None].

You must also design your application to minimize the data fetched to the client machine. It is NOT true that JET on the client always does a full table scan. If you apply a filter on an indexed field, JET first fetches the index blocks and then sends specific requests to the file server for the data pages it needs to satisfy the request. So, it's important to both define appropriate indexes on your tables AND to NEVER request "all the tea in china" by opening a form or report unfiltered.

You should design custom "query by form" forms into your application that prompt the user to focus on the records needed for the task at hand, and the QBF code should refuse to proceed unless the user specified at least one filter value. You should also avoid any combo boxes that return more than about 200 rows. Although Access does try to optimize the combo box row source by not fetching all the rows until the user types in the box or drops it down, fetching 1000+ rows in a combo box is a performance killer. Most successful applications provide a way for the user to filter the combo first - for example, by selecting a product category that then filters the product name list. One alternative is to write code in the Change event to lookup the closest value for the user as the user types the entry they want. Single probes via DLookup are much more efficient than fetching the 1000+ row recordset.

In applications that might release the last active thread by returning to an unbound switchboard, performance can be improved by keeping one recordset open in code as long as the application is running. When you close the last bound form or report, Access releases the thread to the server - and if someone else has the data database open, it can take seconds to reopen the thread. If you keep a thread open, other processes in the application will share the open thread.

For reports, consider fetching the "unnormalized" report data to a local table and bind the report to that. Depending on how your report is designed, the report engine can end up fetching all the data from the server four or five times before it displays the result. Also, if you open a large report in Print Preview and the user pages back and forth in the result, the report engine will fetch the data several times again.

Be advised that "upsizing" to SQL Server is not a panacea. If your appication is badly designed for client/server, it won't run any better on SQL Server, and might actually run worse. You should upsize only when you need to manage more than 200 meg of data, you need true security, or you need to support more than 20 simultaneous users.

As for lookups - don't do it, at least not in table definitions. One of the best discussions as to why not can be found at:

http://www.mvps.org/access/lookupfields.htm



John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"