INF: Appending Data from Access Table to SQL Table

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 '<table name>' when IDENTITY_INSERT is set to OFF

1. Create a new SQL Passthrough query window. In this window, issue the statement SET IDENTITY_INSERT <table name> 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.