[AccessD] Framework Discussion - set up question

DWUTKA at marlow.com DWUTKA at marlow.com
Thu Mar 25 16:37:09 CST 2004


Two things.  No need to do what you described.  Already knew that.

First thing.  AND?  If you could LINK the tables in a corrupted database,
you could have also used DAO/ADO to just pull the data.  Both of which would
have completely ignored the Lookup information.  OR, you could have just
made dummy tables to represent the lookup tables.  Lookup doesn't care if
the old data is there or not, and it will look for a local table, whether
it's linked, or it's native. It also doesn't perform data integrity checks.
It doesn't care if old data fits the lookup stuff or not!  Now, if you said
that Lookups can cause corruption, completely different story.  There are a
lot of things that can make recovery difficult, that doesn't mean that you
shouldn't use code, ban multiple users from your database, or prevent
referential integrity!

Second, still have yet to see proof of performance degradation.  Show me
proof of that before the 'no lookup' side brings it up again!

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Ken Ismert
Sent: Thursday, March 25, 2004 3:54 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [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>

-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list