Access 2013 ODBC リンク テーブル と SQL Server - 12

RecordsetOptionEnum 列挙 (DAO) dbSeeChanges

編集中のデータを別のユーザーが変更している場合、実行時エラーを生成します (ダイナセット タイプのみ)。

レコードが更新されていないことを確認して更新をする仕組み（オプティミスティック同時実行制御）になっている。この時発生する可能性がある実行時エラー は、

3197 ：
The Microsoft Access database engine stopped the process because you and another user are attempting to change the same data at the same time.

他のユーザーが同じデータに対して同時に変更を試みているので、プロセスが停止しました。

なのだけど、

You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column.

 IDENTITY 列を持つ SQL Server テーブルにアクセスする場合は、OpenRecordset で dbSeeChanges オプションを使用する必要があります。

なので、 dbSeeChanges を使用する必要がある。
そして、更新可能な レコードセットはその都度レコードを取得するので、何も考えずに使用すると遅い!とか言われることになる。そんな話。

Sub DAORecordset_Dynaset()
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset( _
               "SELECT F_Num FROM Table_0 " & _
               "WHERE ID IN (1,2,3,4) " & _
               "ORDER BY ID" _
              , DAO.RecordsetTypeEnum.dbOpenDynaset _
              , DAO.RecordsetOptionEnum.dbSeeChanges _
              ) ' -------- A
    
    rs.MoveNext '--------- B
    rs.MoveNext '--------- C
    rs.MovePrevious '----- D
    
    rs.Edit '------------- E
    rs.Fields(0) = 100 '-- F
    rs.Update '----------- G
End Sub

この時、

A   SQLExecDirect: SELECT "dbo"."Table_0"."ID" FROM "dbo"."Table_0" WHERE ("ID" IN (1 ,2 ,3 ,4 ) ) ORDER BY "dbo"."Table_0"."ID"
    SQLPrepare: SELECT "ID","F_Num","F_TS"  FROM "dbo"."Table_0"  WHERE "ID" = ?
    SQLExecute: (GOTO BOOKMARK)
    SQLExecute: (GOTO BOOKMARK)
B   SQLExecute: (GOTO BOOKMARK)
C   SQLExecute: (GOTO BOOKMARK)
D   SQLExecute: (GOTO BOOKMARK)
E   SQLExecute: (GOTO BOOKMARK)
G   SQLExecDirect: UPDATE "dbo"."Table_0" SET "F_Num"=?  WHERE "ID" = ? AND "F_TS" = ?
    SQLExecute: (GOTO BOOKMARK)

A   SELECT "dbo"."Table_0"."ID" FROM "dbo"."Table_0" WHERE ("ID" IN (1 ,2 ,3 ,4 ) ) ORDER BY "dbo"."Table_0"."ID"  
    declare @p1 int
    set @p1=7
    exec sp_prepexec @p1 output,N'@P1 int',N'SELECT "ID","F_Num","F_TS"  FROM "dbo"."Table_0"  WHERE "ID" = @P1',1
    select @p1
    exec sp_execute 7,1
B   exec sp_execute 7,2
C   exec sp_execute 7,3
D   exec sp_execute 7,2
E   exec sp_execute 7,2
G   exec sp_executesql N'UPDATE "dbo"."Table_0" SET "F_Num"=@P1  WHERE "ID" = @P2 AND "F_TS" = @P3',N'@P1 int,@P2 int,@P3 binary(8)',100,2,0x0000000000009C49
    exec sp_execute 7,2

• レコードセット内でレコードを移動しカレントレコードにするとき
• カレントレコードの編集を開始するとき
• レコードを更新した後 (必要なときのみ)

に、主キーを使用したレコード取得を繰り返すことになる。この動作は、レコードセット上のレコード検索でも発生する。

Sub DAORecordset_Find()
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset( _
               "SELECT ID, F_Num FROM Table_0 " & _
               "WHERE ID IN (1,2,3,4) " & _
               "ORDER BY ID" _
              , DAO.RecordsetTypeEnum.dbOpenDynaset _
              , DAO.RecordsetOptionEnum.dbSeeChanges _
              ) ' --------------- A
    rs.FindFirst "F_Num = 4" ' -- B
    rs.FindFirst "ID = 5" ' ----- C
End Sub

A   SQLExecDirect: SELECT "dbo"."Table_0"."ID" FROM "dbo"."Table_0" WHERE ("ID" IN (1 ,2 ,3 ,4 ) ) ORDER BY "dbo"."Table_0"."ID"  
    SQLPrepare: SELECT "ID","F_Num","F_TS"  FROM "dbo"."Table_0"  WHERE "ID" = ?
    SQLExecute: (GOTO BOOKMARK)
    SQLExecute: (GOTO BOOKMARK)
