Microsoft KB Archive/153151

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

Article ID: 153151

Article Last Modified on 7/5/2002

-

APPLIES TO


 * Microsoft Access 95 Standard Edition

-



This article was previously published under Q153151



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



SYMPTOMS
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



STATUS
Microsoft has confirmed this to be a problem in Microsoft Access version 7.0.



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
 Open the sample database Northwind.mdb. On the File Menu, point to Get External Data, and then click Link Tables. From the Files Of Type list, click ODBC Databases, select the SQL Server Data Source, and then click OK. Click Options, select (or type) Pubs in the Database box, and click OK. Select the Jobs table from the list, and then click OK. 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.</li> Click Append on the Query menu, select Jobs from the Table Name list, and then click OK.</li>  Add the following values to the query grid:

<pre class="fixed_text">        Field:  Expr1:999   Expr2: "New Job"   Expr3: 25   Expr4: 100 Append To:    job_id           job_desc     min_lvl      max_lvl </li> Close and save the query as Add_New_Job.</li> 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.</li> On the Query menu, point to SQL Specific, and then click Pass-Through.</li>  Enter the following into the SQL Pass-Through Query window:

<pre class="fixed_text">      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. </li> 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.</li> Close this pass-through query (you don't have to save it) and double- click the Add_New_Job query.</li> Open the linked table (Jobs) in Datasheet view.</li> Try to insert a new record into the table. Note that you receive the following error:

Numeric Value Out of Range

</li></ol>

<div class="references_section">