So-net無料ブログ作成
検索選択

MS Access VBAプロシージャの呼び出し [Accessクエリ]



今回は、VBAプロシージャをMS Accessのクエリから呼び出してみるのである。
SQLポケリでは、AccessではCREATE PROCEDUREやCREATE FUNCTIONは使えないことになっている。このことに間違いはないのだが、ストアドプロシージャが使用できないかのような誤解を招いているようなので、本ブログで訂正したいのである。

そもそも

Accessは、Office製品のひとつなので、VBAが使用できる。VBAはVisual Basic for Applicationだったかしら。Visual Basic言語でOfficeアプリケーションでの業務を自動化するようなプログラムを作成できます。っていうのがVBAのウリなわけです。

SQLは、リレーショナルデータベースへの命令が可能な言語である。Accessはもちろんのこと、SQL ServerやOracleといった大規模システム用のデータベースでも使用できる。
SQLの特徴として「非手続き型言語である」というものがある。一般的なプログラミング言語は、手続き型である。VBAも手続き型言語である。いくつものステートメントが順番に実行されていき、すべての手続きが終了しないと、処理が終了しない。
SQLでは、ひとつのステートメント(命令文)で1回の処理は終了するようにできている。
また、SQLの基本命令には、一般的な手続き型のプログラミング言語には"必ず"と言ってよいほど存在する、IF文のような場合分けの命令や、WHILE文のような繰り返し処理を行う命令文が存在しない。

ムズカシイ話になってきてしまった。簡単にいってしまえば、

SQL基本命令の特徴
 非手続き型
 1ステートメントで1処理が完結
 IFやFORなどの制御命令がない

ということなのである。

SQL拡張命令

SQLの基本命令には、IFやWHILEが存在しないのであるが、拡張命令には存在する。データベースベンダが各社の製品をより使いやすくするために、言語の拡張を行ってきた結果である。
SQL Serverでは、TransactSQLと呼ばれる拡張されたSQLが使用できる。Oracleでは、PL/SQLと呼ばれるものがある。どちらもIFやWHILEといった制御命令が使用できる。

でもって、Accessはどうかというと、もともとVBAやマクロでユーザが機能を拡張できるようになっていたので、わざわざ新しくSQLを拡張しなくても"いいじゃん"的な発想であったのであろう。SQLを拡張することでデータベースエンジンを複雑にするよりも、既存のVBAというしくみを使って、なんとかやりくりしてしまおう、という道を選んだのである。

ストアドプロシージャ

ストアドプロシージャとファンクションは、データベースの中に作成されるプロシージャやファンクションのことである。
SQL基本命令ではなく、SQL拡張命令に分類される。

SQL ServerやOracleでは、CREATE PROCEDUREでプロシージャを作成できるし、CREATE FUNCTIONでファンクションを作成できる。処理の内容は、拡張されたSQL命令で記述する。SQLの基本命令から作成したプロシージャやファンクションを呼び出すことが可能である。

Accessではというと、CREATE PROCEDURE、CREATE FUNCTIONを使用することができない。しかし、VBA Editorを使ってPublic Functionを作成しておけば、SQLからVBAのファンクションを呼び出すことが可能である。

ちょっと作ってみよう。

VBAのコードは、作成タブの「Visual Basic」ボタンをクリックして、Visual Basic for Applicationsの別ウインドウを開いて作成していく。
このウインドウは、いつまで経っても操作性が変化しない。.NETじゃない昔のVBを操作しているようなデジャブ感を味わいつつ、操作性が変わらないのは「いいことだ」としみじみ思いながら、標準モジュールに、ModuleTestを作成して、Public Functionでf_fooを作成してみた。

AccessVBAファンクションを作成.png

.NETじゃなくVBAなので、Return命令は使えない。ファンクションと同じ名前の変数で戻り値を返す。
f_fooファンクションは、引数を倍にして戻すだけ。
当然ながら、VBの文法で処理内容を書くわけです。

f_fooを呼び出すクエリを作成してみる。

AccessVBAファンクションf_fooをクエリから呼び出し.png

SQLビューにしたらこんな感じ。

AccessVBAファンクションf_fooをクエリから呼び出しSQL.png

実行するとこうなる。

AccessVBAファンクションクエリの結果.png

ちゃんと計算されている。

さて、VBAファンクションを作成して、SQLから呼び出すことができた。
VBAファンクションはストアドファンクションと呼ぶべきかどうか...
VBAファンクションはVBAで書かれているものだからなぁ... SQLじゃないし。

ということで、SQLポケリでは、Accessでは、ストアドプロシージャを扱えない、ということになっているのであった。どこかに、書いておくべきかなぁ...
えーと、長い言い訳でした。すみません。



サイト内を検索


nice!(0)  コメント(0) 
共通テーマ:携帯コンテンツ

MS Access ルックアップフィールド [Accessクエリ]



MS Access ルックアップフィールド

前回、パラメータクエリを調べた。
クエリのパラメータ部分に値を投入するのは、ダイアログボックスからでしかできないらしい。VBAマクロであれば、Parameterオブジェクトを使ってやればできなくもない。クエリデザイナやSQLビューでテーブルのある列の値をパラメータとして渡すことができないかとやってみたが、無理だった。

