Error-You cannot record your changes because a value you entered violates the setings defined for this table.

Any info on what field caused the error?
You need to identify the incorrect value(s) in the table...
Then create a system to prevent these invalid values from being entered in the first place.

Or, on the other end, import the table to a temp "staging" table, then create a process to scan this temp table for violations (then fix them).

Do you have access to the field definitions in SQL to see what all the constraints are on each field?

JeffCoachman

Check if the fields in the table have integrity (Field > x or Field < Y)checks and edit your data accordingly.

I have no info on what field it caused the error.As i tried using 200 rows,,it uploaded successfully.

I have access to the field definitions in SQL AND there are no constrainst on sql besided the one for the auto generated field ,Attached is the excel.Here's my sql definitions--


CREATE TABLE [dbo].[Analysis Report](
      [Report Date] [datetime2](0) NULL,
      [MERS MIN] [nvarchar](255) NULL,
      [Primary Borrower Name] [nvarchar](255) NULL,
      [Property City-State] [nvarchar](255) NULL,
      [Original Balance] [nvarchar](50) NULL,
      [Note Date] [datetime2](0) NULL,
      [Pool Number] [nvarchar](255) NULL,
      [Inv Loan Number] [nvarchar](255) NULL,
      [FHA/VA/MI Nbr] [nvarchar](255) NULL,
      [MIN Status Code Alphanumeric 2] [nvarchar](50) NULL,
      [MIN Status] [nvarchar](255) NULL,
      [Foreclosure Status Code] [nvarchar](50) NULL,
      [Foreclosure Status] [nvarchar](255) NULL,
      [Registration Date] [datetime2](0) NULL,
      [Deactivation Date] [datetime2](0) NULL,
      [MOM Indicator] [nvarchar](255) NULL,
      [Lien Type] [nvarchar](50) NULL,
      [Lien Type Description] [nvarchar](255) NULL,
      [County Name] [nvarchar](255) NULL,
      [Servicer Org ID] [nvarchar](50) NULL,
      [Servicer Name] [nvarchar](255) NULL,
      [Subservicer Org ID] [nvarchar](255) NULL,
      [Subservicer Name] [nvarchar](255) NULL,
      [Investor Org ID] [nvarchar](50) NULL,
      [Investor Name] [nvarchar](255) NULL,
      [Interim Funder Org ID] [nvarchar](255) NULL,
      [Interim Funder Name] [nvarchar](255) NULL,
      [Custodian Org ID] [nvarchar](255) NULL,
      [Custodian Name] [nvarchar](255) NULL,
      [Report Recipient Org ID] [nvarchar](50) NULL,
      [Report Recipient Name] [nvarchar](255) NULL,
      [Requestors LOB] [nvarchar](255) NULL,
      [Group by Org ID] [nvarchar](50) NULL,
      [Group by Name] [nvarchar](255) NULL,
      [Group by LOB] [nvarchar](255) NULL,
      [Agency ID] [nvarchar](255) NULL,
      [Note Date From] [nvarchar](255) NULL,
      [Note Date Through] [nvarchar](255) NULL,
      [Registration Date From] [nvarchar](255) NULL,
      [Registration Date Through] [nvarchar](255) NULL,
      [Borrower SSN] [nvarchar](255) NULL,
      [Property Street] [nvarchar](255) NULL,
      [Property Unit] [nvarchar](255) NULL,
      [Property Zip] [nvarchar](50) NULL,
      [QC Indicator] [nvarchar](255) NULL,
      [ID] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [MERS Portfolio Analysis Report$PrimaryKey] PRIMARY KEY CLUSTERED
(
      [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
MERSPA.xls

>>As i tried using 200 rows,,it uploaded successfully<<  

that just means that those 200 specific rows did not have the error condition that you are encountering in other rows in the full set of data.

@Arthur, I didn't have errors on those rows.I tried using import/export in sql server from excel to the sql table,it did load all the 35,000 rows successfully but i dont understand why I am not able to load it in access table which are linked to sql from excel.

The only way I was able to import to linked table without any error is converting all spreadsheets fields type to Text.What I figure out is,some of the fields have numbers for few rows and later it takes some charater or alphanumeric,then it violates with this error,even though datatype is nvarchar in sql.Isn't this weird??

Fields that are empty asre being imported as blanks (which are seen as characters)  . Generally, you should import into a 'holding  table", verify the data, then import into the final destination.

AW

it's just the access issues

I figured it out