Updating DB2 with Access

[Jim0845]

I have a simple DB2 table on an AS400. I have created a linked table in Access and have no problem retrieving any of the data I want. However, when I try to update or append the DB2 table, I get "ODBC--insert on a linked table 'LIBRARYNAME_TABLENAME' failed.

When I click on the help button I get "ODBC — insert on a linked table <table> failed. (Error3155)"

When I click OK, I get "[IBM][iSeries Access ODBC Driver][DB2 UDB]sql7008 - TABLENAME' in LIBRARYNAME not valid for operation."

I have full administrator access to the DB2 file and the AS400.

I use a 3rd party SOL tool to access all files on the AS400 and have no problem in manipulating the data in any way that I need. When I try to refine my Access query in the way that I have to construct the 3rd party tool queries for update or insert, I get

"Syntax error in WITH OWNERACCESS OPTION declaration."

I have not been able to come up with a variation of this declaration that Access likes, or that DB2 recognizes. The ODBC drivers I use for the 3rd party tool are the same drivers I have used for the Access ODBC connection.

I want to enable a power user that has no interest in learning SQL to maintain user information in this simple table. Access would be a perfect and quick solution if I can get over this hurdle.

All suggestions will be much appreciated.

 

[martyclaus]

Does your table have a unique access? I had a situation where I had an index, just not a unique one and Access would not let me update the table until I added a unique index.

 

[Jim0845]

I felt I had submitted this issue in the wrong forum so I resubmitted it under IBM: DB2. There is some additional dialogue there, but the solution was to change the commit mode setting under "Advanced server options" in my ODBC driver setup. Once I did that everything worked as expected.

Thanks for the response.