IDENTITY INSERT

[Des Norton]

Hi NG

My apologies if this is not the correct group for my question. Please point
me to the correct group if necessary.

I need to port data from Access2000 to SQL2000.

I have created queries in Access, that displays the data in the correct
format to match the destination tables in SQL. I have created linked tables
to SQL.

My problem is that I would like to keep the PrimaryKey values from the
Access.
In SQL, I would use
SET IDENTITY_INSERT tablename ON
INSERT tablename (ID, Field1, ..) values (1, 'data', ..)
INSERT tablename (ID, Field1, ..) values (5, 'data', ..)
INSERT tablename (ID, Field1, ..) values (2, 'data', ..)
SET IDENTITY_INSERT tablename OFF

However, I am unable to find a method of doing this from within Access.


Any help would be greatly appreciated

Regards
Des Norton

 

[Guest]

If you wont get any other answer, and its only a one time go, then you can
export the tables from Access to sql and excute the query in your sql server,
instead of doing it in Access.

 

[Des Norton]

Ofer

Thanks for your reply.

Although this will be a one-time data port, I will not have any control of
the port.

I need to come up with a pre-written solution for us "Non-Technical" user to
implement - << Open the file and Press the button >>

They do not have a DBO, but the user does have Access200 installed, so I kow
they can open the file.


At this point I am thinking of creating a new Access DB with code that
dynamically imports all the tables into itself, adds an extra column for the
new ID filed (from SQL), inserts all the data and uses the new ID field for
linking as it goes along.