[AccessD] Question about ANPKs

Yes you can do it. This is how some people add a 0th record.

As for the import, if you have access (no pun intended) to SQL server, you
could always crate a DTS import package then when finished with
normalization, export everything into Access (or import from SQL into Access
if you prefer.)

By using SQL, you can use temp tables and table variables to help out with
some of the tasks. I like to import all tables as is(in their flat state)
from  Access, then add a new ANPK column even if it already has a PKID.
When joining tables to get ParentIDs, join on as many fields as possible but
include this new ANPK for your "relating". Sometimes you have to add
additional INT columns to serve as links between child tables that are
broken apart into many tables. Once you have everything mapped, you can move
that data into your final normalized table structure.

HTH
David McAfee

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Arthur Fuller
Sent: Thursday, October 06, 2005 8:59 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Question about ANPKs


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

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