Error-You cannot record your changes because a value you entered violates the setings defined for this table.
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
Microsoft DevelopmentMicrosoft AccessMicrosoft SQL Server
Last Comment
Josh2442
8/22/2022 - Mon
Jeffrey Coachman
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
Ephraim Wangoya
Check if the fields in the table have integrity (Field > x or Field < Y)checks and edit your data accordingly.
Josh2442
ASKER
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--
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Arthur_Wood
>>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.
Josh2442
ASKER
@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.
Josh2442
ASKER
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??
Unlimited question asking, solutions, articles and more.
Arthur_Wood
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.
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
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