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

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

さて、Recordset オブジェクト (DAO) です。Database.OpenRecordset メソッド (DAO)で Recordset を取得する。まずは、テーブル定義。都合がよいときもあるので rowversion (Transact-SQL)を持たせている。

CREATE TABLE Table_0(
  ID     int IDENTITY(1,1)
 ,F_Num  int
 ,F_Date date
 ,F_TS   rowversion
 CONSTRAINT PK_Table_0 PRIMARY KEY CLUSTERED (ID ASC)
)

この時、こんな感じでRecordsetを取り扱う。

Sub DAORecordset_Dynaset()
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset( _
                "SELECT ID, F_Num FROM Table_0" _
              , DAO.RecordsetTypeEnum.dbOpenDynaset _
              , DAO.RecordsetOptionEnum.dbSeeChanges _
              )
End Sub

レコードの更新/削除が可能。また、フィールドに主キーが含まれているから、レコードの追加が可能。なければ追加できない。OpenRecordset メソッドが実行された時、

SQLExecDirect: SELECT "dbo"."Table_0"."ID" FROM "dbo"."Table_0" 
SQLPrepare: SELECT "ID","F_Num","F_TS"  FROM "dbo"."Table_0"  WHERE "ID" = ?
SQLExecute: (GOTO BOOKMARK)
SQLExecute: (GOTO BOOKMARK)

最初に主キーのみ取得する。条件があれば条件に該当する主キーのみ取得。その主キーを利用してレコードを取得する。該当するレコードがあれば先頭のレコードのみ取得まで。該当する他のレコードをすぐさま取得しないことと rowversion のフィールドを自動的に取得することがポイント。

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

ログばっかりで飽きてきたので、SQL Server 2014 Express LocalDB も使ってみる。SQL Server をさくっと試せるからいい感じ。
必要なもの：

Microsoft® SQL Server® 2014 Express
本体：SqlLocalDB.msi と、お好みで  管理用ツールは、SSMS

SSMS まで必要ないなら、PowerShell で管理できるように、

Microsoft® SQL Server® 2014 Feature Pack

• SharedManagementObjects.msi
• PowerShellTools.msi

インストールしたら、データベースとテーブルを作成。

Import-Module SQLPS -DisableNameChecking

# Create database
$QueryString = @"
    USE master;
    GO
    CREATE DATABASE testDB on (
        name =   'testDB1',
        filename='C:\LocalDB_data\testDB.mdf'
    )
    COLLATE Japanese_XJIS_100_CI_AS_KS_WS;
"@

Invoke-Sqlcmd $QueryString -ServerInstance '(LocalDB)\MSSQLLocalDB'


# Create table
$QueryString = @"
    USE testDB;
    GO
    CREATE TABLE Table_1 (
        ID int IDENTITY(1,1) NOT NULL
       ,F_Num int
       ,F_Date date
       ,CONSTRAINT PK_Table_1 PRIMARY KEY (ID ASC)
    )
"@

Invoke-Sqlcmd $QueryString -ServerInstance '(LocalDB)\MSSQLLocalDB'

# Drop database
Invoke-Sqlcmd "DROP DATABASE testdb" -ServerInstance "(localdb)\MSSQLLocalDB"

照合順序を Japanese_XJIS_100_CI_AS_KS_WS にしているのは、Access アプリに合わせているから。
"MSSQLLocalDB"は自動インスタンス名。試すぐらいならこのままでも困ることはない。

コマンド ライン管理ツール: SqlLocalDB.exe

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

Database.Execute メソッド を実行したとき。
Execute メソッド の Query パラメータは Access SQL で記述。そして、実際には、T-SQLに翻訳されてSQL Server で実行される。ACE は その操作に関与しているから、Database.RecordsAffected プロパティ で影響を受けたレコード数の取得は可能。

