Access 2016 への製品バージョンアップ

Surface Book(ja-jp)を買っちゃったのだけど、プレインストールされた Office スイート と Access 2013 についての問題を解決した。PIPC な システムを買うことがほぼないのでこれまで確かめてなかった件。

以前に購入した Surface Pro には Office Home and Business 2013 が用意されていたが、Access が含まれない Office スイートなので Access 2013(C2R)別途追加購入し使っていた。

Surface Book は

Microsoft Office Home & Business Premium プラス Office 365 サービス

が用意されているのだけど、同様に Access が含まれない Office スイートであり、Office 2016 へのバージョンアップが可能。次々と機能が追加されていくであろう Office 2016 バージョンを使いたいのだけど、ここで発生する問題は Click-to-Run製品(C2R) の仕様による制限。

Office 2016 購入に関する ご注意

にある内容通り、Office 2016 にバージョンアップすると Access 2013(C2R)がインストールできない。

シミュレーション環境なので実際とは異なるかもですよ

Access 2013(C2R) を先にインストールしても制限があり、続行すると Access はアンインストールされることになる。

シミュレーション環境なので...

で、どうするか。

Office 365 Access アプリ -52- Download in Excel

Access アプリ の新機能
データシート ビュー から Excel ファイル(xlsx) のダウンロードができるようになった。
すでに配置された Access アプリ も順次アップグレードされていくはず。

Introducing a new feature in Access 2013 web apps—Download in Excel

しばらく前に情報は公開されていたのだけど、手元の環境で使用できるなったのでさらっと確認。小難しい内容は全くなく、コマンドボタンが自動的に配備されるから特段の手順などなく使えるようになる。特徴は 生成された Excel ファイルをダウンロードするということと、ダウンロードするとき最新のレコードを出力するから ビュー に表示されている レコード と異なる場合があること。

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 リンク テーブルを。

Office 365 Access アプリ -51- Access 用コンテンツ アプリ - 5

どこに保管されるのだろうと思って。

• Binding オブジェクト / Binding object
• Settings オブジェクト / Settings object

Access アプリ を終了してから再度起動しても同じ情報を取得できるのだからきっとデータベース上にあるんだろうと思って確かめてみただけ。

SELECT [ID]
      ,[ObjectName]
      ,[Definition]
  FROM [AccessSystem].[Objects]
 WHERE [ObjectTypeNumber] = 112

ここでした。

Office 365 Access アプリ -50- Access 用コンテンツ アプリ - 4

Binding.getDataAsync メソッド / Binding.getDataAsync method  options  パラメータのうち、filterType / rows について確認してみた。

FilterType 列挙体 / FilterType enumeration / "thisRow"

配置はこの状態

Office 365 Access アプリ -49- Access 用コンテンツ アプリ - 3

SPC 2014 で公開されていた "Access 用コンテンツ アプリ / Contents apps for Access" の Bing Maps サンプル が更新されていたので。

Office 365 Access アプリ -48- Access 用コンテンツ アプリ - 2

"Access アプリ / Access app" だけだど表現が乏しいから、"Access 用コンテンツ アプリ / Contents apps for Access" を使ってグラフの表示をやってみた。
スクラッチで実装するのは避けて Chart.js を使ってみることにした。Canvas を使用するけど、

Office 365 と Internet Explorer に関する FAQ

にある通り対応については問題なかろうかと。
で、出来上がり。

Access 2013 Runtime ServicePack 1 公開

必要ならばダウンロードしておけばよい。

• Microsoft Access 2013 Runtime ja-jp
• Microsoft Access 2013 Runtime en-us

Office 365 Access アプリ -47- Access 用コンテンツ アプリ - 1

一応やっておかないとかなと。MSDN ライブラリだけ読んでやってみる。

• Access 用コンテンツ アプリ / Content apps for Access
• JavaScript API for Office (v1.1) / JavaScript API for Office (v1.1)

とりあえず、このような構成。

そしてこんな感じになる。

"レコード移動時 / OnCurrent" や "レコード更新時 / AfterUpdate"などイベントでフィールドの値が取れたりすればいいかなと。