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

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