[AccessD] Framework Discussion - set up question

Drew,

>Lookups are just part of Access' table design, and are relatively
>irrelevant when you use Access strictly as a BE. ... But is that
>going to affect anything using that .mdb as a BE? No.

Try this:

 1. Create a blank database. Save it as Test.mdb.
 2. Turn off Name Autocorrect (Tools, Options, General)
 3. Import all tables from Northwind.mdb. Note that the Orders table has
lookups defined for the CustomerID, EmployeeID, and ShipVia fields.
 4. Rename the Customers, Employees and Shippers tables. Reopen Orders. You
will now get intermittent errors saying the recordsource specified in the
lookup doesn't exist.
 5. Create a new blank database, and save it as TestLink.mdb. Link to the
Orders table in Test.mdb. Open the linked orders table. You will also get
the same intermittent errors, especially after re-opening TestLink. (My test
platform is A2K.)

When a linked table is queried in the front end, Access tries to open ALL
the lookup queries associated with that table in the back end. Errors in
those lookup queries will propagate to the front end.

This can play havoc when trying to recover corrupted data. I personally
experienced this when corrupted data in the lookup tables was preventing
data in the main table from being read. Only after opening the table in
design mode and removing the lookup queries were we able to get a partial
recovery.

In short, data is too important to risk with such an error-prone feature as
table lookups. The performance degradation issues, while valid, are
secondary. The same applies for subdatasheets.

If you want to use lookups, make a query, and define them there.

-Ken

-----Original Message-----
From: DWUTKA at marlow.com [mailto:DWUTKA at marlow.com]
Sent: Thursday, March 25, 2004 1:59 PM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Framework Discussion - set up question


I don't get that 'main' argument.

<snip>