Update to AS400

[cjany]

Is it possible to use Access to Update fields on the AS400? I have an ODBC connection, and linked tables to the AS400. I've tried creating an update query to update certain fields. Here is my SQL:

UPDATE EZTAX30DTA_TXEVNTD SET EZTAX30DTA_TXEVNTD.EI2RPLCDE = "REPLYNONE", EZTAX30DTA_TXEVNTD.EI2RPLDAT = 20061228
WHERE (((EZTAX30DTA_TXEVNTD.EI2INSSNT)="TARGETLT00") AND ((EZTAX30DTA_TXEVNTD.EI2DATDUE)=20061115) AND ((EZTAX30DTA_TXEVNTD.EI2RPLCDE)=""));

The error I get when running this query is:

ODBC-update on a linked table 'EZTAX30DTA_TXEVNTD' failed.
[IBM][iSeries Acces ODBC driver][DB2 UDB]SQL7008-TXEVNTD in EZTAX30DTA not valid for operation.

I've even tried this in a pass-through query.
I have my ODBC settings correct with Read/Write SQL Statements checked.

Any suggestions?

 

[lespaul]

Do you have permissions on the AS400 to perform data changes?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[cjany]

Yes, I can make changes manually to the TXEVNTD table.

 

[AlexCuse]

Through your access front end you can do this?

Ignorance of certain subjects is a great part of wisdom

 

[cjany]

No, I cannot update the linked table from access.

 

[AlexCuse]

Then it is probably an issue of permissions on your AS400's database. You should look in the forums for whatever db platform you use on this machine, and hopefully find some help there on how to modify permissions.

GOodLuck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[willybgw]

When you link the file you should get a dialog box to select a unique record identifier, If you don't I don't think the file is updatable. I just tried this and was able to update an AS400 record.

willybgw

 

[JPMontreal]

Hi,
When you had created the link to the AS400, did you select, in the list of fields that popup, a primary Key for update or edit?
(If you could not edit a field directly in the Access table, it could be the case)
Also, if you could edit a table using one of the AS400 screen, that doesn’t necessary mean that you have direct right access to the table.
Last, you should use a pass through query with the AS400 syntax, especially for date, since the action is going to be performed there.
Regards.


Jean-Paul
Montreal
To send me E-Mail, remove “USELESSCODE”.
jp@USELESSCODEsolutionsvba.com