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

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.

Back to the top