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

• Microsoft Access version 7.0

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

When you try to insert a new record into a linked (attached) SQL Server table, you may receive the following error message:

   Numeric Value Out of Range

RESOLUTION

Install the Microsoft Access for Windows 95 version 7.0a release.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access version 7.0. This problem no longer occurs in Microsoft Access version 7.0a.

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

REFERENCES

For more information about pass-through queries, search for "Pass-Through Queries", and then "creating" using the Microsoft Access 7.0 Help Index.

For more information about Microsoft Access Version 7.0a, please see the following article in the Microsoft Knowledge Base: