You cannot use upsized append queries in an Access project in Access 2002

Article translations Article translations
Article ID: 294923 - View products that this article applies to.
This article was previously published under Q294923
This article has been archived. It is offered "as is" and will no longer be updated.
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access project (.adp).

For a Microsoft Access 2000 version of this article, see 229681.
Expand all | Collapse all

On This Page

Symptoms

When you try to run an upsized append query in a Microsoft Access project, you may receive the following error message:
Cannot insert explicit value for identity column in table 'table name' when IDENTITY_INSERT is set to OFF.

Cause

When you create a new SQL Server database (including SQL Server 2000 Desktop Engine), the IDENTITY_INSERT options are set to OFF.

If you upsize a Microsoft Access table that contains a field of the AutoNumber data type, the field is created on SQL Server as an IDENTITY column. Unlike the AutoNumber data type, you cannot directly edit IDENTITY columns, nor explicitly insert data into an IDENTITY column while the IDENTITY_INSERT option for that table is set to OFF. To insert or update data in an IDENTITY column, you must set the IDENTITY_INSERT option to ON.

Resolution

You can use the SET IDENTITY_INSERT statement to set the IDENTITY_INSERT option. SET IDENTITY_INSERT always references a table, and you should place it before the UPDATE or INSERT statement that modifies or inserts data into an IDENTITY column. The following example sets IDENTITY_INSERT for the NewEmployees table.
   SET IDENTITY_INSERT NewEmployees ON
				

More information

Steps to Reproduce the Behavior

  1. Open the sample database Northwind.mdb.
  2. In the Database window, click the Employees table, and then click Copy on the Edit menu.
  3. On the Edit menu, click Paste. In the Paste Table As dialog box, type NewEmployees in the Table Name box. Click OK.
  4. Create a new query in Design view, and then close the Show Table dialog box without adding any tables or queries.
  5. On the View menu, click SQL View.
  6. Type the following INSERT INTO statement into the SQL window. This statement will create an append query:
       INSERT INTO 
              NewEmployees (EmployeeID, LastName, FirstName, Title,
              TitleOfCourtesy, BirthDate, HireDate, Address, City, Region,
              PostalCode, Country, HomePhone, Extension, Photo, Notes,
              ReportsTo)
       SELECT 
              Employees.EmployeeID, Employees.LastName, Employees.FirstName,
              Employees.Title, Employees.TitleOfCourtesy, Employees.BirthDate,
              Employees.HireDate, Employees.Address, Employees.City,
              Employees.Region, Employees.PostalCode, Employees.Country,
              Employees.HomePhone, Employees.Extension, Employees.Photo,
              Employees.Notes, Employees.ReportsTo
       FROM 
              Employees;
    					
  7. Save the query as qryAppend, and then close the query.
  8. On the Tools menu, point to Database Utilities, and then click Upsizing Wizard.
  9. Complete the steps in the Upsizing Wizard, and accept the default selections, except as noted below:
    Create New Database: Yes
    Which tables do you want to export to SQL Server: Export all tables
    Add timestamp fields to tables: No, never
    Create a new Access client/server application: Yes
  10. After the Upsizing Wizard is finished, close the upsizing report.
  11. Try to run the qryAppend stored procedure, and note the error message.
  12. To set IDENTITY INSERT to ON, add the following line of SQL to the qryAppend stored procedure directly after the keyword AS:
       SET IDENTITY_INSERT NewEmployees ON
    						
    When you are finished, your stored procedure should resemble the following text:
       ALTER PROCEDURE qryAppend
       AS
       SET IDENTITY_INSERT NewEmployees ON
       INSERT INTO 
              NewEmployees (EmployeeID, LastName, FirstName, Title,
              TitleOfCourtesy, BirthDate, HireDate, Address, City, Region,
              PostalCode, Country, HomePhone, Extension, Photo, Notes,
              ReportsTo)
       SELECT 
              Employees.EmployeeID, Employees.LastName, Employees.FirstName,
              Employees.Title, Employees.TitleOfCourtesy, Employees.BirthDate,
              Employees.HireDate, Employees.Address, Employees.City, 
              Employees.Region, Employees.PostalCode, Employees.Country, 
              Employees.HomePhone, Employees.Extension, Employees.Photo, 
              Employees.Notes, Employees.ReportsTo
       FROM 
              Employees
    					
  13. Save the modified stored procedure, and then run it. Note that it succeeds.

References

For more information about IDENTITY INSERT, refer to SQL Server Books Online. To download the SQL Server Books Online, visit the following Microsoft Web site:
http://technet.microsoft.com/en-us/sqlserver/bb331756.aspx

Properties

Article ID: 294923 - Last Review: November 1, 2013 - Revision: 4.0
Applies to
  • Microsoft Access 2002 Standard Edition
Keywords: 
kbnosurvey kbarchive kbclientserver kberrmsg kbprb KB294923

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com