ADO Connection を再利用する方法

質問

【環境】
OS:Windows Server 2003
DB:SQL Server 2005
言語:VB .NET 2005

現在、Windows2000Server + SQLServer2000 + VB6の環境を
上記の環境に移行しております。

ADO Connectionの再利用で
「手動または分散トランザクションモードのため、新規接続を作成できません」
というエラーが発生します。

いろいろと検索してみたのですが、解決せず、お力をお貸しいただきたいと思って投稿させていただきます。

現在のコードは、
--------------------------------------------
Public adoCn As ADODB.Connection
Public adoRs As ADODB.Recordset

Public Sub pfAutoClosed()

　If fGet印刷ジャーナルファイルRecordset() = True Then
　　　'*********************
　　　'トランザクションの開始
　　　'*********************
　　　adoCn.BeginTrans()

　　　'印刷ジャーナルファイルのロック
　　　sMySQL = "SELECT * FROM 印刷ジャーナルファイル WITH (TABLOCKX)"
　　　adoCn.Execute(sMySQL, , ADODB.CommandTypeEnum.adCmdText)

　　　'職員表マスタのロック
　　　sMySQL = "SELECT * FROM 職員表マスタ WITH (TABLOCKX)"
　　　adoCn.Execute(sMySQL, , ADODB.CommandTypeEnum.adCmdText)　<---- ここでエラー発生

　　　adoRs.MoveFirst()
　　　Do Until adoRs.EOF
　　　　　iKubun = nz(adoRs.Fields("区分").Value)
　　　　　'*****************
　　　　　'１．職員表マスタの作成
　　　　　'*****************
　　　　　'SQLステートメントを作成
　　　　　sMySQL = "INSERT INTO 職員表マスタ （～以下省略）"
　　　　　adoCn.Execute(sMySQL, , ADODB.CommandTypeEnum.adCmdText)

　　　　　'**************************
　　　　　'２．更新区分に更新フラグを設定
　　　　　'**************************
　　　　　sMySQL = "UPDATE 印刷ジャーナルファイル " & "SET 更新区分 = 1 （～以下省略）"
　　　　　adoCn.Execute(sMySQL, , ADODB.CommandTypeEnum.adCmdText)

　　　　　adoRs.MoveNext()
　　　Loop

　　　adoCn.CommitTrans() 'トランザクション処理のコミット
　　　adoRs.Close()
　　　adoRs = Nothing
　endif
End Sub

'*****************************************
'印刷ジャーナルファイルのレコードセットを取得
'*****************************************
Private Function fGet印刷ジャーナルファイルRecordset() As Boolean

　　adoRs = New ADODB.Recordset
　　With adoRs
　　　　.let_Source("SELECT * FROM 印刷ジャーナルファイル " & "WHERE 発行区分 = 1 AND 更新区分 = 0")
　　　　.let_ActiveConnection(adoCn)
　　　　.CursorType = ADODB.CursorTypeEnum.adOpenKeyset
　　　　.LockType = ADODB.LockTypeEnum.adLockPessimistic
　　　　.Open()
　　End With

　　If adoRs.EOF Then
　　　　fGet印刷ジャーナルファイルRecordset = False
　　Else
　　　　fGet印刷ジャーナルファイルRecordset = True
　　End If

End Function
--------------------------------------------

となっています。
pfAutoClosed関数内で、同じadoCnを使用しての２度目のSELECT文が実行できません。

ADO Connectionの再利用ができないということで、「Close」をしてくださいという記述を参考にもしたのですが、解決しておりません。
トランザクション開始位置も変更してみたのですが、だめでした。

VB->VB.NETへのアップグレードウィザードを使用したのですが、移行以前のVB6のバージョンではエラーなく動作します。

間違っている点をご指南いただければと思います。
よろしくお願いいたします。

回答No.1

kero_mio · ベストアンサー

問題の原因だと思われる箇所が２つあります。

・fGet印刷ジャーナルファイルRecordsetで定義しているadoRsの
　レコードセットがCloseされていない

・adoCn.BeginTrans()の開始が早すぎる。
　adoCn.Execute(sMySQL, , ADODB.CommandTypeEnum.adCmdText)の
　後に、adoCn.BeginTrans()すべきです。

なので、纏めると

Public adoCn As ADODB.Connection
Public adoRs As ADODB.Recordset

Public Sub pfAutoClosed()

　If fGet印刷ジャーナルファイルRecordset() = True Then

　　　'印刷ジャーナルファイルのロック
　　　sMySQL = "SELECT * FROM 印刷ジャーナルファイル WITH (TABLOCKX)"
　　　adoCn.Execute(sMySQL, , ADODB.CommandTypeEnum.adCmdText)

　　　'職員表マスタのロック
　　　sMySQL = "SELECT * FROM 職員表マスタ WITH (TABLOCKX)"
　　　adoCn.Execute(sMySQL, , ADODB.CommandTypeEnum.adCmdText)　<---- ここでエラー発生

　　　adoRs.MoveFirst()

　　　'トランザクションの開始は、ここから行う
　　　adoCn.BeginTrans()

