Derek,

There are a couple of ways to do this.
First, I would build the tables in SQL(Tables1) with
Identity and a PK on that value, if that is what you want.

I would then create yet another set of those same tables
(Tables2), without Identity and PK values.

Import the Access tables to the second set (Tables2) in
DTS.

Then put together a script in the DTS job that will load
Tables2 to Tables1 but turning off the Identity Insert
Option when loading but turn it back on when done. This
way, you can keep the key values and keep your
referential integrety. When you start inserting into SQL
via normal operations after your initial load, the
identity functionality will work fine.

The reason, I created a second set was the purpose of
being able to turn on and off the SET IDENTITY
functionality. Please review the SET IDENTITY in books on
line.
It should work something like this:

SET IDENTITY_INSERT MyProductinTable OFF
Insert Into MyProductinTable
Select * from MyTEMPTable
SET IDENTITY_INSERT MyProductinTable ON

I may have the OFF ON statements backwards but I think
you will get the picture.

Also, make sure you load the table in the correct order
for referential integrety.

Hope this helps. There are other ways of doing this too
but this is a start and should work - Ive done it many
times.

MG,
From what you are saying is to not worry about the old values, just
use identity afterward.
I started to think about this some more. I was hoping that there was
an easy way to bring the data accross and then resort the PK after the
relational integrity is setup and then set the identity.
Thanks, I will think about this some more.
Derek

"MG" <megordontn (AT) sprintmail (DOT) com> wrote

Derek,
Almost, I was trying to let you keep your values as the
Identity values as well as the primary keys. The trick I
used will do that. As I shared, I have done this for years
and it saves the original values as the keys and identity
as well as still provides you/users with the same values
that you/they saw before. As per your last sentence, you
could do something like that by simply making antoher field
that holds your old values as an attribute/reference that
does not really tie to anything - then let SQL build the
records with a new Identity field with a PK - just make
sure the data is sorted in the order before the load.
Hope this helps.