Link to home
Start Free TrialLog in
Avatar of FSOLL
FSOLLFlag for United States of America

asked on

Problem with character fields when extracting from AS400 and saving to MDB table

I have a connection and SQL statement from mdb to AS400 to extract records and save in mdb table.  I'm getting the correct number of records and NUMBER fields are coming in OK.  But text fields are ???? and cause error 13-TYPE MISMATCH even though they are declared TEXT in mdb
Dim strsql As String
   Dim con As New adodb.Connection
   Dim rsi As New adodb.Recordset
   Dim rso As DAO.Recordset
   Dim db As DAO.Database
   Dim connStr As String
   con.Open "ODBC={MYODBC};" & _
     "Driver={Client Access ODBC Driver (32-bit)};" & _
   strsql = Space(250)
            " FROM JDPCORE.F0902" & _
            " WHERE GBFY = 8 AND GBCRCD = 'USD' AND GBLT = 'AA' AND GBOBJ = '2114'"
   rsi.Open strsql, con, adOpenStatic, adLockOptimistic
   Set rso = CurrentDb.OpenRecordset("ollZZZZZ", dbOpenDynaset)
   Do Until rsi.EOF
       With rso
          !gbaid = rsi!gbaid           <===TEXT 8 --does not work
          !gbctry = rsi!gbctry         <===NUMBER DOUBLE -- WORKS
          !gblt = rsi!gblt             <===TEXT 2 --does not work 
          !GBFY = rsi!GBFY             <===NUMBER DOUBLE -- WORKS
       End With

Open in new window

Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image


!gbaid = nz(cstr(rsi!gbaid))
Hi Fsoll,

You are not only copying from EBCDIC to ASCII, but AS/400 files can also contain Pached and binary fields, these can messup your data if they are not translated as required.

I advise to use an other driver IBMDA400.

Connectionstring is:
Provider=IBMDA400;Data Source=MY_SYSTEM_NAME;User Id=myUsername;Password=myPassword;

Good Luck,
or you can choose from here, whichever connection string is appropriate
Avatar of FSOLL


NZ(cstr) worked to the extent that I don't get the TYPE MISMATCH any more but the character fields were wrong  (see attached file).  
I get the same results with the original or the new connection string from murphey2.

Hi Capricorn1,

There are no other connectionstrings for the IBMDA400 driver, and other drivers will habe probably the same problem  with packed and binary fields.
The only other option (connection string) is to add "Default Collection=MY_LIBRARY;"
to the example i gave, but that will make no diference in this case.
because the SQL command is pointing to a qualified file "JDPCORE.F0902"

thanks for the info Murph
wish i have an access to an AS400 db for testing ;-(
Avatar of rward
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of FSOLL


The TRANSLATE did the trick!!  However, it only works with my original ODBC connection string.  With IBMDA400 it gives error MULTIPLE-STEP OLEDB OPERATION GENERATED ERRORS.  CHECK EACH OLEDB STATUS VALUE IF AVAILABE on the statement.
I was hopinig to use the OLEDB because it's supposed to be faster, correct?
Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of FSOLL


Murph,  thanks for the help.  You're getting the points.
I have a follow-up/continuation question for the same code, do I post it new or just here?  
Just post it somewhere where I can find it :)

"Murph,  thanks for the help.  You're getting the points."

is not the same as giving the ponts to myself and close the question is it ?
Avatar of Member_2_276102


Murph's answer deserves the points, so I'm only adding some elaboration.

The long-term solution is to fix the database on your AS/400. The CCSID 65535 issue is almost certainly caused because the system has never been configured for its true CCSID. The tables were created as CCSID 65535 by default.

CCSID 65535 causes the equivalent of BINARY transport in FTP -- i.e., no translation. In particular, you almost certainly are receiving character text encoded as EBCDIC rather than ASCII (or Windows Unicode or whatever is on the receiving side). You are not getting the benefit of automatic character code conversion.

The implication is that this can be an issue for every future connection.

It's likely that most of your issues can be resolved by setting the proper system CCSID in the QCCSID system value. It might be necessary to set CCSID values for your tables as well, but a number of sites have managed to get by just with changing the system-wide setting.