改修神エクセルの活用：DB準備(Access)

ソフトウェア
PixalineによるPixabayからの画像
2022.05.31

改修した神エクセル帳票から回収したデータを格納するため、今回は、Officeの構成アプリとして企業のPCに良く入っている、Accessを使ってデータベースを作成します。さらに、フリーのSQLクライアントである、A5:SQL Mk-2を使って外部から空のDBファイルを操作し、遠隔操作っぽくデータ格納用のテーブルを作ってみます。

DBの準備：Access

今回はAccess2016を使ってデータベース（accdbファイル）を作成しました。

空のDB（accdbファイル）を作成する

Access2016を開き空のDBを作成（kamiExcelDB）します（表1）。

1Access2016を起動し「空のデータベース」アイコンをクリック
2フォルダマーク（右図青枠）をクリックし、保存先フォルダを参照（kamiExcelDB.accdb）、作成するデータベースのファイル名を入力（D:\DB\Access）します。
3作成したデータベース（accdbファイル）が開いたらそのまま閉じます。
表1：Access2016で空のデータベースを作成する

作成したDBの確認

エクスプローラーから空のデータベースの保存先フォルダ（D:\DB\Access）を開き、入力ファイル名（kamiExcelDB.accdb）で、DB（accdbファイル）が作成されている事を確認します（図1）。

図1：エクスプローラーから所定フォルダを開きDB（accdbファイル）が作成されている事を確認

作成したDB（kamiExcelDB.accdb）を開き、空ファイルである事が確認出来たら「コンテンツの有効化」ボタンをクリックします（図2）。

図2：作成したDB（accdbファイル）を開き、空ファイルである事を確認

A5:SQL Mk-2を取得し、作成したDBに接続する

「A5:SQL Mk-2」は、フリーのSQLクライアントです。開発や運用の現場でも使われることが多く、たいへんに使いやすいので、ここでも活用したいと思います。

A5:SQL Mk-2の取得

開発元のホームページを開き、Vectorからダウンロードをクリックします（図3赤枠）。

図3：A5:SQL Mk-2の開発元ホームページ

Vectorのダウンロードリンクページに遷移するので、ダウンロードページへをクリックします。今回使うAccess2016が32bit版の為、これに揃えて32bit版を取得したいので、図4赤枠ダウンロードページへをクリックします。

図4：A5:SQL Mk-2のVectorダウンロードリンクページ

A5:SQL Mk-2（32bit版）のダウンロードページに飛ぶので、今すぐダウンロード（図5赤枠）をクリックします。ダウンロードが始まるので、任意のフォルダに保存します。

図5：A5:SQL Mk-2（32bit版）のVectorダウンロードページ

A5:SQL Mk-2の初期設定

任意のフォルダにダウンロードしたファイル（A5:SQL Mk-2（32bit版）のzipファイル）を解凍し、A5:SQL Mk-2を使うための初期設定を行います（表2）。

1ダウンロードしたZIPファイルを、適宜解凍します。
2解凍フォルダ(本版では、a5m2_2.17.2_x86)を、適当な場所に配置する。今回はCドライブ直下に配置
3配置したフォルダを開き「A5M2.exe」を起動します。
4A5:SQL Mk-2の設定ファイルの保存先を選択する画面が表示される。デフォルトは「レジストリ」ですが、今回は「設定ファイル」を選択します。
※メインフォルダ配下にPortableフォルダが作成され、設定ファイルがそこに集約されるので、メインフォルダごとコピーすることで、別端末でもすぐ使えるようになります。
5パスワード指定を求める画面が表示される。個人的に使うので「いいえ」を選択
6最初に接続するデータベースの登録を促す画面が表示されるので「OK」で閉じる。続いて「データベースの追加と削除」画面が表示されます。
→次章、表3-2に続く
表2：A5:SQL Mk-2の初期設定

A5:SQL Mk-2から作成したDB（accdbファイル）へ接続する

初期設定の終わったA5:SQL Mk-2から、最初に作成したAccessデータベース（accdbファイル）に接続してみます（表3）。※前章から引き続きDBへの接続設定を行う場合は、表3-2から行ってください。

1「A5M2.exe」を起動し、メニュー[データベース]→[データベースの追加と削除]を選択
2「データベースの追加と削除」画面が表示されたら「追加」ボタンをクリックする。
3「追加するデータベースの接続タイプを選択」画面が表示されます。
今回は「ADO(OLE DB,ODBC)」をクリックします。
4「データベースの内容を登録」画面が表示されたら、フォルダ参照ボタン（右図赤枠）をクリックします。
5「データリンクプロパティ」画面-[プロバイダー]タブが表示されたら「Microsoft Office 16.0 Access Database Engine OLE DB Provider」を選択し「次へ」ボタンをクリックします。
6「データリンクプロパティ」画面-[接続]タブが表示されたら、データソース欄に、接続する「accdbファイル」へのフルパスを入力します。データソース欄を入力したら「接続のテスト」ボタンをクリックします。
7ここまでの設定に問題なければ「接続のテストに成功しました」メッセージが表示されます。接続のテストに成功したら「OK」をクリックして「データリンクプロパティ」画面を閉じます。
8「データベースの内容を登録」画面に戻り、接続文字列欄（右図赤枠）に、接続文字列が入力されている事を確認し「OK」ボタンをクリックします。
9「A5:SQL Mk-2で識別するデータベース別名を入力」画面が表示されたら、A5:SQL Mk-2内で使用するデータベース識別名（ここではkami-excel-db）を入力し「OK」ボタンをクリックします。
10「データベースの追加と削除」画面に戻ったら、設定したDB（今回はkami-excel-db）が追加されている事を確認し「閉じる」ボタンをクリックします。
11本画面に戻り、データベースツリーに設定したDB（kami-excel-db）が追加されている事を確認できたら、ダブルクリック（ここではkami-excel-db）します。
12「データベースログイン」画面が表示されたら、今回はそのまま「接続」ボタンをクリックします。
13接続が確立すると、データベースツリーに設定したDB（kami-excel-db）の各種メニューが展開します。
表3：A5:SQL Mk-2から作成したDB（accdbファイル）へ接続する

