What do you all think 2

[Underling]

I have just finished reading the below text at

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

I also am just finishing off an application that I have been working on for the past month that has about seven look up combo boxes.
I haven't run into any problems so far but this has me worried.
I think its to late to change anything that I have done but if I am making a big mistake I would like not to do it again.
Please, if you have the time, read and post comment.
Thanks
Underling

The Evils of Lookup Fields

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).

------------------------------------------------------------
Be ever vigilant, the stupid are everywhere

 

[TelcoBT]

I'm coming to the end of a development where I took over a database that was built by a technical user who used this method in every table and every field where it was applicable, he also used lots of macros and sub forms. Initially I suffered almost daily, and at times hourly corruptions as I worked with the database and I found it extremely confusing because he named the fields on what he saw - eg User Name, Building Name etc, but these fields were in fact User Id , Building Id. I became so frustrated with the problems that I decided to suffer the financial penalties of working for nothing (it was a fixed price contract) to recode and replace all the clever Access things like that lookup. Corruptions in development are now rare, and the application runs faster. I still have problems because I've not re-written everything, so field names are sometimes inconsistent, but my experience is that I would not use these things in an application that is to be multi-user or more than a few simple tables.

I don't have hard evidence for these things being the cause, but even replacing DLOOKUP with my own hand coded functions appears to produce a noticeable improvement in speed. So I'm now converted to avoiding Access built in options in favour of my own code wherever possible.

 

[johnnymac43]

If you put Volkswagen tyres on a Ferrari and try to corner at full speed the results will be spectacular.

If you put a clunky front end on a properly constructed database, likewise.

A good guide for Access development is:
> If you are the only user, do whatever.
> If anyone else will use the system, build a bullet-proof front end.
> If the front end is good enough, the database can be as good or as bad as you like. The difference will be in speed, but not in crashes or corruption.

Cheers

John

 

[Underling]

Ok I see.
The App that I have just finished should be ok. It’s for a single user and is only Six tables to cover inward and outgoing billing.
I just started to use them because it was easier as I built the tables to go "Click Lookup" and then let the form wiz do the rest.
Thanks for the advice guys.

Underling

 

[Pampers]

Hi there,
What is a good alternative for the lookup in a table?
Peter.

 

[Eupher]

poalaerds,

Do the lookups from a combo or listbox on your forms.

Ken S.