0

I am trying to update a linked table in Access 2013. The Table DP is a MySql table which I am using ODBC to access. The Table ND is a local table in the Access database (MDB format). I am doing plenty of other update queries to DP so the actual technique is not an issue. The text field I am updating has a length of 50 but I am checking that in case that is the source of the Overflow. The other field I am updating is just an Integer.

UPDATE DP INNER JOIN ND ON (DP.DOD = ND.DOD) AND (DP.DOB = ND.DOB) AND (DP.Name = ND.Name) 
SET DP.CategoryId = 1, DP.Description = [ND].[Description]
WHERE (((DP.Description)="XYZ") AND ((ND.Description)<>"XYZ") 
AND ((Len([ND].[Description]))<=50));

When I run the query Access just says "Overflow".

What in this query could possibly be causing an overflow?

I should add that DOB and DOD are date fields and some of my dates go back to the first century AD.

Rob Sedgwick
  • 4,342
  • 6
  • 50
  • 87
  • What are the properties of the Description field on both tables? – Newd Feb 19 '15 at 14:41
  • ND - Long Text, DP - Short Text, field size 50 (that's what Access shows in the attached table) DP - VARCHAR(50) in MySql – Rob Sedgwick Feb 19 '15 at 14:48
  • While the error seems fairly straight forward, trying to fit something large into something smaller. I haven't ran into this situation myself, though to I think to possibly explain this what happens if you run a query that gets the length of all records from ND that will be getting updated into DP and see if any exceed 50. – Newd Feb 19 '15 at 14:58
  • Also from my understanding longtext automatically holds enough characters for 65000 characters and I haven't seen it explicitly limit to 50. For reference: https://social.msdn.microsoft.com/Forums/office/en-US/8ce25934-beb4-4468-9cdd-16419ddd3438/a2013-short-text-long-text-rich-text-differences-and-why-you-can-only-see-rich-text-as-choice?forum=accessdev – Newd Feb 19 '15 at 15:02
  • Actually I just noticed that the scroll bar perfectly cuts off the <= 50 part of your query on my screen. – Newd Feb 19 '15 at 15:12
  • I have a feeling it might be down to the dates that I am using in the join. Access doesn't have the date range that MySql has and early dates might be causing the problem. I shall investigate. – Rob Sedgwick Feb 19 '15 at 15:15
  • 1
    You could be right, http://stackoverflow.com/questions/1275208/sql-server-datediff-function-resulted-in-an-overflow is worth a read. Seems like in the comparison of dates SQL does an internal datediff and if there are any dates that differ greater than 68 years you get that error. – Newd Feb 19 '15 at 15:27

0 Answers0