Sub test_Database_Execute()
On Error GoTo ErrHnd
    Dim dbs As DAO.Database
    Set dbs = CurrentDb
    
    dbs.Execute _
        "insert into Table_0 (F_Num) values (100);", _
        DAO.dbFailOnError
    Debug.Print dbs.RecordsAffected
    Debug.Print dbs.OpenRecordset("SELECT @@IDENTITY")(0)

    dbs.Execute _
        "update Table_0 set F_Num = 99 where F_Num = 100;", _
        DAO.dbFailOnError
    Debug.Print dbs.RecordsAffected

    dbs.Execute _
        "delete from Table_0 where F_Num = 99;", _
        DAO.dbFailOnError + DAO.dbSeeChanges
    Debug.Print dbs.RecordsAffected

Done:

Exit Sub
ErrHnd:
    If DBEngine.Errors.Count > 0 Then
        Dim e As DAO.Error, msg As String
        For Each e In DBEngine.Errors
            msg = msg & e.Number & ":" & e.Description & vbCrLf
        Next
        Debug.Print msg
    End If

    Resume Done
End Sub

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

CRUD の 残りひとつ、レコード を削除するときどのようになるのか。レコードの更新と概ね同じなのだけど。

DELETE Table_1.F_Num
FROM Table_1
WHERE Table_1.F_Num Between 15 And 20;

SQLExecDirect: SELECT "dbo"."Table_1"."ID" FROM "dbo"."Table_1" WHERE ("F_Num" BETWEEN 15 AND 20 ) 
SQLPrepare: SELECT "ID","F_Num","F_Date","F_Text","F_TS"  FROM "dbo"."Table_1"  WHERE "ID" = ?
SQLExecute: (GOTO BOOKMARK)
SQLPrepare: DELETE FROM "dbo"."Table_1" WHERE "ID" = ?
SQLExecDirect: DELETE FROM "dbo"."Table_1" WHERE "ID" = ?
SQLExecute: (GOTO BOOKMARK)
SQLPrepare: DELETE FROM "dbo"."Table_1" WHERE "ID" = ?
SQLExecDirect: DELETE FROM "dbo"."Table_1" WHERE "ID" = ?

条件にマッチするレコードの主キーのみをまず取得。ここでレコード数が0であれば終了。そのあと、1レコードずつフェッチしながら削除を繰り返す流れ。

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

レコードを更新してみる。わかってしまえば難しいことではないけど、Access らしい動作かなと。

UPDATE Table_1 SET Table_1.F_Num = 999, Table_1.F_Text = "ABC"
WHERE Table_1.ID In (10,11);

SQLExecDirect: SELECT "dbo"."Table_1"."ID" FROM "dbo"."Table_1" WHERE ("ID" IN (10 ,11 ) ) 
SQLPrepare: SELECT "ID","F_Num","F_Text"  FROM "dbo"."Table_1"  WHERE "ID" = ?
SQLExecute: (GOTO BOOKMARK)
SQLPrepare: UPDATE "dbo"."Table_1" SET "F_Num"=?,"F_Text"=? WHERE "ID" = ?
SQLExecute: (UPDATE)
SQLExecute: (GOTO BOOKMARK)
SQLExecute: (UPDATE)

ここで実施されているのは、
1行目：抽出条件にマッチするレコードの主キーの取得
2行目：主キーをパラメータとするクエリの準備
3行目：準備されたクエリで1レコードフェッチ
4行目：更新用 SQLの準備
5行目：更新
6行目：準備されたクエリで1レコードフェッチ
7行目：更新
2行目の前にトランザクション開始、対象の更新完了後トランザクションcommit or rollback

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

レコードを追加するとき、ODBC データソースであるSQL Serverとの間でどのような処理がされるのか。

INSERT INTO Table_1 ( F_Text )
VALUES ('ABC');

SQLExecDirect: INSERT INTO  "dbo"."Table_1"  ("F_Text") VALUES (?)
SQLExecDirect: SELECT @@IDENTITY

ログ上ではこの内容のみとなるけど、実際には、

1. トランザクション：begin
2. exec sp_executesql N'INSERT INTO "dbo"."Table_1" ("F_Text") VALUES (@P1)',N'@P1 nvarchar(10)',N'XXX'
3. SELECT @@IDENTITY
4. トランザクション：commit or rollback

この動作は、追加 クエリだけに限らず、テーブルを開いてレコードを追加するときなどでも同じになる。

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

集計クエリについても見ておく。Where条件を持つ選択クエリと概ね同じ。少しだけ気を付けておけば、SQL Server側で集計が実施されるはず。

SELECT Table_1.F_Text, Sum(Table_1.F_Num) AS SumOfF_Num
FROM Table_1
GROUP BY Table_1.F_Text
HAVING Sum(Table_1.F_Num)>100;

SQLExecDirect: SELECT "F_Text" ,SUM("F_Num" )  FROM "dbo"."Table_1" GROUP BY "F_Text"  HAVING (SUM("F_Num" )  > 100 )

期待通りの集計がされて、全レコードを取得するようなことはない。ただし、

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

演算 フィールドについて少し寄り道をして。

SELECT Table_1.ID, Table_1.F_Num, Year([F_Date]) AS F_Year, Month([F_Date]) AS F_Month
FROM Table_1;

SQLExecDirect: SELECT "dbo"."Table_1"."ID" FROM "dbo"."Table_1" 
SQLPrepare: SELECT "ID","F_Num","F_Date"  FROM "dbo"."Table_1"  WHERE "ID" = ?
SQLExecute: (GOTO BOOKMARK)
SQLPrepare: SELECT "ID","F_Num","F_Date"  FROM "dbo"."Table_1"  WHERE "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ?
SQLExecute: (MULTI-ROW FETCH)

SQLExecDirect: SELECT "ID" ,"F_Num" ,"F_Date"  FROM "dbo"."Table_1"

演算は ACE もしくは Access で実施となる。

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

クエリ を開いたときどうなるの？。ベース テーブル が ODBC リンク テーブルという Access クエリで。

SELECT Table_1.ID, Table_1.F_Num
FROM Table_1;

SQLExecDirect: SELECT "dbo"."Table_1"."ID" FROM "dbo"."Table_1" 
SQLPrepare: SELECT "ID","F_Num"  FROM "dbo"."Table_1"  WHERE "ID" = ?
SQLExecute: (GOTO BOOKMARK)
SQLPrepare: SELECT "ID","F_Num"  FROM "dbo"."Table_1"  WHERE "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ?
SQLExecute: (MULTI-ROW FETCH)

SQLExecDirect: SELECT "ID" ,"F_Num"  FROM "dbo"."Table_1" 

並び替え/抽出がないので、特に見どころはない。Dynaset の場合、主キーの取得がまず実施される。それらを利用しレコードをフェッチしていくことになる。Snapshot の場合、すべてのレコードを取得する。

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

作成した ODBC リンク テーブルを開いてみる。その時どのようなことが起きているか。
確認できるログは以下の通り。

SQLExecDirect: SELECT "dbo"."Table_1"."ID" FROM "dbo"."Table_1" 
SQLPrepare: SELECT "ID","F_Num"  FROM "dbo"."Table_1"  WHERE "ID" = ?
SQLExecute: (GOTO BOOKMARK)
SQLPrepare: SELECT "ID","F_Num"  FROM "dbo"."Table_1"  WHERE "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ? OR "ID" = ?
SQLExecute: (MULTI-ROW FETCH)
SQLExecute: (MULTI-ROW FETCH)
SQLExecute: (MULTI-ROW FETCH)
SQLExecute: (MULTI-ROW FETCH)
SQLExecute: (MULTI-ROW FETCH)
SQLExecute: (MULTI-ROW FETCH)
SQLExecute: (MULTI-ROW FETCH)
SQLExecute: (MULTI-ROW FETCH)
SQLExecute: (MULTI-ROW FETCH)
SQLExecute: (MULTI-ROW FETCH)

