Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2010
    Posts
    4

    Unanswered: Write data to iSeries from ODBC Connection

    Hello,

    Ok, so here's the skinny, I am able to link a table in MS Access 2003 to an IBM iSeries V5R4 machine. The problem is that I am unable to add a record or write data to the linked table in MS Access. Here's the error that I receive(ODBC--insert on a linked table 'REPORTS_TEST' failed. [IBM] [iSeries Access ODBC Driver] [DB2 UDB] SQL7008 - TEST in REPORTS not vailid for operation. (#-7008)

    I have checked the user permissions that I am using to connect to iSeries and can't see anything that would restrict the user profile. I also checked the library and file permissions on the iSeries and don't see any glaring problems.

    Any help would be greatly appreciated!

    Thanks.

    Austin

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Are you able to update existing records?

    Does your backend tables have an autonumber type field? This would be a field that auto-increments when adding new records and sometimes the culprit if you cannot add new records when connecting to a non-typical backend such as SQL Server.

    Are there any relational tables involved that need a record first?

    Can you update the linked tables directly? (not via any coding but just opening the tables and trying to add a new record or update an existing record.)

    If you can update the data directly in the linked tables, then it's probably a coding issue but you need to first see if you can update the data in the tables without any coding involved.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Jan 2010
    Posts
    4
    Thanks for the reply, I don't have physical access to the server until Monday morning, so I will check the above suggestions.

    One thing that I do know is that I was unable to manually add a record when the linked table was opened in the DataSheet view in MS Access. I was able to select add record from the insert menu, but when I actually typed data in a few of the fields and then tried to save it, that was when I got the SQL #7008 error.

    Thanks again!

    Austin
    Last edited by austintexas45; 01-23-10 at 19:34.

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You need to check required fields and make sure the error you're getting is not due to any kind of constraints.

    First always try adding a record to the table directly, then in your code. If it fails adding directly, then you know it's either something with the driver (ODBC) which is usually typical when connecting to a non-microsoft type backend. Also look for new a ODBC driver or other driver as these are often the problems.

    If it's a permissions problem, this usually throws up a different kind of error but again, not a lot of us have experience with connecting to a IBM iSeries V5R4 machine type machine so there are many different possibilities. But double-check for a current driver/ODBC type problem. That's what I usually look for if I cannot add data directly into the table if I'm sure permissions are adequate. I recall when I was using a Progressive ODBC type driver to connect to a unix type backend, there were many issues and I had to search and search/uninstall/install and a lot of pain getting it to work.
    Last edited by pkstormy; 01-23-10 at 19:51.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Jan 2010
    Posts
    4
    That makes sense, I will check both the required field suggestion and the iSeries ODBC driver.

    Thanks.

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Good luck. Let us know what you find. It's good to know solutions like this (and I'm curious.)
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Jan 2010
    Posts
    4
    Still working on the problem and looking for a different driver other than the iSeries Access driver (CWBODBC.DLL). Honestly, I think think there might be a logical file or other type of trigger program that isn't allowing a write to the file. So, I'm going to see if a programmer with our data processor can figure it out. Thanks, and will let you know the solution once it's figured out.

  8. #8
    Join Date
    Jun 2011
    Posts
    2

    possible issue with updating the iSeries

    I realize this thread is over 1 year old, and you shouldn't need to do this with odbc connections, but when I am updating the iseries files from a web server the iseries files have to be journaled. You don't get any messages, they just never update or add or delete. Here is a link to one of my forum posts with the instructions and a document you can download. Update files on the iSeries/AS400 from your Web application
    Thanks
    Ann

  9. #9
    Join Date
    Jan 2009
    Location
    Kerala, India
    Posts
    183

    possible issue with updating the iSeries

    Check the ODBC configuration Server SQL settings under Connection Type. See the attached image.

    Reference: LEARN MS-ACCESS TIPS AND TRICKS - Linking with IBM AS400 Tables
    Attached Thumbnails Attached Thumbnails iseries_4.gif  
    www.MsAccessTips.com (Learn MS-Access Tips & Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

    All responses are based on Access 2003/2007

  10. #10
    Join Date
    Jun 2011
    Posts
    2

    thanks for the links

    thanks for the links. I am about to try to connect a UPS pc and an USPS service pc (running Edicia) to the AS400 for auto passing data back and forth and was investigating options. Was hoping to go direct, but may have to fall back on a Java app running on one of our Linux server to move the data automatically. thanks Ann

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •