question on inserting a record on sql server with identity column as key

13 Jan 2006 5:55 AM

Hongyu Sun

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

13 Jan 2006 7:31 AM

Jonathan Chong

There are two options depending your answer to this question:
Do you want you myTableOnSQLServer table to have the same value of ID from
myTableonAccessLocal.
1. If NO. Then don't specify the ID column in your INSERT INTO statement.
2. If YES. Use SET IDENTITY_INSERT command to allows explicit values to be
inserted into the identity column of a table. Like this:

SET IDENTITY_INSERT myTableOnSQLServer ON
insert into .....
SET IDENTITY_INSERT myTableOnSQLServer OFF

Hope it helps.

Show quote

"Hongyu Sun" <s**@cae.wisc.edu> wrote in message
news:dq7fe7$f6b$1@news.doit.wisc.edu...
> 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
>
>