ous">

小さな在庫管理

在庫管理導入からシステム作成まで詳細解説!

データベースの作成 [VBA在庫管理システムVer02#02]

この記事では、データベースを設計・作成していきます。

データベースは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のシステムを活用するため、また、解説を簡素化するために、第1正規化までの上記の構造で、アクセスにテーブルを作成します。


実際には運用に合わせ、テーブル設計をします。
例えば、正規化を進め、商品マスタと在庫マスタ・発注先マスタにテーブルは分けた方がいいでしょう。
小さなシステムでも、テーブル設計の重要さはかわりません。エクセルベースでテーブルを作成するときも、実際の運用状況や正規化を意識しておくといいでしょう。

 

テーブル作成

M_商品のテーブルを作成していきます。

[作成]-[テーブルデザイン]を選択します。

テーブルがデザインビューで表示されたら、各フィールドのフィールド名とデータ型を設定します。

主キーを設定します。
商品IDを主キーとするので、商品ID行を選択し、[テーブルデザイン]-[主キー]をクリックします。

行セレクタに鍵マークが表示され、主キーの設定は完了です。

構造設定が終わったら、[テーブルデザイン]-[表示]-[データシートビュー]を選択して、データシートビューを開きます。
テーブルの表示方法は、構造設定をするデザインビューと、データの表示や操作をするデータシートビューがあります。

[名前を付けて保存]ダイアログが表示されるので、テーブル名をM_商品として保存して下さい。

サンプルレコードを直接入力します。

M_商品テーブルができました。

 

T_入出庫テーブル

テーブル設計

在庫管理DATA.xlsxのT_入出庫シートを基にして、T_入庫テーブルとT_出庫テーブルに分割します。

この構造で、アクセスにテーブルを作成します。

テーブル作成

M_商品のテーブル作成を参考にして、T_入庫テーブルを作成していきます。

デザインビューで各フィールド名とデータ型を設定します。
また、入庫IDを主キーとして、設定します。

構造設定が終わったら、データシートビューでサンプルレコードを直接入力します。

T_入庫テーブルができました。


T_入庫テーブルと同様に、T_出庫テーブルを作成します。

デザインビューで各フィールド名とデータ型を設定します。
また、出庫IDを主キーとして、設定します。

構造設定が終わったら、データシートビューでサンプルレコードを直接入力します。

T_出庫テーブルができました。

リレーションシップの作成

アクセスDBはリレーショナブルデータベースの構造をもち、複数のテーブルの共通フィールドを関連付けることができます。この関連付けのことをリレーションシップと呼びます。

[データベースツール]-[リレーションシップ]を選択します。

リレーションシップウィンドウが表示されます。[テーブルの表示]ダイアログボックスが表示される場合がありますが、あまり使わないので閉じておきます。

ナビゲーションウィンドウのテーブルを選択し、リレーションシップウィンドウにドラッグすると、テーブルのフィールドリストが表示されます。
M_商品・T_入庫・T_出庫の3つのテーブルを順次表示させます。

フィールドリストのリストがすべて表示されていない場合は、フィールドリストの下方をポイントして矢印をドラッグして調整します。

M_商品テーブルの商品IDフィールドを選択し、T_入庫テーブルの商品IDフィールドへドラッグします。[リレーションシップ]ダイアログが表示されるので、[参照整合性]にチェックを入れ、[作成]ボタンをクリックします。

テーブル間に結合線が表示されます。主キーのテーブル側に[1]、関連テーブル側に[∞]が表示され、リレーションシップが設定できました。

同様に、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