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.

 

[blom0344]

No longer access to an AS400, but:

1. Did you test if a pass-through query works?
2. Did you define the appropriate primary/unique key? When linking the AS400 tables you should get a dialogue screen asking you to define a unique set.
Are you using Client Acces Express middleware?

Ties Blom

 

[Jim0845]

As to the pass-through query, no. I am newly returned to Access, and was never an expert in it. I am using a link to the table on the AS400 and can query it with no problem. I will see what a pass-through query can do for me.

The 3rd party software I am using is something called Advanced Query Tool, or AQT, although I also use the Client Access iSeries Navigator sql tools. I can perform any actions I need to on this particular table or any other table on the AS400 with either of those tools.

The table that I want to maintain with Access is a simple two field table, and is set up correctly. I can update/append to a copy of the table in Access from another table in Access, or I can update/append the true table on the AS400 using either of the 3rd party tools I mentioned.

 

[blom0344]

I'd say that searching in the MS Access queries and jet SQL on linked tables may be a better bet. There are plenty of example on pass-through queries and the use of VBA to execute SQL dynamically..

Ties Blom

 

[Jim0845]

I had to change the commit mode setting under "Advanced server options" in my ODBC driver setup. Once I did that everything works as expected.

Thanks for your suggestions.

 

[sathyarams]

Out of curiosity, what did you change the setting to ?

I had to change the commit mode setting under "Advanced server options" in my ODBC driver setup

For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

&

http://www.idug.org/user/UserLogin.asp

 

[Jim0845]

I wanted a different DSN configuration than the one I normally use so I could put tighter restrictions on it, so I duplicated it and then added the restrictions. The original setting for commit mode was "Read uncommitted (*CHG)", which is fine because I am able to apply the commit options as needed as part of the sql scripts in the sql tool I use. I was unable to find out how to do that in Access so I changed the commit mode setting for the new DSN to "Commit immediate (*NONE)".

Any suggestions for another way to do it would still be appreciated.