help on insert a record on sql server with identity column as key

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"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 "myTableOnSQLServe
r"
> has an identity column (ID). The key of the "myTableOnSQLServer" is "ID" a
nd
> the table "myTableonAccessLocal" does not have a key.
> When we try to run the query, it gives errors indicating the key is violat
ed
> 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 lucksql