Access to SQL insert not working:

[alorenzini]

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.

I created a structure in SQL to hold the datalink data and then ran the following code In SQL:

INSERT INTO ClippershipImporttest
( 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;

and it did the insert appropriately. Then I copied the script back into the Access query and recieve the same error. I checked the field size and they all seem to match up between the access and the SQL. I am really at loss.
I hit the help button on the error message and this is what it returned:


Numeric field overflow. (Error 3349)

The data in a Btrieve field is too large to represent in your application.

I then took a step back and just tried just inserting the first field into the SQL table and received this error:

MS Access set 0 field(s) to Null due to a type conversion failure, and it didn't add 2793 record(s) to the table due to a key violations, 0 record(s) due to lock violations, and 0 record(s) due to validation rule violations.

This is the Access query:
INSERT INTO dbo_ClippershipImporttest ( TRACK_NO )
SELECT Datalink.TRACK_NO
FROM Datalink;


I even removed the Primary Key from the SQL table to see if that was the issue and it wasn't? I checked the size for the TRACK_NO field in both Access and SQL and the ACCESS defines it as TEXT 255 and SQL defines it as NVARCHAR(255). Any ideas?

 

[boblarson]

Did you, when you linked the tables, tell Access which field was the primary key? If not, or if you accidentally specified too many fields when the dialog box comes up to ask you to identify the PK field, then you should delete your table link and relink and this time make sure to select the right column as PK.

That's the only reason I can think of.

 

[alorenzini]

I found the issue, one of the fields coming from the Datalink file was a text and the SQL table had the column defined as a float. thanks.

 

[alorenzini]

But now I came up with the issue that my linked SQL table has 257 columns in it, and i can only see the first 255 in Access. The last column is needed for an Update query but I can't see it. Any ideas?

 

[tehNellie]

I believe Access has a 255 column limit.

Create a stored procedure to update your table and call that from your Access code.

if you need to retrieve information either create a view or preferably a stored procedure to retrieve up to 255 columns of data that you need to see and use the view/sp in Access rather than the table.