We help IT Professionals succeed at work.

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

757 Views
Last Modified: 2013-12-06
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

Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016

Commented:
try

!gbaid = nz(cstr(rsi!gbaid))
Theo KouwenhovenApplication Consultant
CERTIFIED EXPERT

Commented:
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
CERTIFIED EXPERT
Top Expert 2016

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

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

Author

Commented:
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 KouwenhovenApplication Consultant
CERTIFIED EXPERT

Commented:
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
CERTIFIED EXPERT
Top Expert 2016

Commented:
thanks for the info Murph
wish i have an access to an AS400 db for testing ;-(
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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?
Theo KouwenhovenApplication Consultant
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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 KouwenhovenApplication Consultant
CERTIFIED EXPERT

Commented:
Just post it somewhere where I can find it :)
Theo KouwenhovenApplication Consultant
CERTIFIED EXPERT

Commented:
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 ?
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
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.