Results 1 to 9 of 9

Thread: SQL0204 error when accessing iSeries file via Microsoft Access/ODBC

  1. #1
    Senior Member Crazy_About_JDE's Avatar
    Join Date
    Jul 2003
    Location
    Indianapolis
    Posts
    224

    SQL0204 error when accessing iSeries file via Microsoft Access/ODBC

    Hello! Is anyone else out there successfully connecting to iSeries V5R3 files via Microsoft Access? I am able to link to the ODBC connection and add the table, but when I double-click the entry, I get this error (screenshot attached):

    Could not execute query; could not find linked table. [IBM] [iSeries Access ODBC Driver][DB2 UDB]SQL0204 - "7334" in OL7334 type *FILE not found. (#-204)

    (For example, Object Librarian - B7334 which points to library OL7334. In this case I was trying to link to OL7334/F9860.)

    It allows me to import the files just fine.

    I have updated to the latest iSeries Access for Windows V5R3 service pack and still get the same results. I have tried Access 2000 and Access 2003 and get the same result for both.

    IBM Software Support helped me capture a trace log, and it appears that Microsoft Access is incorrectly forming the SQL statement. Any ideas on how to get around this?

    -Tim

    Attached Images Attached Images
    Indianapolis JDE/PeopleSoft Regional User Group last met June 2009
    http://indypsjderug.blogspot.com/

    OneWorld B7334 Upd 1 SP23_J1
    iSeries V5R3, iTera Echo2
    IMS/21 document imaging

  2. #2
    Senior Member
    Join Date
    Oct 2000
    Location
    Houston, TX
    Posts
    157

    Re: SQL0204 error when accessing iSeries file via Microsoft Access/ODBC

    Did you try to change Naming Convention in ODBC setup (tab Server) to 'SQL naming convention (*SQL))'?


    Bojan.
    Xe, SP23, V5R4
    Citrix, Optio

  3. #3
    Senior Member Crazy_About_JDE's Avatar
    Join Date
    Jul 2003
    Location
    Indianapolis
    Posts
    224

    Re: SQL0204 error when accessing iSeries file via Microsoft Access/ODBC

    Good question. The naming convention is already set to *SQL. I just tried setting it to *SYS, but got the same result.

    Also, I was wrong in the original post: I get the same error when I try to import a table as when I try to link. What does work is iSeries Access' Data Transfer from iSeries applet.

    Any other ideas?
    Indianapolis JDE/PeopleSoft Regional User Group last met June 2009
    http://indypsjderug.blogspot.com/

    OneWorld B7334 Upd 1 SP23_J1
    iSeries V5R3, iTera Echo2
    IMS/21 document imaging

  4. #4
    New Member
    Join Date
    Jan 2006
    Location
    Eden Prairie, MN
    Posts
    8

    Re: SQL0204 error when accessing iSeries file via Microsoft Access/ODBC

    Tim - it appears that your file is named incorrectly - you can't start a file or object name with a number on the i5 (in your case it appears the file name is 7334) - perhaps something is being transposed or you keyed in the wrong file name. Look up that error message in the message file on the i5 by doing a WRKMSGF QSQLMSG - then position to the SQL0204 error and read the message definition. Hope that helps.
    E1 8.11 SP1, 8.95_i2, iSeries V5R3, WAS 5.0.2-PD
    E1 8.11, SP1, 8.95_i2, iSeries V5R3, WAS 5.0.2-DV/PY
    MIMIX: successful switch test

  5. #5
    Senior Member Crazy_About_JDE's Avatar
    Join Date
    Jul 2003
    Location
    Indianapolis
    Posts
    224

    Fixed: SQL0204 error when accessing iSeries file via Microsoft Access/ODBC

    Does anyone know why OneWorld setup creates IBM Client Access ODBCs using *SYS naming convention instead of *SQL naming convention?

    I tested just now and found that OneWorld works under both circumstances - but I can only use Microsoft Access to display records if the ODBC is set to use the *SYS naming convention.

    Can anyone think of a reason NOT to change them all to *SQL?


    Some Background Details: Back in July, I was getting the SQL0204 error when trying to display records from a normal, pre-installed OneWorld ODBC connection. IBM Software Support told me I just need to change the ODBC to use *SQL naming convention -- but I got the same result.

    I finally got this to work when I created my ODBC connection as a User DSN rather than a System DSN. I was confused and put off further investigation until yesterday. Here's what I found:

    Changing the ODBC connection to use the SQL naming convention REALLY WOULD HAVE WORKED -- but I should have first exited the Microsoft Access application and then reopened it. It appears to me that Access caches the information from the first attempt, and so it NEVER WORKED after the first attempt failed!

    ARGH!

    It turns out that the default naming convention is SQL, so that's why creating a new ODBC worked but modifying an existing one did not.


    So...That's the rest of the story. Thank you for listening.
    Indianapolis JDE/PeopleSoft Regional User Group last met June 2009
    http://indypsjderug.blogspot.com/

    OneWorld B7334 Upd 1 SP23_J1
    iSeries V5R3, iTera Echo2
    IMS/21 document imaging

  6. #6

    Re: SQL0204 error when accessing iSeries file via Microsoft Access/ODBC

    Main advantage of *SYS setting is that it allows for unqualified table reference to be searched in the *LIBL (library list) of the (ODBC) job. Since you say that OW works either way, it must mean one of two things: OW fully qualifies all table references with a schema (collection, library) OR all files are in the same (default) library so even if they’re unqualified, they’re always located easily by the system.

    Other major difference *SYS has is that reference qualifications are signified by a slash (‘/’) rather than by a period (‘.’).

  7. #7
    Member
    Join Date
    Mar 2004
    Location
    MO
    Posts
    38

    Re: Fixed: SQL0204 error when accessing iSeries file via Microsoft Access/ODBC

    It looks to me that JDE doesn't care what the ODBC is set to, it always overrides on the ODBC call to use *SYS and provides the Library name on the INSERT, DELETE, or SELECT statements.
    Jerry Camden
    No longer @ Paul Mueller Company, Springfield, MO
    ERP8, SP23_L2, Updt 1, iSeries V5R4, Fat
    moving to E1-8.12.3 8.97.1.1 Intel/Win2k3 SQL2005 WAS 6.0.2

  8. #8
    Senior Member Sebastian Sajaroff's Avatar
    Join Date
    Mar 2005
    Location
    Montreal (QC) Canada
    Posts
    1,179

    Re: SQL0204 error when accessing iSeries file via Microsoft Access/ODBC

    Hi,

    Does your workstation have the latest MDAC? 2.8 something.
    You can get it from www.microsoft.com/data
    Sebastian Sajaroff
    CNC - B7321 to E900

  9. #9
    New Member
    Join Date
    Feb 2014
    Posts
    1

    Re: SQL0204 error when accessing iSeries file via Microsoft Access/ODBC

    Hi...

    I am new to this and need help. I want to use MS Access to connect to JDE World running on iSeries. Can you tell me where I can find the ODBC driver for this? Currently the Showcase ODBC is not support MS Access and I want to run queries and report from MS Access with link tables. Please advice and thank you very much.

    Sam56

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
The legal restrictions and terms of use applicable to this site are available here.
Use of this site signifies your agreement to the terms of use.
JDELIST is NOT affiliated with JD Edwards® & Company, Oracle or Peoplesoft. Contents of this site are neither endorsed nor approved by JD Edwards® & Company and, or Oracle.