SQL0204 error when accessing iSeries file via Microsoft Access/ODBC

Crazy_About_JDE

Crazy_About_JDE

Well Known Member
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
 

Attachments

  • 109146-sql0204 error.gif
    109146-sql0204 error.gif
    32.8 KB · Views: 247
Did you try to change Naming Convention in ODBC setup (tab Server) to 'SQL naming convention (*SQL))'?


Bojan.
 
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?
 
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.
 
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.
 
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 (‘.’).
 
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.
 
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
 
Back
Top