I am trying to setup up some MS Excel Files which will import data from an AS400 using the following methods but i am getting mixed results.
In Excel i am using the menu "Data / Import External Data / New Database Query"
(i would like to use this method in particular because the "Refresh Data" option is very useful for our users)
In Windows XP i have setup an ODBC data source on the "User DSN" tab as follows.
Using "Client Access ODBC Driver (32-bit)
On the Server Tab i have selected
"SQL Naming convention (*SQL)"
SQL defaul library = <left blank>
Library list = TestlibA
OS/400 Version - V5R4M5
PTF Group Level = SF99540
PTF Status = TL57282
On the AS400 the library "TestlibA" has 26 files.
I have run a test transfer on each of the 26 files have found that 14 files will transfer over ok but 12 files will not.
On the 12 files that will not transfer i get the following message:
SQL5001 Column Qualifer or the table <tablename> undefined.
When i looked up this message in google it directed me to the IBM website.
The explanation for the SQL messge was given as .....
Your ODBC Data Source Name (DSN) configuration uses the wrong naming convention. Use the ODBC Administrator to change your DSN to use the proper (*SQL or *SYS) naming convention. Always use *SQL unless your application design specifically expects *SYS.
I then changed the naming convention in the ODBC Data Administrator
from "SQL Naming convention (*SQL)" to "System naming convention (*SYS)"
When i attempted to transfer the same file again i got the following message:
SQL5016 Qualified object name <tablename> not valid
When i looked up this message in google it directed me to the IBM website.
The explanation for the SQL messge was given as .....
Change your naming convention in your iSeries Access ODBC DSN.
Probable cause:
Your ODBC Data Source Name (DSN) configuration uses the wrong naming convention.
Use the ODBC Administrator to change your DSN to use the proper (*SQL or *SYS) naming convention.
Always use *SQL unless your application design specifically expects *SYS.
This has posed a few questions for me.
Is the problem in the in the ODBC Data Source setup ?
Is the problem in the AS400 files ?
All the files are physical files on the AS400
I have compared the files that will and will not transfer (using DSPFD command) and i can see no difference in their structure.
Most of them are produced from AS400 Queries.
Can anyone help?
Regards
John.
Start Free Trial
