"Hongyu Sun" wrote:

> Hi, All:
>
> Please help. I use sql server as back end and Access 2003 as front end
> (everything is DAO).
>
> A table on SQL server has an identity column as the key.
>
> We have trouble on adding records to this table using the following SQL.
>
> strSQL = "INSERT INTO myTableOnSQLServer (A, B, C, D, E) SELECT A, B, C, D,
> E FROM myTableonAccessLocal"
> db.execute strSQL
>
> The schema of the table "myTableOnSQLServer" and the schema of the table
> "myTableonAccessLocal" are all the same except that the "myTableOnSQLServer"
> has an identity column (ID). The key of the "myTableOnSQLServer" is "ID" and
> the table "myTableonAccessLocal" does not have a key.
>
> When we try to run the query, it gives errors indicating the key is violated
> or missing.
>
> Should I figure out the autonumber for it first and then add to the SQL
> server table?
>
> Many thanks,
>
> HS

As a common an identity column generates values by itself. If you need to
insert values into an identity column use this command:

SET IDENTITY_INSERT myTableOnSQLServer ON

After the insert has been completed issue the following statement:

SET IDENTITY_INSERT myTableOnSQLServer OFF

Good luck