　　　Do Until adoRs.EOF
　　　　　iKubun = nz(adoRs.Fields("区分").Value)
　　　　　'*****************
　　　　　'１．職員表マスタの作成
　　　　　'*****************
　　　　　'SQLステートメントを作成
　　　　　sMySQL = "INSERT INTO 職員表マスタ （～以下省略）"
　　　　　adoCn.Execute(sMySQL, , ADODB.CommandTypeEnum.adCmdText)

　　　　　'**************************
　　　　　'２．更新区分に更新フラグを設定
　　　　　'**************************
　　　　　sMySQL = "UPDATE 印刷ジャーナルファイル " & "SET 更新区分 = 1 （～以下省略）"
　　　　　adoCn.Execute(sMySQL, , ADODB.CommandTypeEnum.adCmdText)

　　　　　adoRs.MoveNext()
　　　Loop

　　　adoCn.CommitTrans() 'トランザクション処理のコミット
　　　adoRs.Close()
　　　adoRs = Nothing
　endif
End Sub

'*****************************************
'印刷ジャーナルファイルのレコードセットを取得
'*****************************************
Private Function fGet印刷ジャーナルファイルRecordset() As Boolean

　　adoRs = New ADODB.Recordset
　　With adoRs
　　　　.let_Source("SELECT * FROM 印刷ジャーナルファイル " & "WHERE 発行区分 = 1 AND 更新区分 = 0")
　　　　.let_ActiveConnection(adoCn)
　　　　.CursorType = ADODB.CursorTypeEnum.adOpenKeyset
　　　　.LockType = ADODB.LockTypeEnum.adLockPessimistic
　　　　.Open()
　　End With

　　If adoRs.EOF Then
　　　　fGet印刷ジャーナルファイルRecordset = False
　　Else
　　　　' 必ずCloseする
　　　　adoRs.Close()
　　　　fGet印刷ジャーナルファイルRecordset = True
　　End If

　　' Nothingを必ず入れる
　　Set adoRs = Nothing
　　' または、 Set文なしで、adoRs = NothingでもOKかも？

End Function

ご参考になれば、幸いです。

補足

回答ありがとうございます。
参考にさせていただき、試してみました。

１．fGet印刷ジャーナルファイルRecordsetで定義しているadoRsの
　レコードセットがCloseされていない
　　→これについてはadoRsの値をpfAutoClosed()で使用している
　　　ため、fGet印刷ジャーナルファイルRecordset()内でClose
　　　することができません。
　　　実際には、pfAutoClosed()のEnd Sub３行前でCloseして
　　　います。

２．adoCn.BeginTrans()の位置を変更する
　　→adoCn.Execute(sMySQL, , ADODB.CommandTypeEnum.adCmdText)
　　　の後にadoCn.BeginTrans()を変更した場合、
　　　一つ目のadoCn.Execute(sMySQL, , ADODB.CommandTypeEnum.adCmdText)
　　　のところでフリーズしてしまい動かなくなります。

ややこしくて申し訳ありませんが、見落としている点ありましたらご教授ください。

それと、印刷ジャーナルファイルと職員表マスタのロックが必要なのですが、このようなSELECT文の発行以外での実現方法はあるのでしょうか。

回答No.2

kero_mio

＞１．fGet印刷ジャーナルファイルRecordsetで定義しているadoRsの
＞レコードセットがCloseされていない
＞　　→これについてはadoRsの値をpfAutoClosed()で使用している
＞　　　ため、fGet印刷ジャーナルファイルRecordset()内でClose
＞　　　することができません。
＞　　　実際には、pfAutoClosed()のEnd Sub３行前でCloseして
＞　　　います。

どういうトランザクション単位で、処理したいのでしょうか？
まずはそこからですね。
別に、adoRsは、pfAutoClosed()用と、fGet印刷ジャーナルファイルRecordset()用と
２種類のRecordsetを定義しても問題はないですよね？
また、Connection（adoCn）についても同じことです。

「○○で使用しているからできません」ではなく、
「トランザクションの単位」がこうなっているから、ここは別けられないと
いう説明がない限り、具体的な解決案を出せませんので、
SELECT系と更新（INSERT, UPDATE, DELETE)のトランザクションは
どういうくくりで処理したいのでしょうか？
そこを補足ください。

＞２．adoCn.BeginTrans()の位置を変更する
＞　　→adoCn.Execute(sMySQL, , ADODB.CommandTypeEnum.adCmdText)
＞　　　の後にadoCn.BeginTrans()を変更した場合、
＞　　　一つ目のadoCn.Execute(sMySQL, , ADODB.CommandTypeEnum.adCmdText)
＞　　　のところでフリーズしてしまい動かなくなります。

フリーズするとは、どのような現象でしょうか？どのようなエラーが出ますか？
また、先ほども申し上げた通り、処理したいトランザクションの単位がわからなければ、
adoCn.BeginTrans()　の書く位置もまた変わってきますので、
「処理したいトランザクション単位」を教えてください。

ただ、見ている限り、SELECT系と更新系のConnection(adoCnを２種類）と
トランザクション(adoCnを２種類用意するとトランザクションは別けられます）は
わけてしまっても問題無いようには、見えます。

もう少し情報を精査して頂き、「エラーになるから」ではなく、最終的に何ができればいいのか？
の情報をお寄せ頂けると幸いです。

よろしくお願いします。