Solved

How do I transfer in VBA Chinese Unicode chars inside SAP fields to Access Table fields, now the Chinese chars are replaced by # char.

Posted on 2008-06-25
12
2,706 Views
Last Modified: 2013-12-08
I'm using VBA from within MS Access 2003 to copy dynamically tables and table contents via a generic SAP RFC Fucntion Module : 'RFC_READ_TABLE'. All works fine, apart from fields containing real unicode characters, than these are converted into # and stored like that in the DB table.

The fields(names) to be filled are dynamically entered, as well as the field content.

The code also caters for the fact in some fields we have single quotes. so in that case the field value in the SQL INSERT string is enclosed in double quotes.

Anyone able to help ? We're going live this weekend with SAP in China region and this is to create reconcilliation reports. Overlooked item ... :(

Any other solution, not using an SQL INSERT statement is fine as well, as I'm not a real VBA programmer, just a gifted amateur, I stick to solutions which I can get working, not always the best or smartest ones ...

Thanks.

Geert

' fill first part of the SQL insert statement

    strSQL1 = "INSERT INTO " + pTableName + " ( "

    For Each xField In TFIELDS.Rows

        strSQL1 = strSQL1 + xField("FieldName") + ", "

    Next

    strSQL1 = Left(strSQL1, Len(strSQL1) - 2) + " ) "

    

    ' Fill second part of the SQL INSERT statement and do insert for each record

    With tdfNew

        For Each ROW In TDATA.Rows

            strSQL2 = "'"

            If InStr(ROW(1), "'") <> 0 Then

                ' single quote in one of the fields, convert

                For Each xField In TFIELDS.Rows

                    FieldValue = Trim(Mid(ROW(1), xField("OFFSET") + 1, xField("LENGTH")))

                    strSQL2 = Left(strSQL2, Len(strSQL2) - 1) & """" & [FieldValue] & """" & ", '"

                Next

              Else

                ' no single quote, easy case

                For Each xField In TFIELDS.Rows

                    FieldValue = Trim(Mid(ROW(1), xField("OFFSET") + 1, xField("LENGTH")))

                    strSQL2 = strSQL2 & [FieldValue] & "', '"

                Next

            End If

            ' add record

            strSQLR = strSQL1 & "VALUES(" & Left(strSQL2, Len(strSQL2) - 3) & ") ;"

            CurrentDb.Execute strSQLR, dbFailOnError

        Next

    End With

Open in new window

0
Comment
Question by:netweaver04s
  • 6
  • 5
12 Comments
 
LVL 7

Expert Comment

by:gnurl
Comment Utility
Just a question, which kind of RFC connection do you use? Do you call a RFC connection defined in SM59?

If yes, please check on tab MDMP if it is UNICODE connection.

Good luck gnurl
0
 

Author Comment

by:netweaver04s
Comment Utility
gnurl,
Thanks.
This is MS Access calling SAP, so no need to create RFC Destinations in SM59.
The main code for the function module (RFC_READ_TABLE) is SAPLSDTX. And that one has the Unicode checks active, so that's ok.

Anymore ideas anyone ?

Cheers,
Geert
0
 
LVL 7

Expert Comment

by:gnurl
Comment Utility
Just an other question, why dont you access the database directly?

gnurl
0
 
LVL 7

Expert Comment

by:gnurl
Comment Utility
By the way, the unicode flag in the properties of the program means, that the program itself is checked for unicode compatiblity, not the data processes by the program.

gnurl
0
 

Author Comment

by:netweaver04s
Comment Utility
Hi gnurl :)

I had that already in mind, but Basis is outsourced and they don't want to give DB level access :)
On other projects I did straight ODBC select calls to the Oracle DB ... Sweet and very fast :)

On this one I need to use the front door, proper RFC's ...
It's an ECC 6 box, I suppose this RFC has grown up along side the other applications.

OSS note  758278 is supposed to fix the Unicode issue and is not applicable anymore (=fixed) in a NW04s box.

That being said, I see lots of messages on SDN for Unicode and this F.M. :) But I was hoping that I did something stupid on the VBA side.

Cheers,
Geert

0
 
LVL 27

Expert Comment

by:jjafferr
Comment Utility
I would use an intermediate software in between, which reads the unicode correctly, then transfer the data back to the Table.
Notepad++ (open source) is able to read with different formats.

I once used it to correct the data for an EE Q.

jaffer
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:netweaver04s
Comment Utility
to Jjafferr,

That's not an option as this must be an easy user-side tool. With a one touch operation.
We were using straight SE16 downloads and then manual uploads in Access but that's too cumbersome and manual labour intensive to make it workable for lots of extracts. But that solution is still possible if needed, eg. for Material Master text downloads.

For the moment, the Integration Mgmnt Team is happy with the current status and they accept the limits (aka no Chinese characters). As most of the verification will be done on numbers, they don't think it's an issue.
Just for my own sake I wanted to get this problem bottomed out and make it truely universal

Geert
0
 
LVL 7

Expert Comment

by:gnurl
Comment Utility
Could I hava a look at the source you use to call the rfc / create the connection?
I could not find it in the source above...

gnurl
0
 

Author Comment

by:netweaver04s
Comment Utility
Gnurl,

I don't see any option to specify codepages or character handling in any of the classes involved.
Anyway, I included the relevant code snippets below.

Cheers,
Geert


Dim RFC_READ_TABLE
 

Dim LogonControl 'As SAPLogonCtrl.SAPLogonControl

Dim conn 'As SAPLogonCtrl.Connection

Dim funcControl 'As SAPFunctionsOCX.SAPFunctions

Dim TableFactoryCtrl  'As SAPTableFactoryCtrl.SAPTableFactory
 

....

  ' set SAP objects

  Set LogonControl = CreateObject("SAP.LogonControl.1")

  Set funcControl = CreateObject("SAP.Functions")

  Set TableFactoryCtrl = CreateObject("SAP.TableFactory.1")

....  

  Set RFC_READ_TABLE = funcControl.Add("RFC_READ_TABLE")

....  

  Set eQUERY_TAB = RFC_READ_TABLE.Exports("QUERY_TABLE")

  Set eDELIMITER = RFC_READ_TABLE.Exports("DELIMITER")

  Set eROWSKIPS = RFC_READ_TABLE.Exports("ROWSKIPS")

  Set eROWCOUNT = RFC_READ_TABLE.Exports("ROWCOUNT")

  Set eNODATA = RFC_READ_TABLE.Exports("NODATA")

....    

  Set TOPTIONS = RFC_READ_TABLE.Tables("OPTIONS")

  Set TDATA = RFC_READ_TABLE.Tables("DATA")

  Set TFIELDS = RFC_READ_TABLE.Tables("FIELDS")

....

.......... Filling of the TOPTIONS and TFIELDS tables

....

  eQUERY_TAB.Value = pQueryTab 
 

...

  Label91.Caption = "Downloading data from SAP"

  Me.Repaint

  ' check if call to SAP was successfull

  If RFC_READ_TABLE.Call <> True Then

    ' SAP call failed

    MsgBox (RFC_READ_TABLE.exception)

    conn.Logoff

    Exit Sub

  End If

Open in new window

0
 
LVL 7

Accepted Solution

by:
gnurl earned 500 total points
Comment Utility
Do you have access to the SAP-net? please check the notes 758278 - RFC_READ_TABLE and row size and 382318 - RFC_READ_TABLE

... just some short snipets ...

382318 - RFC_READ_TABLE
The scope of function module RFC_READ_TABLE is very restricted. E.g., due to the fixed maximum row size only smaller tables can be accessed. There are several data types that cannot be handled, and also there are doubts regarding the Unicode compatibility of the function module.
...
Do not use function module RFC_READ_TABLE in the first place!

-----------------------
758278 - RFC_READ_TABLE and row size
Function Module does not work in Unicode systems.

Please Note warning:
This function module is only for internal purpose !
Using this function module is at customers own risk and customer is responsible for adjusting this code to his system.
No warrenty is given on this Functionmodule RFC_READ_TABLE.
...
Symptons:
Due to Unicode compatibility and no support on the function module RFC_READ_TABLE
the standard RFC_READ_TABLE does not function any more and causes short dumps.
...

So its your own fault that you use this function and get # for unicode signs.... ;)

Sorry, they do not write which other access can be used.

gnurl


0
 
LVL 7

Assisted Solution

by:gnurl
gnurl earned 500 total points
Comment Utility
You could try to use RFC_GET_TABLE_ENTRIES but this function is not released for customers and there are unicode issues, too. You should use basis package 18 for 6.40 or basis package 9 for 7.0 at least..

gnurl

0
 

Author Comment

by:netweaver04s
Comment Utility
gnurl,
You're right, When I was initially investigating the issue and searched OSS for clues,  I came acrosse that note. But II was wrongly reading the validity period for the OSS note. I supposed dthe code was already in, didn't verify. Go-live stress.

After reading it again, it was very clear this note was valid for ALL releases. And after applying the note, the main shortcomings (Unicode and Long data record) of the RFC should be handled. There was no way I would get that pushed through in go-live weekend so I just went ahead without the chinese chars.

But I guess you persistence got me into looking into it again and I'll certainly for other projects get this note applied first. thing :)

Cheers,
Geert
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Hello All, In previous article we used Hortonworks sandbox to work with Hadoop. Now, lets think to create own single node Hadoop on Linux. Here we Install and Configure Apache Hadoop on UI based Oracle Linux. I assume, you have VMware installe…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now