Category Archives: MS Access

MS Access

Msg 8152, Level 16, State 10 String or binary data would be truncated. The statement has been terminated.

It appears that when you want to “insert into” data from an nvarchar(max) field to another nvarchar(max) field, and the table has a lot of data the ms sql query is terminated giving you the error

Msg 8152, Level 16, State 10 String or binary data would be truncated The statement has been terminated.

After narrowing down data fields and understanding which field is the problematic one, and given that the source field is nvarchar(max) and so is the destination, you need to go on troubleshooting this.

Suppose I have the following query:

INSERT INTO ServiceJournal

(PriorityID, TechnicianID, ServiceTaskID, ClientID, ServiceDate, ProblemReportDateTime, EventTypeid, ReminderID, CustomerSiteID, DetailedProblemDescription)

SELECT        3 AS Expr1, Customers.EmployeeResponsibleID, 16 AS Expr2, Customers.CustomerID, GETDATE() AS Expr3, GETDATE() AS Expr4, 2 AS Expr5, 2 AS Expr6, 1 AS Expr7, Customer_Tasks.TaskDescription

FROM            Customer_Tasks INNER JOIN

Customers ON Customer_Tasks.CustomerID = Customers.CustomerID

WHERE        (Customer_Tasks.IsMaintenance = 1)

The field DetailedProblemDescription is the one producing the problem.

I tried creating a new field on the destination table ServiceJournal, called test as nvarchar(max) and changed my query to

INSERT INTO ServiceJournal

(PriorityID, TechnicianID, ServiceTaskID, ClientID, ServiceDate, ProblemReportDateTime, EventTypeid, ReminderID, CustomerSiteID, test)

SELECT        3 AS Expr1, Customers.EmployeeResponsibleID, 16 AS Expr2, Customers.CustomerID, GETDATE() AS Expr3, GETDATE() AS Expr4, 2 AS Expr5, 2 AS Expr6, 1 AS Expr7, Customer_Tasks.TaskDescription

FROM            Customer_Tasks INNER JOIN

Customers ON Customer_Tasks.CustomerID = Customers.CustomerID

WHERE        (Customer_Tasks.IsMaintenance = 1)

The script inserts a number of rows, as it should!

I tried to rename the problematic field to DetailedProblemDescription2 and retry. It does not work.

I backed up the field data to another field and deleted the field. This is where I started losing the table integrity. I had to take the data with an export script, drop and recreate the table (taken from a backup) and restore the data.

Need to mention that concatenating the DetailedProblemDescription field with cast (DetailedProblemDescription as nvarchar(4000)) or other data type, did not work as well.

To make a long story short, I overcame the insert into problem by putting

SET ANSI_WARNINGS  OFF;

 

SET ANSI_WARNINGS  ON;

As long as the “problematic” field characters don’t go over 4000 characters, no problem occurs. I will need to test it with more in the next months.

Have no time to investigate this further, but worth’s writing it down and sharing:)

Till next time!

Scaling of decimal value resulted in data truncation.

This may appear when linking SQL tables in MS Access through ODBC.

Although Microsoft recommends connecting to tables through code, ODBC has always been a fast way to do it.

First of all don’t be upset, no truncation is actually taking place on your data backend, Access is just unable to display the numeric value’s, due to a change you did on the SQL table. Don’t be that sure that you did no change…since this may come from a query/view involving more than one tables. It needs at least one problematic field and the message appears.

I got a good workaround of this, without removing tables and relinking in Access which actually did not work for me. The idea is too find the problematic numeric field. My case was a change of a decimal (18,2) field simply to decimal (18,5). I found the view that was the control source of the form (linked in Access), copied aside the views code and removed the decimal fields that had the problem. Went back on Access and refreshed this problematic view only. Went back again on the Sql view and pasted the code as it was originally copied, NO CHANGE!!! Go back in Access and refresh this particular link and your problem is gone.

Let me go home now, or I will be a divorced poor IT guy…

Creativepeople.gr

Microsoft Access 2010 has stopped working

Had this problem since we upgraded to Office 2010 from the 2007 version.
Our access application has a frontend of 20MB, and a backend on an SQL2005 (now trying to pass on 2008).
I solved my problem after reading a lot…
I opened up my Access application by pressing the Shift button, so I bypassed all startup forms and logins.
Opened up a module, not a particular one!
Went on Debug>Compile <ProjectName> and voila…problematic old code started firing up error messages.
After I removed all the unnecessary parts of the code and changed with new refs/code the needed ones, I closed and reopened.
The database opened like a charm. I compacted and repair since this process made my db frontend went on 90MB and started working again with no problem.
Hope it works for you as well.
Have a good day 🙂20/10/2011 Update

The problem continues…

A quick resolution to this is:

“C:\Program Files\Microsoft Office\Office14\MSACCESS.EXE” “C:\MyAccess.accdb” /decompile

where MyAccess is the name of your Database. The Database opens!

The solution above (posted from another user in

http://social.msdn.microsoft.com/Forums/en/isvvba/thread/99a68e98-d9b7-448f-bc94-d06e994e9ec3 )

, regarding this decomplilation solution, is also posted by Microsoft on September 30,2011 on

http://support.microsoft.com/default.aspx?scid=kb;EN-US;2625046

However, till the time the issue with the Faulting module vbe7.dll is resolved, we have to figure out a way to to manage our access dbs. Since no matter what you do, even if you just make a new form the problem still comes back.

When you access db frontends are in production we need a SOLUTION asap!

PS. Have pending 12 Modules and modifications that I need to make in my access frontend and I cannot…
😦

12/1/2012 Update

Microsoft has released an Office 2010 patch on 12/12/2011 which is the ultimate solution to this problem.  This patch worked great for me (even repairing the previous corrupted databases without the need to decompile).

The kbs you may refer for this are:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;2625046

and
http://support.microsoft.com/kb/2596585

The patch (both 32 and 64 bit) can be found at:

http://support.microsoft.com/kb/2553385


One more issue down….plenty to go 🙂

Creativepeople.gr

%d bloggers like this: