この記事では、データベースを設計・作成していきます。
データベースはVBA在庫管理システムVer01のデータ保管ファイル 在庫管理DATA.xlsxを改修して、アクセスにテーブルを作成していきます。
また、システムはVBA在庫管理システムVer01を改修して、アクセスDBと連携させていきます。
VBA在庫管理システムVer02
ブックの準備
まずは、デスクトップに在庫管理02フォルダを作成し、プログラム用と帳票出力用のエクセルブックと、データ保管用のアクセスブックを用意して下さい。
①プログラム用：在庫管理SYSTEM02.xlsm
②データ保管用：在庫管理DATA.accdb
③帳票類出力用：在庫管理REPORT.xlsx
①プログラム用ブックは、VBA在庫管理システムVer01のプログラム用ブックをコピーして使用していきます。
ブック名は在庫管理SYSTEM02.xlsmと変更し、Ver02のプログラム用ブックとします。
②データ保管用ブックは、アクセスDBを用意します。
アクセスを開き、空のデータベースを選択して、ブック名を在庫管理DATA.accdbとして保存します。
③帳票類出力用ブックは、VBA在庫管理システムVer01の帳票出力用ブックをコピーして使用していきます。
アクセスDBの準備
データ保管用：在庫管理DATA.accdb
データベースの正規化については、下記の記事で解説していますので、参考にして下さい。
M_商品テーブル
テーブル設計
在庫管理DATA.xlsx のM_商品シートの導出項目フィールドである、出庫済み・出庫予定・入庫済み・入庫予定・現在在庫・有効在庫・発注フラグフィールドを排除します。
今回は、VBA在庫管理システムVer01のシステムを活用するため、また、解説を簡素化するために、第１正規化までの上記の構造で、アクセスにテーブルを作成します。
実際には運用に合わせ、テーブル設計をします。
例えば、正規化を進め、商品マスタと在庫マスタ・発注先マスタにテーブルは分けた方がいいでしょう。
小さなシステムでも、テーブル設計の重要さはかわりません。エクセルベースでテーブルを作成するときも、実際の運用状況や正規化を意識しておくといいでしょう。
テーブル作成
M_商品のテーブルを作成していきます。
[作成]-[テーブルデザイン]を選択します。
テーブルがデザインビューで表示されたら、各フィールドのフィールド名とデータ型を設定します。
主キーを設定します。
商品IDを主キーとするので、商品ID行を選択し、[テーブルデザイン]-[主キー]をクリックします。
行セレクタに鍵マークが表示され、主キーの設定は完了です。
構造設定が終わったら、[テーブルデザイン]-[表示]-[データシートビュー]を選択して、データシートビューを開きます。
テーブルの表示方法は、構造設定をするデザインビューと、データの表示や操作をするデータシートビューがあります。
[名前を付けて保存]ダイアログが表示されるので、テーブル名をM_商品として保存して下さい。
サンプルレコードを直接入力します。
M_商品テーブルができました。
T_入出庫テーブル
テーブル設計
在庫管理DATA.xlsxのT_入出庫シートを基にして、T_入庫テーブルとT_出庫テーブルに分割します。
この構造で、アクセスにテーブルを作成します。
テーブル作成
M_商品のテーブル作成を参考にして、T_入庫テーブルを作成していきます。
デザインビューで各フィールド名とデータ型を設定します。
また、入庫IDを主キーとして、設定します。
構造設定が終わったら、データシートビューでサンプルレコードを直接入力します。
T_入庫テーブルができました。
T_入庫テーブルと同様に、T_出庫テーブルを作成します。
デザインビューで各フィールド名とデータ型を設定します。
また、出庫IDを主キーとして、設定します。
構造設定が終わったら、データシートビューでサンプルレコードを直接入力します。
T_出庫テーブルができました。
リレーションシップの作成
アクセスDBはリレーショナブルデータベースの構造をもち、複数のテーブルの共通フィールドを関連付けることができます。この関連付けのことをリレーションシップと呼びます。
[データベースツール]-[リレーションシップ]を選択します。
リレーションシップウィンドウが表示されます。[テーブルの表示]ダイアログボックスが表示される場合がありますが、あまり使わないので閉じておきます。
ナビゲーションウィンドウのテーブルを選択し、リレーションシップウィンドウにドラッグすると、テーブルのフィールドリストが表示されます。
M_商品・T_入庫・T_出庫の３つのテーブルを順次表示させます。
フィールドリストのリストがすべて表示されていない場合は、フィールドリストの下方をポイントして矢印をドラッグして調整します。
M_商品テーブルの商品IDフィールドを選択し、T_入庫テーブルの商品IDフィールドへドラッグします。[リレーションシップ]ダイアログが表示されるので、[参照整合性]にチェックを入れ、[作成]ボタンをクリックします。
テーブル間に結合線が表示されます。主キーのテーブル側に[１]、関連テーブル側に[∞]が表示され、リレーションシップが設定できました。
同様に、M_商品テーブルとT_出庫テーブルもリレーションシップを設定します。
リレーションシップの設定ができました。
プログラムブックの準備
プログラム用：在庫管理SYSTEM02.xlsm
プログラムブックはVer01をコピーしたものなので、オブジェクトやコードが入っています。Ver02の作成にあたっては、データベースとの連携部分を中心に、改修をおこなっていきます。
また、データベースとの連携にはADO機能を使用していきますので、参照設定をしておきましょう。
VBEの[ツール]–[参照設定]をクリックし、
[Microsoft ActiveX Data Objects x.x Library]にチェックを入れ、[OK]を押します。
アクセスとのADOを用いた接続・切断については、下記の記事で解説していますので、参考にして下さい。
データベースの保管位置設定
アクセスDBはデスクトップの在庫管理02フォルダに保管しています。
保管位置は標準モジュール"設定"の中で変数に取り込まれますので、フォルダ名を修正します。
Option Explicit Public 画面高さ As Long Public 高さ割合 As Double Public データ位置 As String Sub InitialSetting() 'サイズ調整 ActiveWindow.WindowState = xlMaximized 画面高さ = Application.Height 高さ割合 = 0.9 ActiveWindow.WindowState = xlMinimized 'データ位置の設定 ***は任意 データ位置 = "C:¥Users¥***¥Desktop¥在庫管理02¥" ←ここを修正 'メニュー画面の表示 frmMainMenu.Show End Sub
データベースの接続/切断設定
標準モジュール"DB接続切断"を作成し、データベースの接続/切断のサブプロシージャを書き込みます。
接続
データベース接続のサブプロシージャを標準モジュール"DB接続切断"に作成します。
Option Explicit Public adoCn As Object Sub DB接続() Dim FileName As String FileName = "在庫管理DATA.accdb" '''''''''' DB接続 '''''''''' Set adoCn = CreateObject("ADODB.Connection") adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & データ位置 & FileName & ";" adoCn.CursorLocation = 3 '''''''''''''''''''''''''''''''''''''''' End Sub
@1. 変数の宣言
宣言領域で、 ADOコネクションオブジェクトをPublicで宣言します。
@2. 変数の格納
AccessDBのファイル名を変数に取り込みます。
@3. データベース接続
adoCnオブジェクトを作成します。
adoCnオブジェクトのOpenメソッドを使用し、データベースと接続します。
パラメータのProviderはAccessの固有値なので、Microsoft.ACE.OLEDB.12.0とします。Date Sourceは変数を使用し、DBのフルパスを設定します。
クライアント側でカーソルを移動できるように設定するため、CursorLocation = 3 に設定します。
＊詳細は省きますが、CursorLocation = 3(adUseClient)を設定すると、レコードセットのRecordCountを取得できるので、設定しておきましょう。
切断
データベース切断のサブプロシージャを標準モジュール"DB接続切断"に作成します。
Sub DB切断() '''''''''' DB切断 '''''''''' adoCn.Close Set adoCn = Nothing '''''''''''''''''''''''''''''''''''''''' End Sub
@1. データベース切断
adoCnオブジェクトのCloseメソッドを使用し、データベース接続を切断します。
adoCnオブジェクトを破棄して終了です。
データベースの作成と、VBAへの取り込みの準備が整いました。
次回から、商品マスタ画面を作成していきます。
まずは、マスタデータ取得のプログラムを作成していきます。
www.minizaiko.com
