正規化したテーブルとVBAの連携例 1/2 [VBA在庫管理システムVer02#05]

前回までで、商品マスタ画面の動作設定が完了しました。
ここから、商品マスタ画面を例に、正規化したテーブルとVBAシステムの連携方法を２回に渡って解説していきます。

今回は、テーブルの正規化からVBAのマスタデータ取得までを解説していきます。

※修正例なので、解説用システムのVBA在庫管理システムVer02には改修をいれません。テスト的に実施する場合は、プログラムブックやデータベースはコピーしバックアップしておき、テスト終了後、バックアップデータに戻しましょう。
この様に、バックアップが手軽に取れるのも、アクセスを利用するメリットとなります。

VBA在庫管理システムVer02 改

アクセスDBのテーブル設定

VBA在庫管理システムVer02は、Ver01の改修で進めていきますが、システムの制約が大きく、テーブル構造がいまいちです。

一般的には、要件定義の後にテーブルを設計し、テーブル構造に対応した画面設計を行います。今回の様な画面設計に合わせたテーブル設計は、初期設計の良否に左右されて、安定度の低いシステムになりがちです。

そこで、テーブルの正規化を進め、正規化されたテーブルに対応させたVBAシステムの作成例を解説していきます。
　

マスタテーブル

テーブル設計

在庫管理DATA.accdbのM_商品テーブルを第３正規化まですすめ、３つのテーブルに分割します。

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

テーブル作成

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

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

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

M_発注先のテーブルを作成していきます。

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

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

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

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

構造設定が終わったら、データシートビューでテーブルを確認します。データは商品ID＝1の在庫データのみ、直接入力します。

３つのマスタテーブルが作成できました。
　

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

リレーションシップウィンドウに、M_発注先・M_商品・M_商品在庫の３つのテーブルを順次表示させ、リレーションシップを設定します。

リレーションシップの設定ができました。
　

商品マスタ画面の初期設定

商品マスタ画面の作成

データベースのテーブル設計の変更に伴い、商品マスタ画面を修正します。

※画面修正後の商品マスタ画面

商品情報と商品在庫情報にフレームを分割し、それぞれのテーブルの操作を分けます。商品マスタに登録した商品のうち、在庫管理したいものだけを商品在庫マスタに登録する構造となります。

発注先のテキストボックスは、発注先マスタの格納用に、コンボボックスに変更します。
また、商品リストは商品情報の表示のみに修正します。

登録/修正のフレームは商品情報と商品在庫情報で同一の構造となるので、オブジェクト名はそれぞれ、_Item・_Stockを接尾語として追加します。
　

商品マスタデータ

クエリの作成

商品マスタデータの取り込み用クエリを作成します。

商品マスタの発注先IDフィールドは関連テーブルのM_発注先の発注先名称を表示します。
　

マスターデータ取得

Q_M商品を取り込むプロシージャーを作成します。

Sub GetItemMaster()

'データベース接続
Call DB接続

'''''''''' レコード抽出 ''''''''''

'レコードセットオブジェクトの作成
Dim adoRs As Object
Set adoRs = CreateObject("ADODB.Recordset")

'SQL文の変数への取り込み
Dim strSQL As String
strSQL = "SELECT*FROM Q_M商品"

'レコードセットの取得
adoRs.Open strSQL, adoCn

'変数の設定
Dim i As Long
ReDim aryItemMaster(adoRs.RecordCount - 1, 6) As Variant
i = 0

'配列変数への書込み
Do Until adoRs.EOF
    aryItemMaster(i, 0) = adoRs!商品ID
    aryItemMaster(i, 1) = adoRs!商品名称
    aryItemMaster(i, 2) = adoRs!登録日
    aryItemMaster(i, 3) = adoRs!更新日
    aryItemMaster(i, 4) = adoRs!発注先名称
    aryItemMaster(i, 5) = adoRs!発注単位
    aryItemMaster(i, 6) = adoRs!梱包数
    i = i + 1                    'カウンターを加算

    adoRs.MoveNext       'カーソルを1行下へ
Loop

'レコードセットオブジェクトの破棄
adoRs.Close
Set adoRs = Nothing

''''''''''''''''''''''''''''''''''''''''

'データベース切断
Call DB切断

End Sub

発注先マスタデータ

クエリの作成

発注先マスタデータの取り込み用クエリを作成します。

　

マスターデータ取得

Q_M発注先を取り込むプロシージャーを作成します。

Sub GetSupplierMaster()

'データベース接続
Call DB接続

'''''''''' レコード抽出 ''''''''''

'レコードセットオブジェクトの作成
Dim adoRs As Object
Set adoRs = CreateObject("ADODB.Recordset")

'SQL文の変数への取り込み
Dim strSQL As String
strSQL = "SELECT*FROM Q_M発注先"

'レコードセットの取得
adoRs.Open strSQL, adoCn

'変数の設定
Dim i As Long
ReDim arySupplierMaster(adoRs.RecordCount - 1, 1) As Variant
i = 0

'配列変数への書込み
Do Until adoRs.EOF
    arySupplierMaster(i, 0) = adoRs!発注先ID
    arySupplierMaster(i, 1) = adoRs!発注先名称
    i = i + 1                    'カウンターを加算

    adoRs.MoveNext       'カーソルを1行下へ
Loop

'レコードセットオブジェクトの破棄
adoRs.Close
Set adoRs = Nothing

''''''''''''''''''''''''''''''''''''''''

'データベース切断
Call DB切断

End Sub

@1. 変数の宣言
配列変数arySupplierMasterは、aryItemMasterと同様に、宣言セクションでPublicで宣言します。
　

データの格納

マスタデータは、UserForm_Initialize プロシージャで、各コントロールに格納します。
商品マスタは商品名称コンボボックスと商品リストボックス、発注先マスタは発注先コンボボックスに格納します。

Private Sub UserForm_Initialize()

'----サイズ調整---------------------------------------------------------------
    Me.Zoom = Me.Zoom * ((画面高さ * 高さ割合) / Me.Height)
    Me.Width = Me.Width * ((画面高さ * 高さ割合) / Me.Height)
    Me.Height = Me.Height * ((画面高さ * 高さ割合) / Me.Height)
'-----------------------------------------------------------------------------

'商品マスタ情報の取得
Call GetItemMaster

'商品名称コンボボックスの設定
With cmbItemName
    .Clear
    .ColumnCount = 10
    .ColumnWidths = "0;200;0;0;0;0;0;0;0;0"
    .List = aryItemMaster()
End With

'商品リストボックスの設定
With lstItemMaster
    .Clear
    .ColumnCount = 10
    .ColumnWidths = "40;250;0;0;150;60;60;60;60;60"

    .List = aryItemMaster()
End With

'発注先マスタ情報の取得
Call GetSupplierMaster

'発注先コンボボックスの設定
With cmbSupplier
    .Clear
    .ColumnCount = 2
    .ColumnWidths = "0;100"
    .List = arySupplierMaster()
End With

End Sub

※データ格納後の商品マスタ画面

商品名称コンボボックスと発注先コンボボックスにマスタデータが取り込まれているか、プルダウンを確認しましょう。

これで、テーブルの正規化からVBAのマスタデータ取得まで完了しました。

次回は、VBAの動作設定からデータベースへの書き込みまでを解説していきます。
www.minizaiko.com