[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1436
  • Last Modified:

Excel 2003 vba program extracting data from iSeries now dropping decimals after win7

I have an Excel 2003 vba program that extracts data from iSeries.
It has been working for the last 2 years but now it is NO longer working -- it brings NO decimals, thus truncating the answer of amounts, quantity, etc.
What has changed:  Upgraded from XP to Win7 and iSeries/Client Access from 11 to 13.
Has anybody experienced this?  What is the solution?

Thank you
0
FSOLL
Asked:
FSOLL
  • 11
  • 11
4 Solutions
 
Gary PattersonVP Technology / Senior Consultant Commented:
I've seen some similar issues, but it was related to an Excel upgrade if I recall correctly, and not Windows or Client Access.  If you want to post your program, I'd be happy to take a look.  Also, post the DDS or SQL DDL for the table with the problem field(s).  

I just want to see the data type and length of the problem fields.

Can you clarify the problem with an example?  When you say it "brings NO decimals, thus truncating the answer of amounts, quantity, etc." do mean it brings down 123.45

as 123 (truncated to integer)

or as 12,345 (decimal point is ignored)?

0
 
FSOLLAuthor Commented:
Thanks for your quick reply.
The code is attached.  This is a JDE file in the iSeries. ODBC is the DSN of the ODBC used to connect to iSeries.
It returns the correct number of records but the 2 amount fields only whole numbers.
For example, Exchange Rate should be 1.4392039 (7 decimals), it comes back as zero.
The other one is an amount which should be 286188.54 but comes back as 286188.   If it's 259.60, it returns 259.

I noticed that in the new iSeries ODBC there is a new tab called DATA TYPES.  I have played with different settings there but always get the same results.  Also played with the PRECISION settings in the SERVER/ADVANCED tab but always get the same result.

Anything you can help, would be highly appreciated.
Sub GetAS400()
    Const DQT As String = """"
    Const SQT As String = "'"
    Const AMP As String = "&"
    
    Dim strSQL1 As String
    Dim con As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim DOC As Long
    Dim ODBC As String

    
    On Error GoTo Err
    
    Dim BatchNumber As Long
    DOC = 1050866
    ODBC = "xxxxxx"
    strSQL1 = Space(2500)
    strSQL1 = "SELECT GLCO,GLDOC,GLDCT,GLJELN,GLICU,GLICUT,GLANI,GLAA,GLRE,GLCRR,GLEXA,GLEXR" & _
             " FROM PRODDTA.F0911 WHERE GLDCT = 'JE' AND GLDOC=" & DOC
    
    Err.Clear
    
    Application.Calculation = xlCalculationManual
    con.Properties("Prompt") = adPromptComplete
    con.Open "Provider = MSDASQL;DSN=" & ODBC & ";"
    
    
    Dim strSheetName As String
    strSheetName = ActiveSheet.Name
    rst.Open strSQL1, con, adOpenStatic
    Cells(1, 1).CopyFromRecordset rst
    
    Set rst = Nothing
    con.Close
    Set con = Nothing
    Cells(1, 1).Select
    
    Exit Sub
    
Err:
    ErrorNumber = Err.Number
End Sub

Open in new window

0
 
Gary PattersonVP Technology / Senior Consultant Commented:
Is there any chance that this is just a formatting issue in the spreadsheet, as opposed to a data transformation issue?  Make sure the columns are formatted properly before attempting to import data.

One other note:

con.Open "Provider = MSDASQL;DSN=" & ODBC & ";"

This is the Microsoft OLE DB Provider for ODBC.  This adds another layer to the data access stack versus using ODBC directly, or via ADO/OLEdb directly using one of the IBM-supplied OLEdb providers, which is the my preferred method.  This Redbook explains the process in detail with robust VB examples:

http://www.redbooks.ibm.com/abstracts/sg245183.html

- Gary Patterson



0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
FSOLLAuthor Commented:
Gary, thanks for the suggestion and the link.   I will review the Redbook documen tonight at home.
As I stated in the case, the program has NOT changed and I never had to format the Excel columns prior to running the code, so why would I need to now?  But no, the problem is not simply formatting.  By the time the numbers are in Excel they have already been truncated so it's already too late.  
I also forgot to mention this very interesting piece of information.......when I use the exact same ODBC and the exact same SQL statement in a MSQuery or Brio report, the numbers are correct, NO decimals dropped.
In the meantime, per your suggestion, I changed the provider part of my code (shown below) and got exactly the same results --- NO decimals.
Thanks a lot.  Any other ideas, please forward.

   Dim Userid As String
    Dim UserPassword As String
   
    ODBC = "myODBC"
    Userid = "myid"
    UserPassword = "mypswd"
    Set con = CreateObject("ADODB.Connection")
    con.ConnectionString = "DSN=" & ODBC & ";UID=" & Userid & ";PWD=" & UserPassword
    con.Open

    rst.Open strSQL1, con, adOpenStatic
    wbData.Activate
    Cells(1, 1).Select
    strSheetName = ActiveSheet.Name
    Sheets(strSheetName).Select
    Cells(1, 1).CopyFromRecordset rst
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
For example, Exchange Rate should be 1.4392039 (7 decimals), it comes back as zero.

Is this really true, or did you mean to say that it comes back as "1"?  The other example look like they are getting cast to integers, but this one just looks completely wrong:

Based on my experience with Excel automation in the past, Excel seems to "guess" how to format the data that is being imported based on the first record, or maybe the first few records.  I'm not sure what rules it uses, but I have seen things change like this in the past, especially when upgrading Excel.  Any chance that the first record contains whole numbers (or zeroes) in these columns, and that is somehow influencing how Excel handle the import?

Your code looks OK to me.  Just for the sake of completeness, what are the data types and lengths of the problem columns in F0911 (AS/400 table data types)

As to "why", I have no idea.  I'm just trying to isolate exactly what is happening here, without seeing it, and without being able to do any firsthand diagnosis.

Are you running the latest service pack for your Client Access version?  AS/400 ODBC Driver V13 is part of V6R1 Client Access:

http://www-03.ibm.com/systems/i/software/access/windows/sphist.html

Also, you need to make sure that the AS/400 host system is running the latest cumulative PTF and Database Group PTF for your OS version.

I'm also curious what happens if you explicitly cast your resulting column in SQL to DECIMAL:

Don't have access to a JDE system at the moment, but assuming GLEXR is "Exchange Rate":

strSQL1 = "SELECT GLCO,GLDOC,GLDCT,GLJELN,GLICU,GLICUT,GLANI,GLAA,GLRE,GLCRR,GLEXA,CAST(GLEXR as DECIMAL(8,7)) FROM PRODDTA.F0911 WHERE GLDCT = 'JE' AND GLDOC=" & DOC

ADO Recordset objects contain data type information for each column.  You might want to see if something is causing these data types to be reported incorrectly.  You might be able to fix the problem by explicitly casting the numeric columns.

Again, I don't know exactly why it is behaving differently now (it does appear to be a bug), but I do have some ideas on how to fix it.  The main one is to force the formatting using CAST.  The second idea is to format the target range BEFORE executing copyfromrecordset.

How do the problem columns end up formatted now?  General?  Text?  Number?

- Gary Patterson
0
 
FSOLLAuthor Commented:
Thanks Gary, great questions and suggestions.  
You're absolutely right, I meant to say that "it comes back as 1", not zero.

The field length and type for F0911 in AS400 are (in order from the SQL) A5, N8.0, A2, N7.1, N8.0, A2,A29,N15.0,A1,N1.7,A30,A30............just to clarify, the Exchange Rate is GLCRR not GLEXA.

I'm attaching a matrix with the difference in versions between working and non-working vba results.

The vba program dumps the data in a brand new/empty workbook/sheet so all columns have the default, GENERAL.  But remember, that's also true when I do it in XP that it works.

I will read the link you provided and try out your CAST suggestion and let you know how it turns out.
Thanks for everything.
 

 


0
 
FSOLLAuthor Commented:
0
 
tliottaCommented:
A5, N8.0, A2, N7.1...

I assume that the "A" items are alphanumeric and the "N" items are numeric. But what kind of 'numeric' are they?

I recall seeing mention of an issue handling one or more specific data types such as packed-decimal. Perhaps the specific types make a difference.

I'll see if I can track down a link for the article I was looking at.

Tom
0
 
FSOLLAuthor Commented:
Here's a better explanation on the fields data types:
A5, N8.0 (zoned decimal), A2, N7.1 (zoned decimal), N8.0 (zoned decimal), A2,A29,N15.0 (packed decimal),A1,N1.7 (packed decimal),A30,A30

Thanks
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
So both the packed and zoned numbers are truncated?
0
 
FSOLLAuthor Commented:
No, only the packed decimals.
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
And the zoned are ok?  If so, then just cast to zoned in the SQL and that should at least give you a workaround.
0
 
FSOLLAuthor Commented:
I tried that.  It still drops off the decimals.  
I found a workaround (actually somebody else gave me the idea, and don't even need to CAST) but I wish I didn't have to go that route.  
If I multiply the field by 10000000 (7 zeros) and then divide it by 10000000 before sending to the sheet, it works fine.  But there are 2 problems --- 1) data is being massaged twice so it takes longer and is not significant in small files but certainly as they're are larger or have a lot of columns (in other programs), and 2) Nobody has still been able to tell me WHAT IS THE EXACT PROBLEM!
I will post my latest program to see if at least there is a better way of doing the 2-pass file manipulation.

Thanks
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
Have you opened an incident with IBM on this?  If you don't report it, and it is a fairly rare issue, they may never know to fix it.  They also have support documents in Rochester that are not available on the Internet, and you may even get a quick answer to your problem if it is really an AS/400 or Client Access issue.

Before you do that, make sure you are running the latest Client Access Service Pack: it will be one of the first things that they ask you.  

I asked you this a while back, but it got lost in the mix: what Client Access service pack are you running?  It is important that you run the latest service pack, since there are numerous fixes in each.  

As you can see, there is a long list of APAR's fixed in these service packs, and several of them involve the data access components:

ftp://public.dhe.ibm.com/as400/products/clientaccess/win32/v7r1m0/servicepack/si42424/readmesp.txt
http://www-03.ibm.com/systems/i/software/access/windows/sphist.html

As for the cause, I think the answer is: "a bug in Excel, the ADO classes, the copyFromRecordSet method, the MSDASQL - OLEDB Provider for ODBC drivers, or the IBM ODBC driver".  That's why you need to make sure that all of your products are patched up to the current versions.

We can do some very low-level troubleshooting and likely isolate the specific failing component, but it is very time-consuming, and frankly, I just don't have the time to do it in a forum like this.  As a result, I say try patching everything you can patch or find quick work-around.  Then report the problem to IBM and see what they come back with.  If they come back with a fix, great.  If not, then at least you have a work-around in place in the meantime.  

- Gary Patterson
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
Just thought of another workaround that might allow you to leave your code almost completely unchanged:

Create a view over the F0911 table that casts the problem packed columns to decimal, then change the query to use the view.

- Gary Patterson

0
 
FSOLLAuthor Commented:
Gary, I wish I could call IBM.  Even though I'm an I.T. person, I am now working under the Accounting department and I have NO information available to call IBM (customer#, serial#, etc).  Unfortunately I would have to get somebody from I.T. to help me with this and right now I can't get them to even look at easier things.

As far as the Client Access version....all that is in an image from 10/19.  However, whether that is the latest SP or not, I don't know.  I realize some of this info may be available on the web but honestly I don't have time since I'm trying to come up with ways to have the vba program work for my users in the meantime.

I can't create a view over F0911.  There are already over 100 indexes over it, I don't have enough power to request it, and unfortunately I can't do it myself.

If you have any other ideas, please let me know.  Thank you so much.
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
I'm happy to continue to help you troubleshoot this, and understand that sometimes you have to operate with restrictions that make things harder.  Before I spend any more time, though, I really need you to verify that you are running the latest Client Access service pack.  

If you are in front of the machine, you can verify the version and SP in about ten seconds.1

Open up the Client Access Control Panel Applet (Contron Panel - iSeries Access or IBM i for Access for Windows Properties).  The version, release, mod level, and service pack are listed right there.  Then go here:

http://www-03.ibm.com/systems/i/software/access/windows/sphist.html

and if the service pack shown in the Control Panel app isn't the latest SP for your version, then download the latest release, download it, read the installation notes, and install it.

Can't tell you how many times simply installing the latest SP has fixed a problem like this.

Once we verify that you're at the latest code version, I'm happy to suggest some next steps.

- Gary Patterson



0
 
FSOLLAuthor Commented:
My Client Access is V7R1M0 Service Level SI00000, which is definitely NOT the latest service pack (should be SI42424).  
I am very tempted to install this myself but if something goes wrong I'm in deep trouble.  Have to go thru I.T. so I'm contacting them now and will let you know before the end of the day.
Thanks for everything.
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
Yeah, this is an unpatched installation of the base code.  You are four service packs behind.  Looking at the Readme for the latest service pack, there are a quite a few ODBC-related fixes:

ftp://public.dhe.ibm.com/as400/products/clientaccess/win32/v7r1m0/servicepack/si42424/readmesp.txt

If you still have trouble after applying the latest SP, post back and we can do some deeper diagnosis if you like.

- Gary Patterson
0
 
FSOLLAuthor Commented:
Thanks for everything Gary.
I.T. has set up a dedicated laptop for me and I will be free to play with the installation there.
WIll get back to you by Friday with a status.  I hope you won't need to help me anymore, but keeping the case opened until it's finished.
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
It would be nice if that was all it took.  Fingers crossed.

- Gary Patterson
0
 
FSOLLAuthor Commented:
I finally got the dedicated laptop with the latest SP for iSeries and ALL PROBLEMS SOLVED!!!!!!!!!!!!!!!!!
Really owe it all to you.   Couldn't have done it without you.   A MILLION THANKS.
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
Glad it worked out.  That'll teach me not to check service pack first!

- Gary Patterson
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 11
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now