Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. ACC2000: Cannot Use Upsized Append and Make-Table Queries in an Access Project

ACC2000: Cannot Use Upsized Append and Make-Table Queries in an Access Project

View products that this article applies to.

This article was previously published under Q229681
Advanced: Requires expert coding, interoperability, and multiuser skills.

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

For a Microsoft Access 2002 version of this article, see 294923 (http://support.microsoft.com/kb/294923/EN-US/).

↑ Back to the top

Symptoms

When you try to run upsized make-table or append queries in a Microsoft Access project, you may receive the following error message:
Cannot run SELECT INTO in this database. The database owner must run sp_dboption to enable this option.
-or-
Cannot insert explicit value for identity column in table 'table name' when IDENTITY_INSERT is set to OFF.
You may also encounter the first error message when running an upsized update query.

↑ Back to the top

Cause

When you create a new Microsoft Data Engine (MSDE) or SQL Server database, the select into/bulkcopy and IDENTITY_INSERT options are set to OFF.

Upsized Make-Table Queries

When you upsize a database that contains make-table queries, the queries migrate as stored procedures. These stored procedures use the SELECT INTO statement to create a new table and then add records to it. You must set the select into/bulkcopy option of the database to ON in any database that contains a stored procedure that uses the SELECT INTO statement.

Upsized Update and Append Queries

If you upsize a Microsoft Access table that contains a field of the AutoNumber data type, the field will be created on MSDE or 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.

↑ Back to the top

Resolution

To set the select into/bulk copy option to ON for your database, create a stored procedure that uses the sp_dboption system stored procedure to toggle the setting. The following SQL sample modifies a database named MyDatabase so you can use SELECT INTO statements: 
   CREATE PROCEDURE SetMyOptions
   AS
   EXEC sp_dboption 'MyDatabase','bulkcopy','ON'
You can use the SET IDENTITY_INSERT statement to set the IDENTITY_INSERT option. SET IDENTITY_INSERT always references a table, and should be placed 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

↑ Back to the top

More information

When you try to upsize to SQL Server 2000 from Access 2000 using the Upsizing Wizard you receive an "Overflow" error message. For additional information about this issue, click the article number below to view the article in the Microsoft Knowledge Base:
272384 ACC2000: "Overflow" Error Message When You Try to Upsize to SQL Server 2000

Steps to Reproduce Behavior

1.Open the sample database Northwind.mdb.
2.Create a new query in Design view, and close the Show Table dialog box without adding any tables or queries.
3.On the View menu, click SQL View.
4.Type the following SELECT statement into the SQL window. It will create a make-table query:
   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
   INTO 
          NewEmployees
   FROM 
          Employees;
5.Save the query as qryMakeTable, and then close it.
6.Create a second query in Design view, and close the Show Table dialog box without adding any tables or queries.
7.On the View menu, click SQL View.
8.Type the following INSERT INTO statement into the SQL window. It 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;
9.Save the query as qryAppend, and then close it.
10.On the Tools menu, point to Database Utilities, and then click Upsizing Wizard.
11.Complete the steps in the Upsizing Wizard, using default selections, except where 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
12.Once the upsizing tool has completed its work, close the upsizing report.
13.Try to run the qryMakeTable stored procedure, and note the error message.
14.To set the select into/bulkcopy option to ON, create a stored procedure using the following SQL:
   CREATE PROCEDURE SetMyOptions
   AS
   EXEC sp_dboption 'NorthwindSQL','bulkcopy','ON'
15.Save and run the stored procedure SetMyOptions.
16.Run the stored procedure qryMakeTable again. Note that this time it succeeds.
17.Try to run the qryAppend stored procedure and note the error message.
18.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 complete, 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
19.Save the modified stored procedure, and then run it. Note that it succeeds.

↑ Back to the top

References

For more information about sp_dboption and IDENTITY INSERT, refer to SQL Server Books Online. To download the SQL Server Books Online, visit the following Microsoft Web site:
http://download.microsoft.com/download/SQL70/File/2/Win98/En-US/SQLBOL.exe

↑ Back to the top

Keywords: KB229681, kbclientserver, kbprb, kbdownload

↑ Back to the top

Article Info
Article ID : 229681
Revision : 3
Created on : 8/9/2004
Published on : 8/9/2004
Exists online : False
Views : 373