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)};" & _
     "System=xxxxx"
   
    
   strsql = Space(250)
   strsql = "SELECT GBAID,GBCTRY,GBFY,GBLT" & _
            " 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)
   
   rsi.MoveFirst
   Do Until rsi.EOF
       With rso
          .AddNew
          !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
          .Update
       End With
       rsi.MoveNext
   Loop

Open in new window

Rey Obrero (Capricorn1)

try

!gbaid = nz(cstr(rsi!gbaid))

Theo Kouwenhoven

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,
Murph

Rey Obrero (Capricorn1)

or you can choose from here, whichever connection string is appropriate

http://www.connectionstrings.com/?carrier=as400

FSOLL

ASKER

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.

BADrecords.doc

Theo Kouwenhoven

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"

Regards,
Murph

Rey Obrero (Capricorn1)

thanks for the info Murph
wish i have an access to an AS400 db for testing ;-(

ASKER CERTIFIED SOLUTION

rward

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

FSOLL

ASKER

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 connstring.open statement.
I was hopinig to use the OLEDB because it's supposed to be faster, correct?

SOLUTION

Theo Kouwenhoven

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

FSOLL

ASKER

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?

Theo Kouwenhoven

Just post it somewhere where I can find it :)

Theo Kouwenhoven

Hi FSOLL,

"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 ?

Member_2_276102

FSOLL:

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.

Tom