Q&A
実行したいのはinsert ですよね。strSQLの中身のSQLが中途半端ですが、コードはこれが全てですか？
Excelでcsvのデータを取り込みOracleのテーブルにADOでinsertしています。
その際、CommitTransで3246エラー「Connection オブジェクトは、トランザクション中に明示的に閉じることができません。」が出てしまいます。
データ数は約40000件です。
何か原因として考えられることはありますでしょうか。
以下のことを試しました。
- デバッグで1件登録 → 成功
- Execute文の前後にBeginTransとCommitTransを置いて実行 → 失敗（3246エラー発生）
VBA
1 Dim sh As Worksheet 2 Dim shMenu As Worksheet 3 4 Set sh = Worksheets("Sheet1") 5 Set shMenu = ActiveSheet 6 7 '初期化 8 sh.Range("A1").Select 9 sh.Cells.Select 10 Selection.ClearContents 11 12 'CSVの取り込み 13 With sh.QueryTables.Add(Connection:= _ 14 "TEXT;" & shMenu.Cells(21, 3).Value _ 15 , Destination:=sh.Range("A1")) 16 .Name = Mid(shMenu.Cells(21, 3).Value, InStrRev(shMenu.Cells(21, 3).Value, "\") + 1) 17 .FieldNames = True 18 .RowNumbers = False 19 .FillAdjacentFormulas = False 20 .PreserveFormatting = True 21 .RefreshOnFileOpen = False 22 .RefreshStyle = xlInsertDeleteCells 23 .SavePassword = False 24 .SaveData = True 25 .AdjustColumnWidth = True 26 .RefreshPeriod = 0 27 .TextFilePromptOnRefresh = False 28 If CInt(Application.Version) > 10 Then 29 .TextFilePlatform = 932 30 Else 31 .TextFilePlatform = xlWindows 32 End If 33 .TextFileStartRow = 1 34 .TextFileParseType = xlDelimited 35 .TextFileTextQualifier = xlTextQualifierDoubleQuote 36 .TextFileConsecutiveDelimiter = False 37 .TextFileTabDelimiter = False 38 .TextFileSemicolonDelimiter = False 39 .TextFileCommaDelimiter = True 40 .TextFileSpaceDelimiter = False 41 .TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 1, 2, 2, 2, 1, 1, 1, 1, 1, 1, _ 42 1, 2, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _ 43 , 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 1, 1, 2, 1, 2) 44 If CInt(Application.Version) > 10 Then 45 .TextFileTrailingMinusNumbers = True 46 End If 47 .Refresh BackgroundQuery:=False 48 End With 49 sh.Range("A1").Select 50 sh.Cells.Select 51 Selection.QueryTable.Delete 52 sh.Range("A1").Select 53 54 Dim cnn As New ADODB.Connection 55 56 cnn.Open ConStr, ConID, ConPwd 57 58 Dim strSQL As String 59 60 Dim lngRow As Long 61 62 cnn.BeginTrans 63 For lngRow = 2 To MaxRow 64 If sh.Cells(lngRow, 1).Value = vbNullString Then 65 Exit For 66 End If 67 strSQL = "insert into table1(COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10, COL11, COL12, COL13, COL14, COL15, COL16, COL17, COL18, COL19, COL20, COL21, COL22, COL23, COL24, COL25, COL26, COL27, COL28, COL29, COL30, COL31, COL32, COL33, COL34, COL35, COL36, COL37, COL38, COL39, COL40, COL41, COL42, COL43, COL44, COL45, COL46, COL47, COL48, COL49, COL50, COL51, COL52, COL53, COL54, COL55, COL56, COL57, COL58, COL59, COL60, COL61, COL62, COL63, COL64, COL65, COL66, COL67, COL68, COL69, COL70, COL71, COL72, COL73, COL74, COL75, COL76, COL77, COL78, COL79, COL80, COL81, COL82, COL83, COL84, COL85, COL86, COL87, COL88, COL89, COL90, COL91, COL92, COL93, COL94, COL95, COL96, COL97, COL98, COL99, COL100, COL101, COL102, COL103, COL104, COL105, COL106, COL107, COL108, COL109, COL110, COL111, COL112, COL113)values(" & _ 68 Trim(sh.Cells(lngRow, 1).Value) & ", '" & Trim(sh.Cells(lngRow, 2).Value) & "', " & Trim(sh.Cells(lngRow, 3).Value) & ", '" & Trim(sh.Cells(lngRow, 4).Value) & "', '" & Trim(sh.Cells(lngRow, 5).Value) & "', '" & Trim(sh.Cells(lngRow, 6).Value) & "', '" & Trim(sh.Cells(lngRow, 7).Value) & "', '" & Trim(sh.Cells(lngRow, 8).Value) & "', '" & Trim(sh.Cells(lngRow, 9).Value) & "', '" & Trim(sh.Cells(lngRow, 10).Value) & "', '" & _ 69 Trim(sh.Cells(lngRow, 11).Value) & "', '" & Trim(sh.Cells(lngRow, 12).Value) & "', '" & Trim(sh.Cells(lngRow, 13).Value) & "', '" & Trim(sh.Cells(lngRow, 14).Value) & "', '" & Trim(sh.Cells(lngRow, 15).Value) & "', '" & Trim(sh.Cells(lngRow, 16).Value) & "', '" & Trim(sh.Cells(lngRow, 17).Value) & "', '" & Trim(sh.Cells(lngRow, 18).Value) & "', '" & Trim(sh.Cells(lngRow, 19).Value) & "', '" & Trim(sh.Cells(lngRow, 20).Value) & "', '" & _ 70 Trim(sh.Cells(lngRow, 21).Value) & "', '" & Trim(sh.Cells(lngRow, 22).Value) & "', '" & Trim(sh.Cells(lngRow, 23).Value) & "', '" & Trim(sh.Cells(lngRow, 24).Value) & "', '" & Trim(sh.Cells(lngRow, 25).Value) & "', " & Trim(sh.Cells(lngRow, 26).Value) & ", '" & Trim(sh.Cells(lngRow, 27).Value) & "', '" & Trim(sh.Cells(lngRow, 28).Value) & "', '" & Trim(sh.Cells(lngRow, 29).Value) & "', '" & Trim(sh.Cells(lngRow, 30).Value) & "', '" & _ 71 Trim(sh.Cells(lngRow, 31).Value) & "', '" & Trim(sh.Cells(lngRow, 32).Value) & "', '" & Trim(sh.Cells(lngRow, 33).Value) & "', '" & Trim(sh.Cells(lngRow, 34).Value) & "', '" & Trim(sh.Cells(lngRow, 35).Value) & "', " & Trim(sh.Cells(lngRow, 36).Value) & ", '" & Trim(sh.Cells(lngRow, 37).Value) & "', '" & Trim(sh.Cells(lngRow, 38).Value) & "', " & Trim(sh.Cells(lngRow, 39).Value) & ", " & Trim(sh.Cells(lngRow, 40).Value) & ", " & _ 72 Trim(sh.Cells(lngRow, 41).Value) & ", " & Trim(sh.Cells(lngRow, 42).Value) & ", " & Trim(sh.Cells(lngRow, 43).Value) & ", " & Trim(sh.Cells(lngRow, 44).Value) & ", " & Trim(sh.Cells(lngRow, 45).Value) & ", " & Trim(sh.Cells(lngRow, 46).Value) & ", " & Trim(sh.Cells(lngRow, 47).Value) & ", " & Trim(sh.Cells(lngRow, 48).Value) & ", " & Trim(sh.Cells(lngRow, 49).Value) & ", " & Trim(sh.Cells(lngRow, 50).Value) & ", " & _ 73 Trim(sh.Cells(lngRow, 51).Value) & ", '" & Trim(sh.Cells(lngRow, 52).Value) & "', " & Trim(sh.Cells(lngRow, 53).Value) & ", " & Trim(sh.Cells(lngRow, 54).Value) & ", " & Trim(sh.Cells(lngRow, 55).Value) & ", " & Trim(sh.Cells(lngRow, 56).Value) & ", " & Trim(sh.Cells(lngRow, 57).Value) & ", " & Trim(sh.Cells(lngRow, 58).Value) & ", " & Trim(sh.Cells(lngRow, 59).Value) & ", " & Trim(sh.Cells(lngRow, 60).Value) & ", " & _ 74 Trim(sh.Cells(lngRow, 61).Value) & ", '" & Trim(sh.Cells(lngRow, 62).Value) & "', '" & Trim(sh.Cells(lngRow, 63).Value) & "', " & Trim(sh.Cells(lngRow, 64).Value) & ", " & Trim(sh.Cells(lngRow, 65).Value) & ", '" & Trim(sh.Cells(lngRow, 66).Value) & "', '" & Trim(sh.Cells(lngRow, 67).Value) & "', '" & Trim(sh.Cells(lngRow, 68).Value) & "', '" & Trim(sh.Cells(lngRow, 69).Value) & "', '" & Trim(sh.Cells(lngRow, 70).Value) & "', " & _ 75 Trim(sh.Cells(lngRow, 71).Value) & ", " & Trim(sh.Cells(lngRow, 72).Value) & ", " & Trim(sh.Cells(lngRow, 73).Value) & ", " & Trim(sh.Cells(lngRow, 74).Value) & ", '" & Trim(sh.Cells(lngRow, 75).Value) & "', '" & Trim(sh.Cells(lngRow, 76).Value) & "', '" & Trim(sh.Cells(lngRow, 77).Value) & "', " & Trim(sh.Cells(lngRow, 78).Value) & ", '" & Trim(sh.Cells(lngRow, 79).Value) & "', '" & Trim(sh.Cells(lngRow, 80).Value) & "', '" & _ 76 Trim(sh.Cells(lngRow, 81).Value) & "', '" & Trim(sh.Cells(lngRow, 82).Value) & "', " & Trim(sh.Cells(lngRow, 83).Value) & ", " & Trim(sh.Cells(lngRow, 84).Value) & ", '" & Trim(sh.Cells(lngRow, 85).Value) & "', '" & Trim(sh.Cells(lngRow, 86).Value) & "', " & Trim(sh.Cells(lngRow, 87).Value) & ", " & Trim(sh.Cells(lngRow, 88).Value) & ", '" & Trim(sh.Cells(lngRow, 89).Value) & "', " & Trim(sh.Cells(lngRow, 90).Value) & ", " & _ 77 Trim(sh.Cells(lngRow, 91).Value) & ", '" & Trim(sh.Cells(lngRow, 92).Value) & "', '" & Trim(sh.Cells(lngRow, 93).Value) & "', " & Trim(sh.Cells(lngRow, 94).Value) & ", " & Trim(sh.Cells(lngRow, 95).Value) & ", '" & Trim(sh.Cells(lngRow, 96).Value) & "', '" & Trim(sh.Cells(lngRow, 97).Value) & "', " & Trim(sh.Cells(lngRow, 98).Value) & ", " & Trim(sh.Cells(lngRow, 99).Value) & ", '" & Trim(sh.Cells(lngRow, 100).Value) & "', '" & _ 78 Trim(sh.Cells(lngRow, 101).Value) & "', " & Trim(sh.Cells(lngRow, 102).Value) & ", " & Trim(sh.Cells(lngRow, 103).Value) & ", '" & Trim(sh.Cells(lngRow, 104).Value) & "', " & Trim(sh.Cells(lngRow, 105).Value) & ", '" & Trim(sh.Cells(lngRow, 106).Value) & "', '" & Trim(sh.Cells(lngRow, 107).Value) & "', " & Trim(sh.Cells(lngRow, 108).Value) & ", " & Trim(sh.Cells(lngRow, 109).Value) & ", " & Trim(sh.Cells(lngRow, 110).Value) & ", " & _ 79 Trim(sh.Cells(lngRow, 111).Value) & ", '" & Trim(sh.Cells(lngRow, 112).Value) & "', '" & Trim(sh.Cells(lngRow, 113).Value) & "')" 80 81 cnn.Execute strSQL 82 Next lngRow 83 cnn.CommitTrans 84 cnn.Close 85 Set cnn = Nothing
ADODB.Connection.Openメソッドで与えている接続文字列を差し支えない範囲で示してほしいのと、ODBCにOracleデータベースへの接続を定義していると思うので設定画面の画像を示してほしいところでしょうか。
コピペミスでINSERT INTO テーブル名が抜けていました。
>ADODB.Connection.Openメソッドで与えている接続文字列は「Provider=MSDAORA;Data Source=サーバー名」です。ODBCは使っておらずinstant clientを使っています。
回答4件
0
自己解決
皆様ご回答ありがとうございました。
MaxRowが65536になっていた為余計なSQL文をExecuteしてしまっていたようです。
For文の終了時に条件でFor文を抜けるようにしたところ上手くいきました。
VBA
1'・・・ 2'・・・ 3'・・・ 4 cnn.Execute strSQL 5 If sh.Cells(lngRow + 1, 1).Value = vbNullString Then 6 Exit For 7 End If 8 Next lngRow 9cnn.CommitTrans 10 cnn.Close 11 Set cnn = Nothing
投稿2018/04/26 01:55
総合スコア29
余計なSQL文で実はエラーになっていたということですか？
実際は約40000件しかないのでそれ以上のセルは空なのでsh.Cells(lngRow, 1).Valueで取得した文字は空文字で「insert into table1(COL1, COL2, ・・・)values(,'', ・・・)」というようなExecute文が実行されていたようです。でもよくよく見たらFor文の先頭に「If sh.Cells(lngRow, 1).Value = vbNullString Then」がありますね。あれ？？
saziさん
m6uさん
ttyp03さん
コピペミスがありました。For文の先頭をなぜか
If sh.Cells(lngRow, 1).Value = vbNullString Then
cnn.Close
Exit For
End If
にしていました。過去の自分がなぜか加えた方がいいと思ったようです。
ご面倒お掛け致して申し訳ございません。
開始したトランザクションを終了せずに、コネクションをクローズするロジックがあったということですね。納得しました。
0
ヒント：Debug.Print strSQL
INSERT INTO テーブル名
に該当する文言がstrSQLに含まれてない不完全なSQLをCommitTransしようとしても、ね。
投稿2018/04/25 06:38
編集2018/04/25 06:39退会済みユーザー
総合スコア0
あなたの回答
tips
プレビュー
