question on inserting 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,

HSHongyu Sun (sun@.cae.wisc.edu) writes:
> 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.

Could you please post the exact error message?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx