linking an iseries data base to microsoft access using odbc
I was able to link my file, but could not get it to update, I read something that said you must turn on journaling on the 400, so I did that. Now when I update the file in access it tells me I am trying to update a record that was changed by another user since I opened the access file (which I know can not be, since it is a test file). Is there something else I need to do to be able to actually update data from the 400 through access? Thanks to anyone who can help.
Audrey
Software/Hardware used:
Software/Hardware used:
ASKED:
March 4, 2005 12:24 PM
UPDATED:
March 8, 2005 5:05 AM
Answer Wiki:
Not claiming any expertise in this area, here is what I'd look at:
1). Is the "identity" of the ODBC link consistent with the "identity" of the journaling process?
In many situations like this, what you want to do in a production environment is have a "thread" on the AS400 which acts as a server to the database, accepting transactions from any legitimate user.
When you set up the ODBC link in an "end user" MS Access environment, you may be accepting default settings for that link which could make it compete with the "Journal" thread for access to the database.
To see all answers submitted to the Answer Wiki: View Answer History.
I have an “old” note that mentions ODBC updates require the AS400 table to have a unique index. This may be your problem.
AS400 does not need a unique key for normal functioning (not even the presence of a key) Journaling has nothing to do with the ODBC access. But when you create the data source on your PC, did you flag read/write access? You can allow only read, in which case you cannot do the update. A 2nd reason can be your logon when you do the ODBC connection: do you have write access to this file/library?
Hope this helps,
Unique keys are one of the possibilities. See this doc on IBM Software Knowledge Base site: http://www-912.ibm.com/s_dir/slkbase.nsf/1ac66549a21402188625680b0002037e/974fd097872a5c59862565c2007ccb27?OpenDocument&ExpandSection=1&Highlight=2,odbc,update#_Section1
If the link doesn’t work, search on ODBC update (All words), it was document# 8102844
Application Requirements:
o Verify that the table has a unique key (primary key or unique constraint). Some applications cannot work with unique key fields of type TIMESTAMP.
o Use Client side cursors or MDAC 2.1 and later (MDAC is available at http://www.microsoft.com/data).
Some info from doc:
OS/400 Requirements:
o Verify that you are authorized to the table.
o Verify that the ODBC data source is configured for a read/write connection and not a “read only” connection.
o Verify that other security restrictions such as user exit programs and Client Access policies are not preventing the update.
o Verify that the file is journaled if the isolation level is not *NONE.
Note that the cause of these problems is clearly indicated by the error message returned; however, some applications may suppress the Client Access error message.
Hope this helps.
There may be a protocol problem between AS400 and server/PC. Not all servers have TCP/IP activated. Not sure how this displays itself in terms of messaging or how far you can get without it.