なんとかIDをパラメータで受け取って、なんとか名を返すクエリを作成しておいて、それを別のクエリから呼び出してやれば、面倒な結合作業をする必要がなくなるかな?
と思ったのであるが、パラメータクエリではできないようである。

しかし、Accessにはルックアップフィールドなるものがあり、これを使用すれば、なんとかマスタから、なんとかIDを検索して、なんとか名で表示する、ということが簡単にできるらしい。
やってみるとしよう。なんとかマスタだとわかりにくいので商品マスタにしよう。まずは、商品マスタテーブルを作成するのである。

作成した商品マスタテーブルにレコードを作成

Access商品マスタ.png

商品ID列が、主キーであり、オートナンバー型となっているところがミソ。

注文テーブルを作成

次に商品マスタの主キーである商品IDを参照する、注文テーブルを作成してみる。
注文ID列を主キー、オートナンバー型で作成。
注文日列を日付時刻型で作成。
商品列を「ルックアップウイザード」で作成。

Accessルックアップウイザードを起動.png

Accessルックアップフィールド1.png

Accessルックアップフィールド2.png

Accessルックアップフィールド3.png

Accessルックアップフィールド4.png

Accessルックアップフィールド5.png

Accessルックアップフィールド6.png

これで、商品列がルックアップフィールドになった。商品マスタテーブルを開いているとリレーションを設定する際にエラーになるので注意。
商品列の次に、個数列を作成して、注文テーブルが完成となった。

注文テーブルにレコードを作成してみる

データシートビューに変更して、レコードを作成してみる。
注文IDは自動採番なので、注文日から入力していく。
次は商品列。ここがルックアップフィールドになっている。

Access注文テーブルの商品列がルックアップフィールド.png

このように、プルダウンのリストが表示され、商品マスタの内容が表示されている。

ガムを選んで、個数に1を入れてみた。

Accessルックアップフィールドの商品を選択.png

あら簡単。
これならフォームを作成しなくても、かなりいい線で入力できちゃう。
商品IDを気にしなくてよいのがイイね。

商品マスタテーブルを開いてみると

商品マスタをデータシートビューで表示してみると、以下のようになった。

Access商品マスタテーブルに子レコードができる.png

商品「ガム」のレコードに子レコードが付く感じになっている。子レコードは、注文テーブルの内容だし。ここで入力も可能なようである。

ここまでできるのなら、単価*個数で金額の計算もやってみたいと思うでしょ。

できちゃうのですよ。Accessって"なにげ"にすごいのかも。
まずは、注文テーブルへの入力用のクエリを作成する。

注文テーブル入力用クエリを作成

作成タブに移動して、「クエリデザイン」ボタンをクリックしてクエリを作成する。
テーブルのリストが出てくるので、「商品マスタ」と「注文」のふたつを選択して「追加」する。Ctrlを押しながらクリックすれば、複数選択ができる。

ルックアップウイザードで、参照整合を付けたので、自動的に結合条件(リレーションシップ)が付いている。これは、便利。

フィールドに入力したい項目を列挙していく。
注文日、商品(ルックアップフィールド)、個数、金額(単価*個数で計算)。
このくらいでよいでしょう。

Access注文入力用クエリ.png

注文入力用クエリでレコードを作成

データシートビューに切り替える。
ビューの切り替えは、右下のステータスバーでやると操作しやすいかも。

Access注文入力用クエリをデータシートビューに切り替え.png

こんな感じになるので、後は入力すればよいだけ。

Access注文入力用クエリにデータを入力.png

金額の列は、計算しているので、入力はできない。個数を入力すると自動的に計算される。
すばらしい。





サイト内を検索


nice!(0)  コメント(0) 
共通テーマ:携帯コンテンツ

AREarthroidPro ARで地球を表示するアプリ

Copyright Atsushi Asai Google+朝井淳
[改訂第4版]SQLポケットリファレンス

[改訂第4版]SQLポケットリファレンス

  • 作者: 朝井 淳
  • 出版社/メーカー: 技術評論社
  • 発売日: 2017/02/18
  • メディア: 単行本(ソフトカバー)

[データベースの気持ちがわかる]SQLはじめの一歩 (WEB+DB PRESS plus)

[データベースの気持ちがわかる]SQLはじめの一歩 (WEB+DB PRESS plus)

  • 作者: 朝井 淳
  • 出版社/メーカー: 技術評論社
  • 発売日: 2015/03/03
  • メディア: 単行本(ソフトカバー)
C言語 ポインタが理解できない理由 [改訂新版] (プログラミングの教科書)

C言語 ポインタが理解できない理由 [改訂新版] (プログラミングの教科書)

  • 作者: 朝井 淳
  • 出版社/メーカー: 技術評論社
  • 発売日: 2011/04/08
  • メディア: 単行本(ソフトカバー)

かんたんAndroidアプリ作成入門 (プログラミングの教科書)

かんたんAndroidアプリ作成入門 (プログラミングの教科書)

  • 作者: 朝井 淳
  • 出版社/メーカー: 技術評論社
  • 発売日: 2013/04/16
  • メディア: 単行本(ソフトカバー)