none
Excel VBAによるADOレコードセットの排他的ロックについて RRS feed

 > 

  • 質問

  • Question
    サインインして投票
    0
    サインインして投票

    Win10、Excel2013の環境で、Excel VBAを使って、ADOを介して、
    accessのデータを更新するプログラムを開発しています。

    この時のADOレコードセットの排他的ロックについて質問します。

    次のコードを書き、実際に２つのExcelブックから同じレコードの更新を試みると、
    レコードセットオープン時に排他的ロック（adLockPessimistic）を指定したにも関わらず、ロックがかかりません。
    どちらのExcelブックからもレコードにアクセスできてしまいます。

    Dim con As New ADODB.CONNECTION
    Dim rs As New ADODB.Recordset
    Dim constr As String
    Dim rc As Integer

    constr = "provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & target_file

    con.Open ConnectionString:=constr

    rs.Open Source:=target_table, ActiveConnection:=con, CursorType:=adOpenDynamic, LockType:=adLockPessimistic

    rs.Find Criteria:="ID=" & id

    rs!ダミー = rs!ダミー  '★ここから排他的ロックをかけたいけど、かからない。

    rs!社員番号 = employee_code
    rs!氏名 = name
    ～

    rc = MsgBox "更新しますか？"
    if rc = vbYes then
    rs.Update
    else
    rs.CancelUpdate
    end if

    ネットで調べたところ、次のような記事があり、Data Shaping Service の機能により、
    排他的ロック（adLockPessimistic）が共有的ロック (Optimistic Lock)に
    強制変換されるという情報を入手しました。

    http://www5f.biglobe.ne.jp/~f-lap/tips_adolock.htm

    上記記事では、おそらくAccess VBAで解決した様子ですが、
    同じことをExcel VBAで行うことは可能でしょうか？
    コネクションオープン時に何か指定する必要があると考えています。

    どなたかご存知の方がおられましたら、回答して頂けると、大変助かります。

    2018年12月9日 8:09
    |

回答

  • Question
    サインインして投票
    1
    サインインして投票
    但し接続先が Access データベースである場合、そのままだとレコードのロックがかかるタイミングは「レコードセットのいずれかのフィールドに値が代入された時」ではなく、その後の「 Update メソッドが実行された時」となります。

    それは adLockOptimistic の場合ですよね? adLockPessimistic なら、編集開始時にロックが開始されますよ。

    以下実験コード。
    TBL 表は、主キー列 COL1 (数値型) と、テキスト列 COL2 を持っており、
    COL1=10、COL2="Old Value" なレコードが登録されている状態で開始します。

    Set cn1 = New ADODB.Connection
cn1.Provider = "Microsoft.ACE.OLEDB.12.0"
cn1.Open strDbPath
cn1.BeginTrans
Set rs1 = New ADODB.Recordset
rs1.CursorLocation = adUseServer
rs1.Open "SELECT * FROM TBL WHERE COL1=10", cn1, adOpenKeyset, adLockPessimistic

Set cn2 = New ADODB.Connection
cn2.Provider = "Microsoft.ACE.OLEDB.12.0"
cn2.Open strDbPath
cn2.BeginTrans
Set rs2 = New ADODB.Recordset
rs2.CursorLocation = adUseServer
rs2.Open "SELECT * FROM TBL WHERE COL1=10", cn2, adOpenKeyset, adLockPessimistic

'この時点では、両者とも同じ値 "Old Value" が出力される
Debug.Print rs1.Collect("COL2")
Debug.Print rs2.Collect("COL2")

rs1.Collect("COL2") = "New Value 1" 'ここでロックが開始される
rs2.Collect("COL2") = "New Value 2" '0x80004005「現在ロックされているので、更新できませんでした。」

'rs1 は編集中だが、rs2 は未編集状態
Set f1 = rs1.Fields("COL2")
Set f2 = rs2.Fields("COL2")
Debug.Print rs1.EditMode, f1.OriginalValue, f1.Value  ', f1.UnderlyingValue
Debug.Print rs2.EditMode, f2.OriginalValue, f2.Value  ', f2.UnderlyingValue

rs1.Update  '明示的トランザクション中につき、これはまだコミット前

'どちらも未編集状態だが、rs1 の OriginalValue が更新されている
Debug.Print rs1.EditMode, f1.OriginalValue, f1.Value  ', f1.UnderlyingValue
Debug.Print rs2.EditMode, f2.OriginalValue, f2.Value  ', f2.UnderlyingValue

'編集した cn1 側がコミット/ロールバックされていないため、まだ書き込めないまま
rs2.Collect("COL2") = "New Value 3" '0x80004005「現在ロックされているので、更新できませんでした。」

Debug.Print rs2.Collect("COL2") '変更前の "Old Value" が出力される

cn1.CommitTrans 'コミットにより、ここでようやくロックが解除される
rs1.Close

Debug.Print rs2.Collect("COL2") 'この時点で、cn1 の変更結果が反映される可能性がある

rs2.Collect("COL2") = "New Value 4"  'ロックされていないので、今度は問題なく書き込める
rs2.Update
cn2.CommitTrans
rs2.Close

cn1.Close
cn2.Close

    手元の環境で試してみたところ、CursorLocation / CursorType / LockType として利用可能な組み合わせは、下記の 9 パターンであることを確認しました。やはり adOpenDynamic の指定は無効でしたし、排他ロックとなる組み合わせは、adUserServer / adOpenKeyset / adLockPessimistic だけのようです。

    1: adUserServer / adOpenForwardOnly / adLockReadOnly
    2: adUserServer / adOpenKeyset / adLockReadOnly
    3: adUserServer / adOpenKeyset / adLockPessimistic
    4: adUserServer / adOpenKeyset / adLockOptimistic
    5: adUserServer / adOpenKeyset / adLockBatchOptimistic
    6: adUserServer / adOpenStatic / adLockReadOnly
    7: adUseClient / adOpenStatic / adLockReadOnly
    8: adUseClient / adOpenStatic / adLockOptimistic
    9: adUseClient / adOpenStatic / adLockBatchOptimistic

    2018年12月10日 18:21
    |

すべての返信

  • Question
    サインインして投票
    1
    サインインして投票

    Microsoft.ACE.OLEDB.12.0 については未確認ですが、Microsoft.JET.OLEDB.4.0 当時は、排他的ロックはサポートされていたものの、動的カーソル (adOpenDynamic) はサポートされていませんでした(ODBC でのみ使用可能)。

    確認のため、Open 前後で CursorLocation, CursorType, LockType プロパティの組み合わせが変化していないことを確認してみてください。変化する場合（あるいはエラーになる場合）はサポートされていないことを意味します。

    ADODB.Recordset で排他ロックを仕掛ける場合、Jet 4.0 当時は上記 URL にあるように、adUseServer + adOpenKeyset + adLockPessimistic の組み合わせが必要でした。Microsoft.ACE.OLEDB.12.0 では検証したことが無いので、結果報告をもらえると嬉しいです。


    2018年12月10日 6:11
    |
  • Question
    サインインして投票
    0
    サインインして投票

    CursorLocation, CursorType, LockType の各プロパティの設定については、魔界の仮面弁士さんのおっしゃる通りですね。
    （ Jet でも ACE でも基本的には同じです）

    rs!ダミー = rs!ダミー '★ここから排他的ロックをかけたいけど、かからない。

    但し接続先が Access データベースである場合、そのままだとレコードのロックがかかるタイミングは「レコードセットのいずれかのフィールドに値が代入された時」ではなく、その後の「 Update メソッドが実行された時」となります。

    rs.Update

    いずれにせよ、トランザクション制御系のメソッド（ BeginTrans, RollbackTrans, CommitTrans）とセットで使用する必要があるでしょう。

    constr = "provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & target_file
    constr = "provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & target_file & _
         ";PERSIST SECURITY INFO=FALSE"




    • 編集済み sk.exe 2018年12月11日 2:23 記述に誤りがありました
    2018年12月10日 7:10
    |
  • Question
    サインインして投票
    0
    サインインして投票

    上記記事では、おそらくAccess VBAで解決した様子ですが、
    同じことをExcel VBAで行うことは可能でしょうか？
    コネクションオープン時に何か指定する必要があると考えています。

    CurrentProject.Connectionからコネクションオブジェクトを取得するとData Shaping Serviceの機能で排他ロックにならないようですので、ItaruMatumotoさんは自分で新しくコネクションオブジェクトを作られているため、既にご紹介された記事と同じ対策を行っていることと同じになると思います。

    では、なぜ排他ロックがかからないかですが、みなさんが既に書かれているように、ADOと使用されているデータベースの組み合わせにおける適切な指定が一番疑わしいと思われます。具体的な回答にならずごめんなさい。

    ★良い回答には質問者は回答済みマークを、閲覧者は投票を！

    2018年12月10日 8:07
    |
    モデレータ
  • Question
    サインインして投票
    1
    サインインして投票
    但し接続先が Access データベースである場合、そのままだとレコードのロックがかかるタイミングは「レコードセットのいずれかのフィールドに値が代入された時」ではなく、その後の「 Update メソッドが実行された時」となります。

    それは adLockOptimistic の場合ですよね? adLockPessimistic なら、編集開始時にロックが開始されますよ。

    以下実験コード。
    TBL 表は、主キー列 COL1 (数値型) と、テキスト列 COL2 を持っており、
    COL1=10、COL2="Old Value" なレコードが登録されている状態で開始します。

    Set cn1 = New ADODB.Connection
cn1.Provider = "Microsoft.ACE.OLEDB.12.0"
cn1.Open strDbPath
cn1.BeginTrans
Set rs1 = New ADODB.Recordset
rs1.CursorLocation = adUseServer
rs1.Open "SELECT * FROM TBL WHERE COL1=10", cn1, adOpenKeyset, adLockPessimistic

Set cn2 = New ADODB.Connection
cn2.Provider = "Microsoft.ACE.OLEDB.12.0"
cn2.Open strDbPath
cn2.BeginTrans
Set rs2 = New ADODB.Recordset
rs2.CursorLocation = adUseServer
rs2.Open "SELECT * FROM TBL WHERE COL1=10", cn2, adOpenKeyset, adLockPessimistic

'この時点では、両者とも同じ値 "Old Value" が出力される
Debug.Print rs1.Collect("COL2")
Debug.Print rs2.Collect("COL2")

rs1.Collect("COL2") = "New Value 1" 'ここでロックが開始される
rs2.Collect("COL2") = "New Value 2" '0x80004005「現在ロックされているので、更新できませんでした。」

'rs1 は編集中だが、rs2 は未編集状態
Set f1 = rs1.Fields("COL2")
Set f2 = rs2.Fields("COL2")
Debug.Print rs1.EditMode, f1.OriginalValue, f1.Value  ', f1.UnderlyingValue
Debug.Print rs2.EditMode, f2.OriginalValue, f2.Value  ', f2.UnderlyingValue

rs1.Update  '明示的トランザクション中につき、これはまだコミット前

'どちらも未編集状態だが、rs1 の OriginalValue が更新されている
Debug.Print rs1.EditMode, f1.OriginalValue, f1.Value  ', f1.UnderlyingValue
Debug.Print rs2.EditMode, f2.OriginalValue, f2.Value  ', f2.UnderlyingValue

'編集した cn1 側がコミット/ロールバックされていないため、まだ書き込めないまま
rs2.Collect("COL2") = "New Value 3" '0x80004005「現在ロックされているので、更新できませんでした。」

Debug.Print rs2.Collect("COL2") '変更前の "Old Value" が出力される

cn1.CommitTrans 'コミットにより、ここでようやくロックが解除される
rs1.Close

Debug.Print rs2.Collect("COL2") 'この時点で、cn1 の変更結果が反映される可能性がある

rs2.Collect("COL2") = "New Value 4"  'ロックされていないので、今度は問題なく書き込める
rs2.Update
cn2.CommitTrans
rs2.Close

cn1.Close
cn2.Close

    手元の環境で試してみたところ、CursorLocation / CursorType / LockType として利用可能な組み合わせは、下記の 9 パターンであることを確認しました。やはり adOpenDynamic の指定は無効でしたし、排他ロックとなる組み合わせは、adUserServer / adOpenKeyset / adLockPessimistic だけのようです。

    1: adUserServer / adOpenForwardOnly / adLockReadOnly
    2: adUserServer / adOpenKeyset / adLockReadOnly
    3: adUserServer / adOpenKeyset / adLockPessimistic
    4: adUserServer / adOpenKeyset / adLockOptimistic
    5: adUserServer / adOpenKeyset / adLockBatchOptimistic
    6: adUserServer / adOpenStatic / adLockReadOnly
    7: adUseClient / adOpenStatic / adLockReadOnly
    8: adUseClient / adOpenStatic / adLockOptimistic
    9: adUseClient / adOpenStatic / adLockBatchOptimistic

    2018年12月10日 18:21
    |
  • Question
    サインインして投票
    0
    サインインして投票
    それは adLockOptimistic の場合ですよね? adLockPessimistic なら、編集開始時にロックが開始されますよ。

    大変失礼致しました。こちらのデバッグミスでした。

    一方のセッションでの LockType が adLockPessimistic であるのに対し、もう一方のセッションでの LockType が adLockBatchOptimistic である状態になっていました。

    2018年12月11日 2:21
    |
  • Question
    サインインして投票
    1
    サインインして投票

    魔界の仮面弁士さま

    回答して頂き、ありがとうございます。
    調査に時間がかかり申し訳ありません。
    結果を報告します。

    >確認のため、Open 前後で CursorLocation, CursorType, LockType プロパティの組み合わせが変化していないことを確認してみてください。変化する場合（あるいはエラーになる場合）はサポートされていないことを意味します。

    rsのOpen後、指定した値がどのようになっているか調べてみました。

    （１）オープン後に変わらない組み合わせ
    以下については、2018年12月10日 18:21に頂いた回答の内容と同じでした。
    1: adUseServer/adOpenForwardOnly/adLockReadOnly
    2: adUseServer/adOpenKeyset/adLockReadOnly
    3: adUseServer/adOpenKeyset/adLockPessimistic
    4: adUseServer/adOpenKeyset/adLockOptimistic
    5: adUseServer/adOpenKeyset/adLockBatchOptimistic
    6: adUseServer/adOpenStatic/adLockReadOnly
    7: adUseClient/adOpenStatic/adLockReadOnly
    8: adUseClient/adOpenStatic/adLockOptimistic
    9: adUseClient/adOpenStatic/adLockBatchOptimistic

    （２）オープン後に変わった組み合わせ
    adUseServer/adOpenDynamic/adLockPessimistic
    adUseServer/adOpenStatic/adLockPessimistic
    adUseServer/adOpenForwardOnly/adLockPessimistic
     →いずれも adUseServer/adOpenKeyset/adLockPessimistic に変わりました。

    adUseServer/adOpenDynamic/adLockReadOnly
     →adUseServer/adOpenStatic/adLockReadOnly に変わりました。

    adUseServer/adOpenDynamic/adLockOptimistic
    adUseServer/adOpenStatic/adLockOptimistic
    adUseServer/adOpenForwardOnly/adLockOptimistic
     →いずれも adUseServer/adOpenKeyset/adLockOptimistic に変わりました。

    adUseServer/adOpenDynamic/adLockBatchOptimistic
    adUseServer/adOpenStatic/adLockBatchOptimistic
    adUseServer/adOpenForwardOnly/adLockBatchOptimistic
     →いずれも adUseServer/adOpenKeyset/adLockBatchOptimistic に変わりました。

    adUseClient/adOpenKeyset/adLockPessimistic
    adUseClient/adOpenDynamic/adLockPessimistic
    adUseClient/adOpenStatic/adLockPessimistic
    adUseClient/adOpenForwardOnly/adLockPessimistic
     →いずれも adUseClient/adOpenStatic/adLockBatchOptimistic に変わりました。

    adUseClient/adOpenKeyset/adLockReadOnly
    adUseClient/adOpenDynamic/adLockReadOnly
    adUseClient/adOpenForwardOnly/adLockReadOnly
     →いずれも →adUseClient/adOpenStatic/adLockReadOnly に変わりました。

    adUseClient/adOpenKeyset/adLockOptimistic
    adUseClient/adOpenDynamic/adLockOptimistic
    adUseClient/adOpenForwardOnly/adLockOptimistic
     →いずれも adUseClient/adOpenStatic/adLockOptimistic に変わりました。

    adUseClient/adOpenKeyset/adLockBatchOptimistic
    adUseClient/adOpenDynamic/adLockBatchOptimistic
    adUseClient/adOpenForwardOnly/adLockBatchOptimistic
     →いずれも adUseClient/adOpenStatic/adLockBatchOptimistic に変わりました。


    このような観点で調べる事を初めて知りました。

    ありがとうございます。

    2018年12月14日 1:57
    |
  • Question
    サインインして投票
    0
    サインインして投票

    魔界の仮面弁士さま

    大変お世話になりました。

    実際に確認して頂き、ありがとうございます（資料がないため、途方にくれていました）。

    本当に助かりました。

    提示して頂いたコーディング例を参考にさせて頂きます。

    2018年12月14日 2:05
    |
  • Question
    サインインして投票
    0
    サインインして投票

    ちなみに adUseClient を指定した場合は、すべて adOpenStatic として扱われる仕様です。このモードでは、サーバ側データのスナップショットコピーがクライアント側の Recordset オブジェクト内に一括して取り出されます。


    以下、CursorType プロパティ の解説から引用

    CursorLocation プロパティに adUseClient が設定されている場合は、adOpenStatic の設定だけがサポートされます。サポートされていない値が設定された場合でも、エラーは発生しません。サポートされている CursorType のうち、一番近い値で代用されます。

    プロバイダが要求したカーソルの種類をサポートしていない場合は、ほかのカーソルの種類が返ります。Recordset オブジェクトが開いているときには、CursorType プロパティは実際に使用されているカーソルの種類に合わせて変更されます。返されたカーソル特有の機能を調べるには、Supports メソッドを使用します。Recordset を閉じると、CursorType プロパティは元の設定値に戻ります。

    2018年12月20日 7:07
    |