[AccessD] Question about ANPKs

Arthur Fuller artful at rogers.com
Thu Oct 6 10:58:59 CDT 2005


Scenario:
Database 1 -- a legacy app that is being redesigned. It contains some tables
with ANPKs and some without. In addition, it suffers from numerous design
problems, including nested tables. (I inherited it, don't blame me :)
Database 2 -- my revised version, in which all tables contain ANPKs. In 
I have to import all the old data into the new design. One particular table
in the old design has been refactored into six tables (no lie). Various
other tables have been refactored as well, but into fewer tables.
My plan is to link the tables in the old database to the new one, then
perform numerous append queries with various degrees of smarts so that the
old data ends up in the refactored database correctly.
The basic question is this: given that a source-table is ANPK in the old
app, can I do an append query and specify that the old ANPK numbers go into
the new ANPK column as-is?
I suppose I could build a test table and try it, but I just wondered whether
anyone had the answer off the top. I know that this can be done in MS-SQL
but I can't recall ever doing it in Access.
------------
A related question: given that the new database has a whole lot of
referential integrity and the old database has none, I cannot do the appends
in just any order. I must guarantee that all foreign keys go in first,
before I attempt to import the rows depending upon their existence. Another
way to say this is, I must walk the tree from its outermost leaves,
progressing inwards as I go (i.e. Add sales reps, product categories,
products, etc. first, then I can add sales orders and finally sales
details). Given that there are about 75 tables, I can easily screw the order
up, forgetting to add this or that leaf-table before trying to add its
branch. Is there some way to determine a sequence in which to add them,
which is guaranteed to work? Obviously, there are many possible sequences
which could work: I don't want an exhaustive list -- I will settle for one
sequence that works. But I cannot readily deduce how to generate such a
sequence.
TIA,
Arthur




More information about the AccessD mailing list