March 5, 2008 at 4:14 pm
I am using Access as the frontend and I need to insert records into a SQL table with an identity column. I keep getting the following error:
You cannot record your changes because a value you entered violates the settings defined for this table or list ( for example , a value less than the minimum or greater than the maximum). Correct this error and try again.
Below please find my append query:
INSERT INTO dbo_ClippershipImport ( TRACK_NO, PKGID, STATUS, CARRIER, ACT_WGT, SHP_WGT, SHP_CHG, TOT_CHG, OACT_WGT, OSHP_WGT, OSHP_CHG, OTOT_CHG, ORD_CNT, EXP_CNT, COD_FLAG, PAY_FLAG, DIM_LENGTH, DIM_WIDTH, DIM_HEIGHT, DIM_FACTOR, ACCOUNT, INTCODE, [DATE], [TIME], TRANS, [ZONE], DEPT, CARNAME, PHONE, CUST, SHIPTO1, SHIPTO2, SHIPTO3, SHIPTO4, SHIPTO5, SHIPTO6, SHIPTO7, ZIP, COUNTRY, PAY_ACCT, BILLTO1, BILLTO2, BILLTO3, BILLTO4, BILLTO5, BILLTO6, BILLTO7, ARRIVE, ROUTE, USERNAME, FSC_CHG, OFSC_CHG, DIM_WGT, RECORDID, DDP_FLAG, DDP_CHG, REF1_CHG, REF2_CHG, SS_FLAGS, PRE_CHG, OPRE_CHG )
SELECT Datalink.TRACK_NO, Datalink.PKGID, Datalink.STATUS, Datalink.CARRIER, Datalink.ACT_WGT, Datalink.SHP_WGT, Datalink.SHP_CHG, Datalink.TOT_CHG, Datalink.OACT_WGT, Datalink.OSHP_WGT, Datalink.OSHP_CHG, Datalink.OTOT_CHG, Datalink.ORD_CNT, Datalink.EXP_CNT, Datalink.COD_FLAG, Datalink.PAY_FLAG, Datalink.DIM_LENGTH, Datalink.DIM_WIDTH, Datalink.DIM_HEIGHT, Datalink.DIM_FACTOR, Datalink.ACCOUNT, Datalink.INTCODE, Datalink.DATE, Datalink.TIME, Datalink.TRANS, Datalink.ZONE, Datalink.DEPT, Datalink.CARNAME, Datalink.PHONE, Datalink.CUST, Datalink.SHIPTO1, Datalink.SHIPTO2, Datalink.SHIPTO3, Datalink.SHIPTO4, Datalink.SHIPTO5, Datalink.SHIPTO6, Datalink.SHIPTO7, Datalink.ZIP, Datalink.COUNTRY, Datalink.PAY_ACCT, Datalink.BILLTO1, Datalink.BILLTO2, Datalink.BILLTO3, Datalink.BILLTO4, Datalink.BILLTO5, Datalink.BILLTO6, Datalink.BILLTO7, Datalink.ARRIVE, Datalink.ROUTE, Datalink.USERNAME, Datalink.FSC_CHG, Datalink.OFSC_CHG, Datalink.DIM_WGT, Datalink.RECORDID, Datalink.DDP_FLAG, Datalink.DDP_CHG, Datalink.REF1_CHG, Datalink.REF2_CHG, Datalink.SS_FLAGS, DataLink.PRE_CHG, datalink.OPRE_CHG
FROM Datalink;
The ClippershipImport is the SQL table. It has a identity column on called CliipershipImportID which is also the primary key. There is also a recorddate column that has a default of GetDate() and a INV_FEDEX column which has a default value of 0. These are only located in the SQL table and not the DataLink file that is being imported.
Any ideas?
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
April 13, 2008 at 11:25 pm
Hi,
This may be a little too late but it sounds like a data type issue. The data types in Access don't match exactly to those in SQL Server. Check that the data you are trying to insert will fit in the data types for each field.
Cheers,
Nicole
Nicole Bowman
Nothing is forever.
April 13, 2008 at 11:57 pm
Hi
The error you mentioned does not look like a typical SQL Server error. Is the error an application error that is not displaying the actual error issued from the Server?
The error mentions a value that is not within a range, is there a value that is supposed to only range between 0 and 100 or something similar?
I would try to run the insert statement on the SQL Server with the first record of values from the Access DB and see if the insert will actually work and elimenate any major data / key constraints.
Let us know how it goes
Thanks
Kevin
April 14, 2008 at 6:57 am
I figured out that that one of the columns was typed wrong. After I corrct that everything started to work. Thanks.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply