troubleshooting Question

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

Avatar of FSOLL
FSOLLFlag for United States of America asked on
Operating SystemsMicrosoft Access
13 Comments2 Solutions761 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
rward

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 13 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 13 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros