Retrieve data from Oracle DB using VBA Excel containing Japanese Character

abadilala

New Member
Joined
Sep 29, 2017
Messages
27
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

VBA Code:
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:

VBA Code:
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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Forum statistics

Threads
1,144,339
Messages
5,723,801
Members
422,518
Latest member
quack_quack

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top