オートナンバーでなく，主キー値を自動取得したい時に
複数のユーザーが使用するような環境だと
採番テーブル
というものを作って，そこに現在の主キーの最大値を入れて置くようなことはよくあります。
その時，その値を更新する時，排他状態にしないといけません。
採番テーブルは，以下のようなものを前提にします。
テーブル名: 採番_A ID 長整数型 MaxValue 長整数型 '現在の最大の主キー値を保存 Dummy 長整数型
採番テーブルのレコードは，ID: 1 の1レコードのみ。
主キー値は単純に +1 ずつ増加。
※ 主キーが "AAA-000001" のように組み合わさっている場合は，
下の fGetNextID に引数を入れて
WHERE句でそれぞれの最大値を拾い出すようにします。
Nz(rs!MaxValue) + 1 のようなものでなく，
少しロジックも変更します。
次の主キー値を取得する関数(例えば，fGetNextID_A)を作っておいて，
Private Sub Form_Current() If Me.NewRecord Then Me!主キーフィールド.DefaultValue = fGetNextID_A 'テキスト型の時は， 'Me!主キーフィールド.DefaultValue = """" & fGetNextID_A & """" End If End Sub
のようにします。
まず，DAOで説明すると
Private Function fGetNextID_A() As Long Dim db As DAO.Database Dim rs As DAO.Recordset Dim strSQL As String Dim lngNextID As Long strSQL = "SELECT * FROM 採番_A WHERE ID = 1" Set db = CurrentDB Set rs = db.OpenRecordset(strSQL, dbOpenDynaset) rs.Edit '<--ここでロックがかかる。 lngNextID = Nz(rs!MaxValue) + 1 rs!MaxValue = lngNextID rs.Update rs.Close Set rs = Nothing ' この行は無くてもかまいません。 db.Close Set db = Nothing ' この行は無くてもかまいません。 fGetNextID_A = lngNextID End Function
のようにした場合，DAOだとデフォルトで
rs.Edit のところで排他的ロックがかかります。
これで万が一他のユーザーがこのテーブルにアクセスして
同時に .Edit しようとしてもできません。
一方，ADOで行うと，
rs.Open 時に，adLockPessimistic を指定しても，なぜか，
以下の rs.Update のところで初めてロックがかかるため
非常に具合が悪いので，ADOでは難しいと書きました。
rs.Update のところで一瞬だけロック状態になるのを
オプティミスティック・ロック (楽観的ロック/共有的ロック) といいます。
Private Function fGetNextID_A() As Long Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim strSQL As String strSQL = "SELECT * FROM 採番_A WHERE ID = 1" Set rs = New ADODB.Recordset Set cn = CurrentProject.Connection rs.Open strSQL, cn, adOpenKeyset, adLockPessimistic, adCmdText 'rs.Edit (注) ADOには，明示的にrs.Editはないが，それでも rs!Dummy = rs!Dummy + 1 '本来ならここでロックされる予定 lngNextID = Nz(rs!MaxValue) + 1 rs!MaxValue = lngNextID rs.Update '実際は，ここでロックされる rs.Close Set rs = Nothing ' この行は無くてもかまいません。 cn.Close Set cn = Nothing ' この行は無くてもかまいません。 End Function
ADOのヘルプより
adLockPessimistic 2
レコード単位の排他的ロックを示します。プロバイダは、
レコードを確実に編集するための措置を行います。通常は、
編集直後のデータ ソースでレコードをロックします。
adLockPessimistic の説明からも
最初に編集した地点でレコードがロックされるはずなのに
実際は，ロックされずに楽観的ロック/共有的ロック(adLockOptimistic)
になってしまいます。
SQLServerやMSDE相手だと他の方法(※)があるのですが，
Accessだと難しいのか? ... と考えていました。
※ "SELECT * FROM 採番_A WITH (UPDLOCK) WHERE ID = 1"
のように WITH (UPDLOCK) を付けます。
2001年11月号のVBMagazineにも説明があります。
今まで，ずっと ADO や JET のヘルプで
何か手がかりはないのか? と探していました。
昨夜，Access側のヘルプを見ていて以下の記述に気が付きました。
CurrentProjectオブジェクトのConnectionプロパティの下の方に
3. MSDataShape では Recordset.LockType = adLockOptimistic、
adLockBatchOptimistic、または adLockReadOnly (既定値)
をサポートします。adLockPessimistic を設定すると、
adLockOptimistic に変更されます。
のように書いてあります。
実は，Access2000から，ADO と JET Database の間に
Data Shaping Service (上記のMSDataShapeのこと)
というものが入っています。参考資料1
参考資料2
(テーブル表示で，リンクされた多側のテーブルを
いっしょに表示できるようにするためのもので，
Access2000からテーブルで + をクリックすると
開くようになった例の機能です。)
そして，CurrentProject.Connection を使って取得すると
この DataShapingService との間の Connection のコピー
が返ってきます。
つまり，DataShapingService との接続情報が返ってきます。よって，
Set cn = CurrentProject.Connection
rs.Open strSQL, cn, adOpenKeyset, adLockPessimistic, adCmdText
のように，Pessimisticで指定しても，上記の
「adLockPessimistic を設定すると adLockOptimistic に変更されます。」
という恐ろしい暗黙の変換によって
共有的ロック (Optimistic Lock)
になってしまっていたのです ... なんてことか!
JET Databaseとの基本接続情報は，
CurrentProject.BaseConnectionString
で取り出せるので，それをConnectionオブジェクトの接続文字列に設定して，.Openします。
Private Function fGetNextID_A() As Long Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim strSQL As String strSQL = "SELECT * FROM 採番_A WHERE ID = 1" Set cn = New ADODB.Connection cn.ConnectionString = CurrentProject.BaseConnectionString cn.Open Set rs = New ADODB.Recordset rs.Open strSQL, cn, adOpenKeyset, adLockPessimistic, adCmdText 'rs.Edit (注) ADOには，明示的にrs.Editはないが， rs!Dummy = rs!Dummy + 1 'ちゃんとここでロックされる lngNextID = Nz(rs!MaxValue) + 1 rs!MaxValue = lngNextID rs.Update rs.Close Set rs = Nothing ' この行は無くてもかまいません。 cn.Close Set cn = Nothing ' この行は無くてもかまいません。 End Function
Published: 2001-10-19
Last Updated: 2013-06-14
つくれますの部屋
Copyright(C) 2001 Yasuharu Takahashi, All Rights Reserved.