VB ODBC DLL Translation

What connection inteface is the AS400 running?  There are a few sites that have info.  If you can send standard SQL commands to the server then it will be easy.

Once you have loaded the database drivers onto your PC MS advise the use of a UDL file to access the database.  In this way you can test your connection to the database without having to write a single line of code.  The UDL file will also give you a connection string you can use to put into your VB code.

I presume you have read stuff like like this....there are many sites offering help.

http://www.as400pro.com/tipView/category/PC/key/273

I'm connecting using the iseries ODBC driver. I'm not sure if that is answering your question. I'm not sure what you mean by UDL file.

I have done searches on google and yahoo and many searches on ibm.com.

Basically I can make a connection and retrieve data back. But I've been told this particular file on the 400 in a Turkey (code page 1026) environment has code page 37 on one of the fields.  But when I put 37 as the code page in the driver or use a sql statement like "select aban8,Cast(abalph AS CHAR(50) CCSID 37)  from tr73ladvc.jde2f0101"
I still get odd characters that are not translated properly. like "OMN$TEK B.SAYAR  SAN. VE T$C.LTD @T$"  I actually get these results no matter what code page I try or if I just do a select abalph without cast. Although some records look fine. others look like this. Specifying the code page in the sql statement or the odbc driver does nothing for us so I thought maybe if I could look at a sample translation dll; I could change write one for our needs.

Our AS/400 team in Europe suggested this route so I was trying to find a sample file.

Thx for any help.

Sorry been away....so you are mailing a connection using what ADO DAO or a 3rd party database tool?

If you are using ADO, then there is more we can do...the company the wrote the driver are mostly likely to be able to help as they own the problem.

So if you are using ADO you need to find out what a UDL file is and lucky for you it is so simple it is almost not true.

You simply create a new text file and rename it MyConnection.udl then double click on the file. You will then see the Universal Data Locator tool, you select provider, the  choose AS400 or whaterver driver and then you should get all of the options available to you. One of which may heklp with your connection issue.  But if you can find the company who created the driver "the provider" then you should be able to solve this issue through them.

Although I get the same results from ADO; I'm actually using a DB2 sql tool called WinSQL (free).

I'm specifically using the i-series IBM ODBC driver standard with the AS/400 for both the DB2 sql tool and the ADO connectivity.

IBM is not very helpful; I have done search after search on ibm.com reading through red papers etc with no luck.

ok. I created a UDL; but would that really be any different than the following code:
con.Open "PROVIDER=IBMDA400;Data Source=EDC400D1;USER ID=TRGPDSS;PASSWORD=fa4ze9ca3 ;"

Set cmd.ActiveConnection = con

cmd.CommandText = "select Cast(abalph AS CHAR(50) CCSID 37)  from tr73ladvc.jde2f0101  where aban8=900006"
cmd.Prepared = True
Set rs = cmd.Execute()
While Not rs.EOF
Debug.Print rs.Fields(0)

rs.MoveNext

Wend
con.Close


Thanks !