A5:SQL Mk-2から接続したDBにCREATE文を流す

任意のaccdbファイルとの接続設定ができたので、A5:SQL Mk-2から前回、改修神エクセルの活用：DB準備(データ定義)で作成した、CREATE文を流し込んでみます。

1A5:SQL Mk-2を起動し、メニュー[ファイル]→[新規]を選択します
2「新規ドキュメント」画面が表示されたら「SQL」を選択します。
3空白Queryシート（ここでは「Query-2」シート）が追加されます。
4Queryシートの左上ドロップダウンリストより、接続先DB（今回は、kami-excel-db）を選択します。前回、改修神エクセルの活用：DB準備(データ定義)で作成した、※CREATE文を貼付けます。「実行」ボタン（右図赤枠の「▶」アイコン）をクリックします。
5「データベースログイン」画面が表示されたら、今回はそのまま「接続」ボタンをクリックします。
6本画面に戻ると、画面左下に「コンソール」子画面が表示され、SQLの処理結果が表示されます。特にエラーが出力されていなければ、問題ありません。
7データベースツリーの接続先DB（kami-excel-db）を右クリック→[データベース情報の再読み込み]を選択します。
8接続先DB（kami-excel-db）ツリーのテーブルブランチを展開し、先程実行したCREATE文により作成されたテーブル（T_UIDINFO）が、表示されることを確認します。
表4：A5:SQL Mk-2からCREATE文を流し込む

※表4-4で実行したCREAT文は、以下の通り。前回、改修神エクセルの活用：DB準備(データ定義)で作成した、CREATE文を流し込みました。

CREATE TABLE T_UIDINFO (
    user_id VARCHAR(8) NOT NULL,
    uname_kj VARCHAR(128) NOT NULL,
    uname_kana VARCHAR(128) NOT NULL,
    eｍp_num VARCHAR(8) NOT NULL,
    department VARCHAR(128) NOT NULL,
    division VARCHAR(128) NOT NULL,
    unit VARCHAR(128),
    extension_num INTEGER,
    birthday DATE NOT NULL,
    email_add VARCHAR(28) NOT NULL,
    access_lv INTEGER NOT NULL,
    option_jyuki INTEGER DEFAULT 2,
    option_dev INTEGER DEFAULT 2,
    remarks VARCHAR(128),
    application_num VARCHAR(7) NOT NULL,
    del_flg INTEGER NOT NULL,
    reg_date DATE,
    up_date DATE,
CONSTRAINT T_UIDINFO_PK PRIMARY KEY (user_id)
)

DBに作成したテーブルのレイアウトを確認する

A5:SQL Mk-2からCREATE文を流し込んだDB（accdbファイル）をAccessで開き、作成したテーブルのレイアウトを確認してみます。

1テーブル作成後のDB（kamiExcelDB.accdb）をダブルクリックし、Accessで開きます。
2前章でCREATE文から作成さしたテーブル（T_UIDINFO）を右クリックし、メニュー→[デザインビュー]を選択します。
3前章で実行したCREATE文に記載されたカラム名と並び順で、フィールドが作成されている事を確認します。
各フィールドのデータ型も、CREATE文で記載した通りになりますが、※Access仕様の表記になるので読み替えて確認します。
表5：Accessからテーブル作成後のaccdbを開く

※表5-3でデータ型の確認をするとき、CREATE文で記載したデータ型（SQL標準構文）と、Access仕様の表記が違うので、表6を参考に読み替えて下さい。

№SQL標準構文のデータ型Access仕様のデータ型
1VARCHAR型短いテキスト型
2INTEGER型数値型
3DATE型日付/時刻型
表6：SQL標準構文とAccess仕様のデータ型の表記違いについて

補足：ミドルウェア選択によるCREAT文の挙動の違いについて

表3-3「追加するデータベースの接続タイプを選択」画面で、ミドルウェアの選択をしますが、ここで、「Microsoft Access（ODBC経由）」を選択しても、DB（accdbファイル）との接続は確立します（図6）。

図6：Microsoft Access Driverの接続設定、対象のDB(accdbファイル）へのフルパスを指定するだけで接続可能

「Microsoft Access Driver」と言うODBCドライバーで、業界標準のミドルウェアになりますが、実際に接続して、今回使用したCREATE文を実行すると、ステートメントエラーが出力され、テーブルが作成できませんでした（図7）。標準仕様のDB接続で汎用性も高いと思われますが、利用場面によりAccess仕様との違いを吸収できないこともある様です。

図7：「Microsoft Access Driver」(ODBCドライバー)経由の接続では、実行するSQLに間違いが無くても、Access仕様との違いを吸収しきれず、ステートメントエラーが出力される事がある

一方、表3-5で選択した「Microsoft Office 16.0 Access Database Engine OLE DB Provider」は、Office 2016インストール時に、自動的にインストールされるOLE DBドライバーです。Microsoft独自の機能拡張が施されている為、今回のCREATE文の実行時も、SQL標準構文とAccess仕様との違いを吸収して、使いやすいSQLクライアント環境が提供されます。

関連記事

参考文献

コメント

タイトルとURLをコピーしました