CODEWITHSUNDEEP
ms-access
Totty.js
Totty.js

Reputation: 15831

I can't use UPDATE in MS Access but I can add rows to a database

The error I'm getting is that the row wasn't added because of protection violation reasons. The error is in Portuguese so is not exactly what is wrote there. Any help? thanks

Upvotes: 0

Views: 91

Answers (4)

Brett Rossier
Brett Rossier

Reputation: 3482

Try adding a timestamp field to the source database table. Can't tell you how many times an update issue on a linked table has been fixed that way for me. Also check that your source table has adequate keys set up to uniquely identify records.

Upvotes: 0

BIBD
BIBD

Reputation: 15404

It sounds like you are having a problem with a type mis-match. The screen shot of the error (or the error text) would still be helpful; however this is probably what you want:

UPDATE table1 SET quantity=8 , last_Sent_Date=#16-05-2011# WHERE ID = '18';

or even better, put it in YYYY-MM-DD format (instead of DD-MM-YYYY - which could give you the wrong results for days of the month <=12)

UPDATE table1 SET quantity=8 , last_Sent_Date=#2011-05-16# WHERE ID = '18';

This way is impossible for MS Access to misinterpret the date. The # marks indicated that it's a date-constant.

Upvotes: 0

HansUp
HansUp

Reputation: 97131

When updating more than one field in an UPDATE statement, use a comma (instead of AND) between the field expressions.

UPDATE table1 SET quantity=8, last_Sent_Date='16-05-2011' WHERE ID = '18';

That example assumes the field type for quantity is numeric, last_Sent_Date is text, and ID is text. Use different delimiters if those assumptions are wrong.

Upvotes: 3

Sjuul Janssen
Sjuul Janssen

Reputation: 1812

last_Sent_Date='16-05-2011'
WHERE ID = '18'

Are you sure you're storing your dates in text format? I would expect it to be a date field. And are you sure you're storing your ID's in text format? I would expect it to be a number field.

For AS400 date functions: http://www.broculos.net/tutorials/as_400_chapter_4_sql/20071026/en (scroll down)

I would expect something like:

UPDATE table1 SET quantity=8 AND last_Sent_Date=DATE('16-05-2011') WHERE ID = 18;

I'm not sure whether the date conversion is completely correct. I don't have a lot of experience in AS/400

Upvotes: 0

Related Questions