Autonumber + MSDE

[vbgladiator]

Hey,

I have to take a record from an Access table and insert it into a MSDE table. The catch is that I have to preserve the AutoNumber field's value. If I upsize it from Access, it does it. However, I need to create a conversion program as this will be sent out to customers.

Thanks,

[rlwhealdon]

Are you writting the conversion app?

The SQL Syntax for this is:

SET IDENTITY_INSERT <tablename> ON
    --Your Insert SQL Query Goes Here.
    --The below is one possible example.
    INSERT INTO <tablename> (<field1>, <field2>, ...)
    VALUES (<value1>, <value2>, ...)
SET IDENTITY_INSERT <tablename> OFF

Note that IDENTITY_INSERT can be active for only one table at a time.

It can be issued as a separate statement (meaning that you don't have to immediately run a query after turning it on). It remains active until turned off. Make sure that you always turn it off when done.

[vbgladiator]

Thanks you so much.