B   SQLExecute: (GOTO BOOKMARK)
    SQLExecute: (GOTO BOOKMARK)
    SQLExecute: (GOTO BOOKMARK)
    SQLExecute: (GOTO BOOKMARK)
    SQLExecute: (GOTO BOOKMARK)
C   SQLExecDirect: SELECT "dbo"."Table_0"."ID" FROM "dbo"."Table_0" WHERE "ID" = 5
    SQLExecute: (GOTO BOOKMARK)

A   SELECT "dbo"."Table_0"."ID" FROM "dbo"."Table_0" WHERE ("ID" IN (1 ,2 ,3 ,4 ) ) ORDER BY "dbo"."Table_0"."ID"  
    declare @p1 int
    set @p1=2
    exec sp_prepexec @p1 output,N'@P1 int',N'SELECT "ID","F_Num","F_TS"  FROM "dbo"."Table_0"  WHERE "ID" = @P1',1
    select @p1
    exec sp_execute 2,1
B   exec sp_execute 2,1
    exec sp_execute 2,2
    exec sp_execute 2,3
    exec sp_execute 2,4
    exec sp_execute 2,1
C   SELECT "dbo"."Table_0"."ID" FROM "dbo"."Table_0" WHERE "ID" = 5
    exec sp_execute 2,1

• 必要なレコードだけを取得する条件を持つクエリ
• 更新の必要がないときはスナップショットタイプのレコードセット
• レコード追加のみの場合は dbAppendOnly オプションの使用

ということを考えればよいが、レコードをキャッシュさせることでパフォーマンスの向上ができることがある。
Recordset.FillCache メソッド (DAO)

Sub DAORecordset_Cache()
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset( _
               "SELECT ID, F_Num FROM Table_0 " & _
               "WHERE ID IN (1,2,3,4,5,6,7,8,9,10,11,12) " & _
               "ORDER BY ID" _
              , DAO.RecordsetTypeEnum.dbOpenDynaset _
              , DAO.RecordsetOptionEnum.dbSeeChanges _
              ) ' --------------- A
    
    rs.CacheSize = 10 'between 5 and 1200
    rs.CacheStart = rs.Bookmark
    rs.FillCache ' -------------- B

    rs.FindFirst "F_Num = 0" ' -- C このフィールドに存在しない値で検索
End Sub

A   SQLExecDirect: SELECT "dbo"."Table_0"."ID" FROM "dbo"."Table_0" WHERE ("ID" IN (1 ,2 ,3 ,4 ,5 ,6 ,7 ,8 ,9 ,10 ,11 ,12 ) ) ORDER BY "dbo"."Table_0"."ID"  
    SQLPrepare: SELECT "ID","F_Num","F_TS"  FROM "dbo"."Table_0"  WHERE "ID" = ?
    SQLExecute: (GOTO BOOKMARK)
    SQLExecute: (GOTO BOOKMARK)
B   SQLExecDirect: SELECT "ID","F_Num","F_TS"  FROM "dbo"."Table_0" WHERE ("ID" IN (1 ,2 ,3 ,4 ,5 ,6 ,7 ,8 ,9 ,10 ,11 ,12 ) ) ORDER BY "dbo"."Table_0"."ID"
C   SQLExecute: (GOTO BOOKMARK)
    SQLExecute: (GOTO BOOKMARK)

A   SELECT "dbo"."Table_0"."ID" FROM "dbo"."Table_0" WHERE ("ID" IN (1 ,2 ,3 ,4 ,5 ,6 ,7 ,8 ,9 ,10 ,11 ,12 ) ) ORDER BY "dbo"."Table_0"."ID"  
    declare @p1 int
    set @p1=19
    exec sp_prepexec @p1 output,N'@P1 int',N'SELECT "ID","F_Num","F_TS"  FROM "dbo"."Table_0"  WHERE "ID" = @P1',1
    select @p1
    exec sp_execute 19,1
B   SET ROWCOUNT 10 
    SELECT "ID","F_Num","F_TS"  FROM "dbo"."Table_0" WHERE ("ID" IN (1 ,2 ,3 ,4 ,5 ,6 ,7 ,8 ,9 ,10 ,11 ,12 ) ) ORDER BY "dbo"."Table_0"."ID"  
    SET ROWCOUNT 0 
C   exec sp_execute 19,11
    exec sp_execute 19,12

FillCache メソッドで CacheStart で指定した開始位置から CacheSize で指定したレコード数をキャッシュする。キャッシュ内であればレコード移動の都度発生するレコードの読み取りは行われない。キャッシュするレコードの読み取りは、SET ROWCOUNT もしくは、10レコードごと読み取りの繰り返しが使用される。
キャッシュにないレコードの読み取りが発生しても実行時エラーになることはないが、新たなキャッシュが必要ならば、再度 CacheStart を指定して FillCache を実行すればよい。