MS Access and AS400

[Guest.Visitor]

I am trying to interface Microsoft Access 97 and the AS400 through Client Access. I can make connection to the AS400 and retrieve Data. Yet when I select a sort option in a Microsoft Access Query I get the error ODBC Call Failed - Order by Column xxxx not in result table. It appears that the order by sql statement is not being properly interpreted by the AS400. Any suggestions? Thanks

[Guest.Visitor]

post a copy of the SQL select statement and the DSPFFD for the 400 table; I seem to remember something on this in an earlier forum;

[Guest.Visitor]

Following is a sample of the SQL. Note that this error happens anytime i create a query with access that has a sort. Does not matter the table or the field. My first thought was some configuration parm in the odbc setup but have not been able to find anything to change this. SELECT BPCSF604_IIML01.IPROD, BPCSF604_IIML01.IDESC, BPCSF604_IIML01.ISCST, BPCSF604_IIML01.IREF01, BPCSF604_IIML01.IREF02 FROM BPCSF604_IIML01 WHERE (((BPCSF604_IIML01.IPROD) Like "1N*")) ORDER BY BPCSF604_IIML01.IREF01; Thanks

[Guest.Visitor]

Michael, I am puzzled if that is the EXACT statement. My first guess is that you mis-spelled the column name in the order by reference. You should be able to use the column number rather than the name so just change it to Order by 4 Bill

[Guest.Visitor]

The SQL statement is automatically created by Microsoft Access using the Query tool. After linking to the appropriate table using ODBC all I have to do is select the fields from the list and tell what sort order, which is a drop down list box. I do not actually ever type the SQL statement in. I just selected View Sql to see what was generated by Access. Hope This helps Thanks Michael Rhodes

[Guest.Visitor]

Michael - I have a vague recollection from an Access class that Access creates "Microsoft SQL" (meaning non-standard). I don't remember all the specifics but would suggest using STRSQL on the /400 and keying (or prompting) the same SQL statement (minus the Access-centric parts) to see if there are differences. HTH, Steve

[Guest.Visitor]

I am having trouble with some of my users and their processes. They are downloading data from the AS400 via Client Access. They then upload this data to MS Excel and MS Access '97. The dates are downloaded in "CYYMMDD" format. This did not cause a problem processing 20th century dates, because the "C" will be a zero which Excel and Access surpress. Now that they, the users, are processing 21st century dates, the "C" is a 1. Now Excel and Access say this is an invalid date. I am trying find a fix for the problem with out changing the 400. I have looked at a tool by Data Junction that seems to help transfer the data to the PC with the correct dates. If anyone else has any suggestions, they would be appreciated

[Guest.Visitor]

3 suggestions. 1, write an excell macro to retrieve the data because then you have direct control over the formatting of the data and can use appropriate scalar functions in the sql statement or vba functions for data conversion. I use ADO to do this all of the time and should have a few examples on my web site: www.sqlthing.com 2, use CA express to open the table in quick view and copy the data to the windows clipboard, it will then paste into excell correctly. 3, Check out tool SQLThing. Howard

[Guest.Visitor]

Michael, One thing you might try would be to copy the generated SQL via CTRL-C and then create a query without the use of the Wizard. CTRL-V to past the code into a blank Query and then try to run it. If it still bombs, then try the Order by 4 option I mentioned earlier. Bill

[Guest.Visitor]

Hi Michael, Did you ever solve the "Error 3146...ODBC Call Failed" problem ? I'm having the same problem when sorting a linked table on the 400 thru Access '97 SQL ? Thanks Bob Wenner