Retrieve data from Oracle DB using VBA Excel containing Japanese Character

[abadilala]

I am trying to fetch data from my oracle db using excel VBA. I have made the connection and successfully fetching the data, however my data containing japanese character is broken. It became something like "A14 ｿｿ: ｿｿｿ ｿ ｿｿ: ｿｿｿｿ (ｿｿｿｿｿ)"

I wonder why my data containing japanese character is broken, so I assume that I need to change the charset in the DB and I added

DBcon.Execute ("ALTER SESSION SET NLS_LANGUAGE= 'JAPANESE'")

However, it still doesnt work as expected. Is there a way to fetch the japanese character into excel sheet?

here is my code so far:

Sub Connection()
Dim DBcon As ADODB.Connection
Dim DBrs As ADODB.Recordset
Set DBcon = New ADODB.Connection
Set DBrs = New ADODB.Recordset
Dim DBHost As String
Dim DBPort As String
Dim DBsid As String
Dim DBuid As String
Dim DBpwd As String
Dim DBQuery As String
Dim ConString As String
Dim intColIndex As Integer
On Error GoTo err

DBHost = "127.0.0.1"
DBPort = "1521"
DBsid = "ORCL"
DBuid = "TEST"
DBpwd = "TEST"

ConString = "Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=" & DBHost & ")(PORT=" & DBPort & "))" & _
"(CONNECT_DATA=(SID=" & DBsid & "))); uid=" & DBuid & "; pwd=" & DBpwd & ";"

DBcon.Open (ConString) 'Connecion to DB is made
DBQuery = "SELECT * FROM MYTABLE"

DBrs.Open DBQuery, DBcon
If Not DBrs.EOF Then 'to check if any record then

    Sheets("data").Range("A2").CopyFromRecordset DBrs
    For intColIndex = 0 To DBrs.Fields.Count - 1  ' recordset fields
        Sheets("data").Cells(1, intColIndex + 1).Value = DBrs.Fields(intColIndex).Name
Next
End If

DBcon.Execute ("ALTER SESSION SET NLS_LANGUAGE= 'JAPANESE'")
DBcon.Execute DBQuery
MsgBox ("Query is successfully executed")


DBcon.Close
Exit Sub
err:
MsgBox "Following Error Occurred: " & vbNewLine & err.Description
DBcon.Close


End Sub