ACCESS 排他制御について考える

　最初に断っておきますが、もしもこれをご覧になっている方が、業務システムをACCESSのACCDB（MDB）のみで構築しようと考えていて、信頼度の高い排他制御の方法をお探しでしたら、この記事は役に立ちませんのでご了承ください。
　ACCESSに関するBlogをやっていて何ですが、ACCESSだけで業務用のシステムが構築できるなんて思ってません。ACCESSのDB向けに用意されたUI環境は、小規模から中規模くらいまでのシステムを効率よく開発するのには有用だと思っています。しかし、信頼性の高いデータベース運用の要求には応えられないと思います。
　OracleやSQLServerといったデータベースは、バックアップや履歴をきちんと保管しておけば、何かあったときに、何日の何時何分何秒を指定して、その時点の状態に戻すことができます。
　トランザクション処理も、一応ACCESSでもできますが、処理可能な容量が小さいようで、おそらく実用になりません。

　前置きが長くなりましたが、要は排他制御が必要になるような、信頼性が高い物を作るんだったら、データベースにACCESSを採用するのは不適切だと思うということです。
　シングルユーザー、スタンドアロンがACCESSで安定稼働できる限界だと思います。
　SQL Serverには無償版のExpress、Oracleにも無償版のExpress Editionがあります。
　複数のパソコンからアクセスされるデータベースを構築されるのなら、こちらをおすすめします。

　Oracleの場合は、分かり易くロックをかける事ができます。
　更新する前に、更新をかけたいレコードが抽出されるように、SELECTを行います。その時、SQL文の最後に、「FOR UPDATE NOWAIT」と書いておきます。
　こうすると、SELECTを行った時点で、同じレコードに対して、同じ事をしているユーザーが居た場合、即時にエラーになります。
　この方法のロックは、トランザクション中有効なので、複数のテーブルに渡ってロックをかける事も可能です。

　ACCESSには、このような方法は実装されていません。
　ロックはかかるのですが、レコードの編集が行われるその時にかけられます。
　上記のように、事前にロックをかけて、他のユーザーの状況を確認したりすることはできません。

　ロックを検出するためのロジックを一つ作ってみました。

Public Sub subhaita1()

  Dim CnA As New ADODB.Connection
  Dim recA As New ADODB.Recordset

  Dim CnB As New ADODB.Connection
  Dim recB As New ADODB.Recordset

  CnA.Open "PROVIDER=Microsoft.ACE.OLEDB.12.0;DATA SOURCE=D:\TEST.accdb;"

  recA.Open " SELECT * FROM TMTDFK WHERE TDFKCD = 1 ", CnA, adOpenForwardOnly, adLockPessimistic

  CnB.Open "PROVIDER=Microsoft.ACE.OLEDB.12.0;DATA SOURCE=D:\TEST.accdb;"

  recB.Open " SELECT * FROM TMTDFK WHERE TDFKCD = 1 ", CnB, adOpenForwardOnly, adLockPessimistic

  recA!TDFKMEI = recA!TDFKMEI
  recB!TDFKMEI = recB!TDFKMEI
  '↑ここでエラー

  recA.Update
  recB.Update

  recA.Close
  recB.Close

End Sub

　プログラムを実行するACCDBと、接続先のACCDBは別ファイルにしています。
　1本のプログラムで排他制御のテストを行うので、2つのConnectionを作成しています。2つの接続は、別々の接続として扱われます。

recA!TDFKMEI = recA!TDFKMEI
のところで、ロックがかかります。
ロックがかかっているので、次の
recB!TDFKMEI = recB!TDFKMEI
は、エラーになります。

　こういうエラーが表示されます。

　ACCESSで排他制御を行う場合は、この制御を上手く利用して行うしかなさそうです。

　たいして参考になりませんが、関連記事です（失敗例）。
ACCESS　排他制御について考える・その２

コメント 2

こんにちは、アクセスＶＢＡで検索してたどり着きました。
ＡＤＯの概要を説明されている記事は大変勉強になりました。漠然と考えていたのですが、解りやすい文面と図でありがとうございます。

　アクセスでの排他制御でこのプログラム処理を具体的に書かれた記事は殆ど目にしたことがありません。もう２年ほど前の経験ですが、さっぱりありませんでした。結論は私では排他制御はできず、複数ユーザで使う場合、むりやり入力フォームを開けなくするという排他でごまかしました。なので、記事が素晴らしいと思いました。

　大変申し訳ございませんがご教授いただきたいです、アクセス＋ＳＱＬサーバでのシステムの場合において、排他制御を使いたい場合はＳＱＬサーバ側でストアドを書く必要があるのでしょうか？。
　システムを作る前準備で、環境をアクセスＡＤＰでいくか、アクセスはｍｄｂのままで、すべてＡＤＯ接続を利用するか悩んでいます。　
by まらいや (2012-01-04 23:16) 

まらいや　さん

コメントありがとうございます。感想を頂けると記事作成の励みになります。
ADOは、「これさえ見ておけば大丈夫」的な資料に未だ出会えず、常に試行錯誤しています。ここに書いている内容も、私の経験上でのものでしかありませんが、参考になれば幸いです。

ACCESS+SQLServerの環境で排他制御を行う場合ですが、ストアドでなくてもかけられます。
現在SQLServerの環境が無いので、具体的な内容は提示できないのですが、WITH、NOWAIT、ROWLOCK、UPDLOCKあたりのキーワードで検索すると、いろいろ見つかると思います。
SQLServerの資料だと、
httpコロンスラッシュスラッシュ
msdn.microsoft.com/ja-jp/library/ms187373.aspx
（スパム対策で、URLを書けないようにしているので、こんな書き方ですいません）
あたりをご参照ください。

また、排他制御はトランザクション内で有効になりますので、ADOの場合は、以下のような処理手順になります。
ConnectionをOpen
BeginTransでトランザクション開始
SELECT･･･WITH･･･で排他制御
更新処理などなど
CommitTransでトランザクション終了（確定）

ADPについては、私はADPを利用した経験が全くありません。
Oracleもよく使用していたので、MDBの方が都合がよかったのです。
MDBの場合、「テーブルリンク＋ACCESSのクエリ」でデータを処理する場合、遅くなることがあるので、注意が必要です。なので、私はクエリを作る場合は、ほとんどパススルークエリで作成していました。（必要に応じて、プログラムから書き換えたりもします。）
参考にならなくてごめんなさい。
by NZ (2012-01-05 15:14)