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)};" & _
     "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

Operating SystemsMicrosoft Access

Avatar of undefined
Last Comment
Member_2_276102

8/22/2022 - Mon
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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER
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.


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 ;-(
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
rward

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
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 connstring.open statement.
I was hopinig to use the OLEDB because it's supposed to be faster, correct?
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
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?  
Theo Kouwenhoven

Just post it somewhere where I can find it :)
Your help has saved me hundreds of hours of internet surfing.
fblack61
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