excelで’付きデータのoracle登録

質問

office2010 oracle12c

下記のexcelマクロで文字列の中に'があるとoracleのテーブルにデータ登録できません。
Option Explicit

' ADOのオブジェクト変数を宣言
Private cnn As New ADODB.Connection
Private cmd As New ADODB.Command
Sub add()

On Error GoTo err_hdr

Sheets("table").Select

' ' Oracleの接続
cnn.Open "Provider=OraOLEDB.Oracle;" & _
"Data Source=Source_name;", "ID", "PW"
Set cmd.ActiveConnection = cnn

'追加の例

Dim row As Long

row = 1

Do Until ActiveSheet.Cells(row, 1) = ""

With cmd

.CommandText = "insert into table " & " (name1, name2)" & "" _
& "VALUES('" & ActiveSheet.Cells(row, 1) & "','" & ActiveSheet.Cells(row, 2) & "')"

.CommandType = adCmdUnknown
.Execute

End With
row = row + 1

Loop

cnn.Close

Set cmd = Nothing
Set cnn = Nothing
Exit Sub
err_hdr:
Resume Next

End Sub

具体的には
name2のデータがPEOPLEだと上記マクロでoracleのtableにデータ登録できますが、
name2のデータがPEOPLE'Sの様に'がついているとデータ登録できません。
’付きの文字列を、上記マクロ修正してデータ登録可能でしょうか？

回答No.3

HohoPapa · ベストアンサー

https://oracle.programmer-reference.com/sql-singlequotation/
の対応、つまり、
.CommandText を組み立てる前に
name2 = Replace(name2, "'", "''")
を実行します。

お礼

回答ありがとうございます。
教えていただいた内容が参考になりデータ追加できました。

name2はテーブルの項目名になるので、対応としては、excel帳票の中で
PEOPLE'S→PEOPLE''S
と置換しました。
’が使用されている文字列が多かったら、マクロで対応とも考えましたが、この文字列１ケだけが’付きだったので上記の様に置換で対応しました。
excelからoracleへのデータ追加マクロは他にもあり、そちらは'付きでもデータ追加できていたのですが、処理時間が遅いので、今回事例であげたマクロで対応出来て時間短縮となり、非常に助かりました。(^^)/

回答No.2

maiko04

文法がわからんので
.CommandText = 'insert into table ' & ' (name1, name2)' & '' _
& 'VALUES("' & ActiveSheet.Cells(row, 1) & '","' & ActiveSheet.Cells(row, 2) & '")'
か
.CommandText = "insert into table " & " (name1, name2)" & "" _
& "VALUES(\'" & ActiveSheet.Cells(row, 1) & "\','" & ActiveSheet.Cells(row, 2) & "')"
か
.CommandText = "insert into table " & " (name1, name2)" & "" _
& "VALUES(''" & ActiveSheet.Cells(row, 1) & "'','" & ActiveSheet.Cells(row, 2) & "')"

あたりでうまくいきませんか？

お礼

回答ありがとうございます。
3件試しましたが、３件ともだめでした。

回答No.1

oboroxx

Oracleでは、'は文字列を囲むときに使われる文字なので、エスケープが必要だと思います。
'を\'とバックスラッシュ（日本語環境では円記号かも）を前に付けてください。

お礼

回答ありがとうございます。
'を\'とバックスラッシュ（日本語環境では円記号）を前に付ける対応ではだめでした。