Solved

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

Posted on 2013-01-04
10
3,573 Views
Last Modified: 2013-01-13
I have a access tables which are linked to SQL.When I tried to import from excel to that table,it gives me this error --Error-You cannot record your changes because a value you entered violates the setings defined for this table.(for example, a value is less than the minimum or greater than the maximum)
I have about 35000 rows and I tried using 200 rows,and I didnt have a problem,it loads successfully.But if I import the whole file,it gave me an error.

I have other table too which does the same process and it could successfuly load about 55000 records but I dont know what I am missing in this table.Any help will be greatly appreciated.Thanks
0
Comment
Question by:Josh2442
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38744302
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
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 38744327
Check if the fields in the table have integrity (Field > x or Field < Y)checks and edit your data accordingly.
0
 

Author Comment

by:Josh2442
ID: 38744330
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
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 38744840
>>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.
0
 

Author Comment

by:Josh2442
ID: 38744886
@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.
0
 

Author Comment

by:Josh2442
ID: 38748954
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??
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 38749362
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
0
 

Accepted Solution

by:
Josh2442 earned 0 total points
ID: 38751383
@arthur-I realized what access does was access looks at first 20 rows,if it sees the number as a datatype and later it sees some alphanumeric,it won't load that rows even the datatype is text in access.If there is alphanumeric in first few rows, it will load the whole file without error.I tested creating one test table in access and try to load the same file into that table ,not the linked one,it loads the whole file but also created another table called import$error  where it shows the rows that were not loaded.
0
 

Author Comment

by:Josh2442
ID: 38756634
it's just the access issues
0
 

Author Closing Comment

by:Josh2442
ID: 38771621
I figured it out
1

Featured Post

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question