I am trying to figure out if this is possible. I need to transfer data
from SQL server(from a web application) into an Access database multiple
times, both the SQL server database and Access will have data inputed
into them at various times. The Access database has an autonumber field
as a primary key. I know that I can insert data using DTS by enabling
identity insert. The problem is that the Access database will get more
data into it manually after my transfer and if say the last id that I
inserted was 1000, then the next record in Access will be 1001, but in
SQL Server if someone makes an insert it will also have a record 1001. I
could insert the data from SQL server without the primary key field but
there are related child tables. I need to know if there is a way for me
to get the new autonumber created in Access as I insert records from SQL
Server so I can also import the related child records.

Thanks.

The only way that I know to do it is to run the query:

select Max(AutonumField) from MyTable

You could use a Dynamic Properties task to put this value into a global variable (using a Query).

Russ

This sounds like classic collisions which are dealt with by Merge
Replication. Can you not range your data so SQL Server's values start at
say 100,000,000 and increase by 1 but Access starts at the beginning i.e. 1
and increase by 1

You could also have slight variation on your table structure. I use this in
a warehouse

PKField
SourceSystemPKVal
SourceSystem


This way when I insert new rows into the destination I add some extra
details. I can then match the children to the SourceSystemPKVal and return
the New PK from the destination.



--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Roberto Lopez" <lopez.family (AT) earthlink (DOT) net> wrote