Microsoft KB Archive/152035

{|
 * width="100%"|

INF: Appending Data from Access Table to SQL Table

 * }

Q152035

-

The information in this article applies to:


 * Microsoft SQL Server version 6.0

-

SUMMARY
When you insert or append data from Microsoft Access to a Microsoft SQL Server table containing an identity column, you must ensure that the IDENTITY_INSERT option is set to ON in order to maintain the existing values for the corresponding Access column.

MORE INFORMATION
SQL Server automatically generates values for an identity column when a row is inserted, and there is no need to specify a value for that column. You can override this behavior on a per connection basis by setting the IDENTITY_INSERT property to ON.

Applications attempting to insert a specific value into an identity column will stop with the message:

Attempting to insert explicit value for identity column in table ' ' when IDENTITY_INSERT is set to OFF

To insert an explicit value into the identity column, follow these steps:


 * 1) Create a new SQL Passthrough query window. In this window, issue the statement SET IDENTITY_INSERT ON. The table name is the SQL Server table name, not the name that Access has given it if you have attached to that table.
 * 2) Verify that the query runs successfully. If you have the "Returns rows" option set to true, you will get a confirmation that the query ran and did not return any rows.
 * 3) Minimize that query window to maintain the connection to SQL Server. Access will use this connection for the subsequent Append query.
 * 4) Create an Append query in Access. When you run the query, SQL Server will allow Access to specify specific values to be inserted into the identity column.
 * 5) Close the connections to SQL Server. Because the connection is closed, the IDENTITY_INSERT property will no longer be set.

Additional query words: counter field 544 ODBC

Keywords :

Issue type :

Technology : kbSQLServSearch kbAudDeveloper kbSQLServ600