Hi there,

I'm trying to get data from AS/400. When I execute this SQL line in AS/400 I'll get proper data (= proper data in both colmuns). When I execute that code from MS Access VBA the first columns shows as question marks '?????'.

The SQL in AS/400 (STRSQL) is like this:

SELECT DRKY, DRDL01 FROM JLSFSP73.F0005 where DRSY='58 ' AND DRRT='WS'

The code in MS Access looks like this:

-----<code start>-----
Sub TestThis()

Dim myCnn As ADODB.Connection
Dim myRs, myRs2 As ADODB.Recordset

Set myCnn = New ADODB.Connection
myCnn.Open "Driver={Client Access ODBC Driver (32-bit)};System=mySystemName;UID=myUserID;PWD=myPwd"

Set myRs = New ADODB.Recordset
Set myRs.ActiveConnection = myCnn

myRs.Open "SELECT DRKY, DRDL01 FROM JLSFSP73.F0005 where DRSY='58 ' AND DRRT='WS'"
myRs.MoveFirst
Do Until myRs.EOF
Debug.Print myRs!drky & " " & myRs!drdl01
myRs.MoveNext
Loop
myRs.Close
Set myRs = Nothing

myCnn.Close
Set myCnn = Nothing

End Sub

-----<code end>-----

And the result looks like this:

????? LAMINOITU LASI
????? IKKUNALASI
????? IKKUNALASI KEHYKSILL#
????? KIERR#TYSKERAMIIKKA

(The # characters in second column are scandinavian characters which have translated into # characters)

In DB2 field DRKY is CHAR 10 with CCSID=65535 and field DRDL01 is CHAR 30 with CCSID=37.

I have tried changing the connection string so that it has CCSID=37 at the end (and other values too) with no help. Only the data in second column has changed.

I figured that this is a connect string issue, am I right? Anyone?

-V-

See if this helps:

http://laservault.com/sales/caerr.html

Unfortunately no help Changing CCSID only changes the data in the second field, not in the first one. They still show up as questionmarks.

This is a known problem : having this ccsid 65...
this because db2 connect / drda does not support this ccsid translation and result data is displayed in ebcdic mode.
the only way to correct this
have another ccsid as default for db2 (new data)
old data should be converted to new files, flagged with different ccsid
and old data copy to new files...
Not very easy but possible
Best Regards, Guy Przytula

Problem solved! I used T-SQL. Now sql goes like this:

"SELECT CAST(DRKY AS CHARACTER(30) CCSID 037) AS DRKY, DRDL01 FROM JLSFSP73.F0005 WHERE DRSY='58 ' AND DRRT='WS'"

With that CAST function I can change the datatype AND the CCSID of any fields in my query. It's brilliant