ついにAccessでちゃんとしたシステムを作る機運が高まりました。
これまでFileMakerで自社用にカスタマイズしていたシステムからSaaSへ移行しました。しかし、そのSaaSでは以前のシステムではできていたことが一部できない状態で、その部分的な使用で以前のシステムが生きているという状況です。
FileMakerは僕もそれなりに使えるものの、そのシステムを作った張本人ではないので何か不具合があったり改修する場合には中身を知るところからなのでメンテナンスに難がある状況です。部分的に使うには以前のシステムはかなり複雑ですので、もう少しコンパクトで簡単なシステムが必要となっていました。
使い慣れたExcelやWebを活用できるGWSなんかも考えましたが、費用をあまりかけずに複数名が同時に使用できるシステムということでAccessを使うことにしました。Accessの将来性ってどうなんだろうなぁとかいろいろ思うところもありますが、しばらくは現役で使えるだろうってことで。
Accessはテキストでひと通り勉強はしたものの、ほとんど素人です。いろいろ調べながらシステムを作っていきましょう。
今回はシステムの概要に始まり、CSVファイルをインポートするところまでやってみます。
システムの概要
前述の通りメインはSaaSを使用しており、大部分の業務は行えますが一部の業務だけが不足しているといった状況です。
その業務というのがこの４点。
- 物品購入等の支払い登録
- 予約管理
- 物品の貸出管理
- 特定の利用に対する集計表
いずれも顧客の情報が必要であり、メインのシステムから情報を取得する必要があります。
システム間の連携といえばCSVのインポートということで、まずはAccessでCSVのインポートを行えるようにしていきます。
一般的なCSVファイルのインポート
最終的にVBAでインポート作業を簡略化するのですが、テーブルを作成がてら一般的なCSVファイルのインポートをやっていきます。
CSVファイルは下記サイトで作成したものを使用します。UTF-8 (BOM付き) のファイルをダウンロードできますが、このままAccessで読み込むと文字化けしちゃうので文字コードをANSIにしておきます。メモ帳で開いて文字コードを変えて保存し直すだけです。
Accessで新規DBを作成すると早速テーブルを作るところから始まりますが、一旦そのテーブルは閉じておきます。
メニューの外部データ > 新しいデータソース > ファイルから > テキストファイル と進みます。
CSVファイルはExcelの一種だと思っている人も多いですがテキストファイルです。
ダウンロードしたCSVファイルを選択し、「現在のデータベースの新しいテーブルにソース データをインポートする」で進みます。
サンプルデータが表示されます。ここで文字化けしてなければとりあえずOK。「区切り記号付き」で進みます。
CSVは「comma separated values」の略で、コンマを区切り記号にしたデータ形式のことです。
そういうわけで区切り記号は「コンマ」、「先頭行をフィールド名として使う」にチェックを入れ、テキスト区切り記号は「”」にしておきます。
CSVは文字列のデータを「”」で囲む仕様です。CSVファイルをExcelで開いて保存するとこの「”」がすべて消されて、たまに受け取るシステム側で不具合が起きたりします。他にもいろいろ不都合があって、CSVとExcelは結構相性が悪かったりします。
今回はメインのシステムから顧客情報をすべて一括エクスポートするが、Access側では一部しか使わないという想定です。
不要な列 (フィールド) を選んで「このフィールドはインポートしない」にチェックを入れていきます。
主キーはユニークな値をもった列を指定します。本来であればメインシステム側でもっているIDを設定したいところ。今回はマイナンバーという項目があったので代わりにこれを主キーにしておきます。
ここまで来たら左下の設定をクリック。
インポート定義
この画面でインデックスの設定ができるのでやっておきます。
インデックスはソートや検索の頻度が多い列に設定するとソートや検索の処理が速くなるというもの。ただし、設定し過ぎるとレコードを作成・削除の度にインデックスの更新が発生してかえって処理が遅くなってしまいます。最低限の設定を行います。
設定できたら保存をクリック。
定義名を設定します。この定義名は後にVBAでも使用するので覚えておきます。忘れた場合は先程の画面で「定義」をクリックすると表示されます。
そのまま画面を進めてインポート完了。インポート操作は今回は使いませんが一応保存しておきましょう。
インポート操作の保存は、インポートするファイル名まで同じであればインポートの手順を一気に省略できます。毎回エクスポートをしたファイルというよりも、どちらかといえば同じファイルを編集してはAccessにインポートするという場合に便利ですね。
テーブルができました。これがインポートの手順です。
ただ、結構行程が多いですし、手順を忘れて必要な設定が漏れる可能性もあるので、そういったリスク回避の意味も込めてVBAで自動化します。
とりあえずこのテーブルのレコードは全部削除しておきましょう。
VBAでCSVファイルをインポートする
CSVファイルをインポートするマクロをVBAで作っていきます。
まずマクロの実行ボタンを設置するためのフォームを用意します。作成 > フォームデザインを選びます。どの作り方でも別に問題ありません。
フォームの名前は「F01_インポート」にしておきます。
フォームデザインからボタンを選択してフォーム上に配置します。
コマンドボタンウィザードが出ますがキャンセルで閉じてOK。
ボタン内の表示テキストを「顧客マスタ」にし、プロパティシートを開いてその他タブの名前を「T01_顧客マスタ_インポート」に設定します。これがボタンの名称になります。
イベントタブを開き、クリック時を [イベント プロシージャ] に設定し、右側の「…」をクリックします。
VBEが起動してプロシージャが作成されました。いよいよコードを書いていけますね。
Microsoft Office 16.0 Object Libraryの設定
今回作ろうとしているマクロは、「ファイルを開く」ウィンドウでインポートするCSVファイルを選択する仕様なのですが、これをするにはライブラリの設定が必要です。
VBEのメニューバーにあるツールから「参照設定」。
ライブラリファイルの中から「Microsoft Office 16.0 Object Library」にチェックを入れます。
VBA
参考にしたのはこちらのリンク。
これを少しいじっています。
- 他のインポートにも使い回せるように、引数に対象のテーブルとインポート定義を指定して実行できるように変更
- ファイルを開くウィンドウが最初に表示するフォルダをDBがあるフォルダに変更
- キャンセル時のメッセージは削除、その代わりにインポート完了時のメッセージを追加
Private Function CSVインポート(TARGET_TABLE, IMPORT_DEFINITION)
Dim strpath As String
Dim ret As Integer
'ファイルを開くダイアログ用
Dim dlg As Object, boolResult As Boolean
Dim strFiles As String, i As Long
Dim myStr As String
'オブジェクト変数にFileDialogオブジェクトを代入
Set dlg = Application.FileDialog(msoFileDialogSaveAs)
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False '複数選択可能かを設定
.Title = "CSVファイルのインポート" 'ファイル ダイアログ ボックスのタイトル設定
.InitialFileName = Application.CurrentProject.Path 'DBのあるディレクトリを最初に開く
.InitialView = msoFileDialogViewWebView '初期ビューを設定(バージョンによって無視される)
'ファイル フィルタのコレクション追加
With .Filters
.Clear
.Add "インポートファイル", "*.csv"
End With
If .Show = True Then
myStr = .SelectedItems(1)
strpath = myStr
Else 'キャンセルの場合
myStr = ""
Exit Function
End If
End With
'インポートする
DoCmd.TransferText acImportDelim, IMPORT_DEFINITION, TARGET_TABLE, strpath, True
MsgBox TARGET_TABLE & " にインポート完了しました。"
End Function
Private Sub T01_顧客マスタ_インポート_Click()
Call CSVインポート("T01_顧客マスタ", "T01_顧客マスタ_インポート定義")
End SubDoCmd.TransferTextメソッドは、(変換の種類, インポート定義, テーブル名, CSVファイルのパス, 先頭行をフィールド名にするか) の順番に指定します。この後にも２つ指定できるパラメータがあるのですが、あまり出番は無さそうなので割愛。
まずファイルを開くウィンドウが表示されます。DBと同じフォルダが表示されますので、ここから目的のCSVを選んで開きます。
インポートが完了したらメッセージが表示されます。以上です。
重複したデータは上書き更新にならない
メインのシステムから定期的にCSVファイルをダウンロードして、情報を更新していく仕組みにしたいところ。
そういうわけでCSVファイルを取り込むと新規のレコードは追加され、既存のレコードは上書き更新される形が理想です。
試しに同じファイルをもう一度インポートしてみると、なんと弾かれました。
どうやらこの方法では追加のみの対応で、更新にはならないようです。
いろいろ調べた結果、取込用のテーブルを用意して、更新クエリで既にあるデータの更新、追加クエリで新規のレコードを追加するという方法がよいということがわかりました。
参考にしたのは下記のYahoo!知恵袋。
というわけでこんな感じの構成になりました。
取込用テーブル
「T02_顧客マスタ取込用」というテーブルを作成しました。T01_顧客マスタを複製してレコードを全削除しただけです。
T01_顧客マスタとフィールドの構成は同じ。基本的に中身は空の状態です。
更新クエリ
更新クエリは２つのテーブルで重複したレコードについて差分を上書き更新するものです。
あくまで既にあるレコードのみが対象ですので、新規のレコードは追加されません。
クエリをこのような感じで作成します。メニューで更新を選んでおきます。
まずT01_顧客マスタとT02_顧客マスタ取込用のテーブルを選択し、マイナンバーでリレーションを組んでおきます。
そしてT01_顧客マスタのマイナンバー以外のフィールドを下のエリアに移し、レコードの更新を下記のように記述します。
[T02_顧客マスタ取込用]![氏名]これはフィールドが「氏名」の場合の記述方法で、他のフィールドはそれぞれのフィールド名に変更します。
追加クエリ
更新クエリが既にあるレコードの上書きなのに対し、新規レコードだけを追加するクエリを用意します。
上記のYahoo!知恵袋を参考にやってみたのですがなかなかうまくいかず、下記の記事の通りやってうまくいきました。
SQLビューで下記のように記述します。
INSERT INTO T01_顧客マスタ
SELECT T02_顧客マスタ取込用.*
FROM T02_顧客マスタ取込用 LEFT JOIN T01_顧客マスタ ON T02_顧客マスタ取込用.[マイナンバー] = T01_顧客マスタ.[マイナンバー]
WHERE (((T01_顧客マスタ.[マイナンバー]) Is Null));T01_顧客マスタが元のテーブル、T02_顧客マスタ取込用が取込用のテーブルです。マイナンバーはリレーションを組んでいるキーとなる列です。
SQLを入力後デザインビューで開くとこんな感じ。この通りやってたんだけどなぁ。とりあえず動いたのでよし。
テーブルを結ぶ矢印をダブルクリックすると結合プロパティを確認できます。
削除クエリ
取込用のテーブルは取込が終われば中身は不要なので削除するクエリを用意します。
メニューで「削除」を選んだ上で、T02_顧客マスタ取込用を使えるようにしてテーブルの１番上にある*を移動させるだけです。
VBA
VBAも合わせて直します。
Private Function CSVインポート(TARGET_TABLE, IMPORT_DEFINITION, UPDATE_QUERY, INSERT_QUERY, DELETE_QUERY)
Dim strpath As String
Dim ret As Integer
'ファイルを開くダイアログ用
Dim dlg As Object, boolResult As Boolean
Dim strFiles As String, i As Long
Dim myStr As String
'オブジェクト変数にFileDialogオブジェクトを代入
Set dlg = Application.FileDialog(msoFileDialogSaveAs)
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False '複数選択可能かを設定
.Title = "CSVファイルのインポート" 'ファイル ダイアログ ボックスのタイトル設定
.InitialFileName = Application.CurrentProject.Path 'DBのあるディレクトリを最初に開く
.InitialView = msoFileDialogViewWebView '初期ビューを設定(バージョンによって無視される)
'ファイル フィルタのコレクション追加
With .Filters
.Clear
.Add "インポートファイル", "*.csv"
End With
If .Show = True Then
myStr = .SelectedItems(1)
strpath = myStr
Else 'キャンセルの場合
myStr = ""
Exit Function
End If
End With
'インポートする
DoCmd.TransferText acImportDelim, IMPORT_DEFINITION, TARGET_TABLE, strpath, True
DoCmd.SetWarnings False
DoCmd.OpenQuery UPDATE_QUERY '更新クエリ
DoCmd.OpenQuery INSERT_QUERY '追加クエリ
DoCmd.OpenQuery DELETE_QUERY '削除クエリ
DoCmd.SetWarnings True
MsgBox "インポート完了しました。"
End Function
Private Sub T01_顧客マスタ_インポート_Click()
Call CSVインポート("T02_顧客マスタ取込用", "T02_顧客マスタ取込用_インポート定義", "Q01_顧客マスタ_更新", "Q02_顧客マスタ_追加", "Q03_顧客マスタ取込用_削除")
End Sub直したポイントとしては、CSVインポートの引数に指定する値を、(対象テーブル, インポート定義, 更新クエリ, 追加クエリ, 削除クエリ)と変更しました。
対象テーブルはT02_顧客マスタ取込用に変更します。ついでにインポート定義の名前も、実際に取り込むのはT02_顧客マスタ取込用のテーブルなので、それに合わせて変更しています。
インポートが終わったところで今回用意したクエリを更新→追加→削除の順に実行します。クエリを実行すると確認用のダイアログボックスが表示されるのが、マクロでは邪魔なのでDoCmd.SetWarningsをFalseにして無効にし、クエリを実行し終わったらTrueに戻します。
クエリの実行については下記の記事を参考にしました。
最後のメッセージではテーブルの名前を表示していましたが、この変更で本体となるテーブルの名前が登場しなくなったので、シンプルに「インポート完了しました。」だけを表示するようにしました。
取り込んだCSVに無いレコードに対する処理
取り込んだCSVに新らたなレコードの追加と、既にあるが内容が変わっているレコードの更新は上記の方法でできるようになりました。
僕が実際に作ろうとしているシステムではこれで事足りるのですが、ケースによっては削除されて存在しないレコードに対する処理も必要な場面がありそうです。
顧客マスタに「削除」というフィールドを追加して、Yes/No型にします。
最新のCSVに無いものはレコード削除という手もありますが、それだと既に作成されてリレーションが組まれているデータに影響が出る可能性があります。
そこでレコードは消さずに、代わりに非表示にできるよう削除チェックを入れる方式にします。
更新クエリを用意します。
これは上記の追加クエリの応用で、テーブルを逆にして条件設定しました。
T01_顧客マスタの削除フィールドについて、レコードの更新を「1」にします。これはYes/No型のYesに相当します。
あとはT02_顧客マスタ取込用のマイナンバーフィールドについて、抽出条件「Is Null」を設定して完了。
試しに取込用の方で１件だけレコード削除してクエリを実行してみたらうまくいきました。
クエリがうまく決まると気持ちいいですね。
これで無事CSVファイルを取り込めるようになりました。なかなか大変な作業ではありましたが、これで外部連携ができるようになりましたし、Access VBAもなんとなくわかってきました。
ここまではデータを同期するだけのことなので、ここからようやくスタート地点って感じです。続きは次回へ。
コメント