大事なのは、1行目と4行目以降。

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

勉強用に SQL Server 2014 を インストール したのに別のことを始めてしまう。
Access 2013 としているけど、Access 2007 / 2010 と変わらんでしょう。違いがありそうなときは別途考える。
Access 2013 でSQL Server などODBC ソースに接続できる リンク テーブルの作り方については、

SQL Server のデータにリンクする

にあるから特に説明はない。DSN-less とかの工夫するくらいだろうか。
ともあれ、SQL Server にテーブルを作成して、ODBC リンク テーブルを。

Access 2013 Access 97 ファイル形式のmdbをなんとかする

Access 97 ファイル形式を サポート してないのだからしょうがない。なら、なんとかするまでよ。

　JET 3.x をサポートしないから Access 97 ファイル形式を Acccess 2013 で開こうとするとこうなる。Access 2013 上で Access アプリケーション としての動作しないとしても、せめて ファイル形式を読み込める状態に。Access 2013 (ACE15)では対応できないから、ほかの方法で変換するしかない。Access 97ファイル形式を読み込める バージョン の Access がなくても、今のところなんとかなるはず。

PowerShell DAOでAccessファイルを操作

Access が導入されていない環境で accdb (Access ファイル)を操作する。ただし、DAOで。

　集計フィールドをどうやって作ればよいのだろうと右往左往した結果。新規ファイルを作成して、テーブルといくつかのレコードを追加するメモ。

　忘れてしまうほど使うことがない。

access2010 access2007 添付フィールド/複数値フィールド付レコードコピー

Sub CopyRecord_Attachment_MultiValues()
    Dim rs1 As DAO.Recordset, rs1a As DAO.Recordset, rs1m As DAO.Recordset
    Dim rs2 As DAO.Recordset, rs2a As DAO.Recordset, rs2m As DAO.Recordset
    Dim dbs As DAO.Database
    
    Set dbs = CurrentDb
    
    Set rs1 = dbs.OpenRecordset( _
        "SELECT F01, F_attachment, F_multivalue FROM table01 WHERE ID = 1;")
    Set rs1a = rs1("F_attachment").Value
    Set rs1m = rs1("F_multivalue").Value
    
    Set rs2 = dbs.OpenRecordset( _
        "SELECT F01, F_attachment, F_multivalue FROM table02;")

    rs2.AddNew
        rs2.Fields("F01") = rs1.Fields("F01")
        
        Set rs2a = rs2("F_attachment").Value
        
        Do Until rs1a.EOF
            rs2a.AddNew
                rs2a.Fields("FileData") = rs1a.Fields("FileData")
                rs2a.Fields("FileName") = rs1a.Fields("FileName")
            rs2a.Update
            rs1a.MoveNext
        Loop
        
        Set rs2m = rs2("F_multivalue").Value
        
        Do Until rs1m.EOF
            rs2m.AddNew
                rs2m.Fields("Value") = rs1m.Fields("Value")
            rs2m.Update
            rs1m.MoveNext
        Loop
    rs2.Update

End Sub

access2010 access2007 DAO.Fields2 SaveToFile

添付ファイル型フィールドに保存されているファイルをローカルに保存する。

Sub SaveToFileAll()
    Dim strSQL As String, rs As DAO.Recordset
    strSQL = "Select AttachmentField_Name.FileName As FileName, " & _
             "AttachmentField_Name.FileData As FileData " & _
             "From table_Name"
            '"Where FileName = 'FileName'"
 
   Set rs = CurrentDb.OpenRecordset(strSQL)
    
    Do Until rs.EOF
        rs.Fields("FileData").SaveToFile CurrentProject.Path & _
                                     "\" & rs.Fields("FileName").Value
        rs.MoveNext
    Loop
End Sub