ACC95: Numeric Value Out of Range Error Inserting into SQL Server

MORE INFORMATION

When you use Microsoft Access version 7.0 to link to an SQL Server 6.0 table which contains an SQL Identity column, you will receive a "Numeric Value Out of Range" error if you try to insert a value greater than 999.

Steps to Reproduce Problem

1. Open the sample database Northwind.mdb.
2. On the File Menu, point to Get External Data, and then click Link Tables.
3. From the Files Of Type list, click ODBC Databases(), select the SQL Server Data Source, and then click OK.
4. Click Options, select (or type) Pubs in the Database box, and click OK.
5. Select the Jobs table from the list, and then click OK.
6. Open a new query in Design View, but do not add a table. Simply click the Close button when the Show Table dialog box appears.
7. Click Append on the Query menu, select Jobs from the Table Name list, and then click OK.
8. Add the following values to the query grid:
   

            Field:  Expr1:999   Expr2: "New Job"   Expr3: 25   Expr4: 100
        Append To:     job_id           job_desc     min_lvl      max_lvl
   						

9. Close and save the query as Add_New_Job.
10. Open a new query in Design View, but do not add a table. Simply click the Close button when the Show Table dialog box appears.
11. On the Query menu, point to SQL Specific, and then click Pass-Through.
12. Enter the following into the SQL Pass-Through Query window:
    

           Set Identity_Insert dbo.Jobs ON
    						

    
    Note: In order to insert or append a value to an Identity column within SQL Server version 6.0 and 6.5, you must first set the IDENTITY_INSERT property for the desired table to ON before running an append query. This will ensure that the Pass-Through query is using the same connection information as the linked table.
13. On the Query menu, click Run. Be sure to use the same Data Source and Database when prompted.
    
    NOTE: To avoid ODBC errors, you must be the Database Owner (DBO) or object owner (owner of the table) when running this pass-through query.
14. Close this pass-through query (you don't have to save it) and double- click the Add_New_Job query.
15. Open the linked table (Jobs) in Datasheet view.
16. Try to insert a new record into the table. Note that you receive the following error:
    
    Numeric Value Out of Range