2014/07/26

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

2014/07/16

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

2014/07/05

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

2014/07/01

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