Access学習メモ 0-11. ExcelがAccessに化ける

ExcelとAccessを家電に例えると、

Excelは炊飯器、Accessは土鍋(家電ちゃう)に近いと思う。

炊飯器がないと困る家庭は少なくない。企業にとってのExcelも然り。

これまで人事に総務、経理と経験してきたが、勤怠管理に給与計算、顧客台帳、出納簿と、Excelが生命線となっている職場は少なくなかった。

Excelが万人受けのポジションであることに対し

Accessは明らかに好みが分かれる。

「我が家は炊飯器でご飯炊いてます」と聞いて驚く人はいないと思うが、「土鍋でご飯を炊いてます」と聞くと、「おお！(なんか分からんけど)すごい！」と反応したくなったりする。

炊飯器の設定次第では土鍋で炊いた風の食感も楽しめる現代で、敢えて手入れが大変な土鍋を愛用するぐらい、炊き方にこだわりがあるんだろうな、といった感想に落ち着く。

土鍋って使いこなすには若干ハードルが高く面倒なことも多いが、炊飯器より短い調理時間で美味しいご飯が味わえるのだから、家事が苦手でなければ利用価値はある。

Accessはなんとなく世間的に、土鍋のポジションのような気がする。

そんな土鍋Accessだが、仕事を教わる際の情報整理にも重宝できると発見。

業務を習得する際、最初は説明内容を消化することに全集中し、教わった手順を模倣しながら全体の流れをつかむ。

先輩の付き添いを受けながら見よう見まねの任務、そして次は、それまでの教えの記録を照合しつつ付き添いなしで行ってみる。

そうする内、引継ぎ当初に説明を受けた際には素通りしていた部分の疑問に気づくことがある。

時間の経過や経験値の積み上げと共に、自身の保有情報に階層が生じていく形である。

Accessはそういった「枝分かれする情報の整理整頓」が必要な場面こそ強みを発揮する、ように思う。

学習メモ内でこれまで運用してきたデータはいずれもダミー情報であることから、Accessのサンプル情報もごく限られており、実データ量と仮定すれば、ぶっちゃけExcelを使った方が手っ取り早い。

今回、AccessへインポートするExcelの実データ量は、実際には数万セル。

そういうときこそ本領発揮するのが、たすきリレーを得意とするAccess選手ではないだろうか。

まずはExcelで「ここはおさえておきたい」項目を一覧化する。

項目のタイプに沿ってグループ化し、シート別に分類する。

そのExcelシートがAccessのテーブルとなる形だ。

AccessにはExcelのデータを取り込む(インポートする)機能があらかじめ備わっている。

Excelシートを読み込ませば、Accessが自動的にテーブルを作ってくれる。

デザイン画面からテーブルの骨組み作りに挑むよりは圧倒的に便利だが

テーブルが３つ４つある場合、何度も同じ作業を繰り返すのが意外と手間に感じる。

きれいなシステムを作るより、仕事を憶えることを優先したい時期だからこそ、普段なら苦にならない工程を少しでも短縮したいと考えてしまう。

インポート画面の選択肢の途中で待ちきれなくなり、思わず「完了」としてみたところ、テーブルが静かに完成する。

時短できたと思いきや、すべてのテーブルに主キーフィールドが自動追加されているではないか。

主キー候補はすでにExcelの中に用意できているため、余分なフィールド。

テーブルひとつひとつをデザインビューに戻し、「ID」フィールドを取り除く工程が生じてしまった。これでは本末転倒だ。

そこで、複数テーブルを一括インポート可能なVBAコードを作れないものかと思いつき、チャレンジしてみた。

「テーブル作成」コマンドをもとに、4枚のExcelシートから４つのテーブルを一気に生み出せる方法。

フォーム画面にコマンドを作り、下記モジュールを記入し、AccessとExcelの各ファイルを同一ドライブ上に置き、コマンドをワンクリック。

各テーブルの主キー設定、各フィールドのデータ型は手動で調整する必要がありそうですが、気持ちの上でかなりラクになりました。

Option Compare Database

'Access標準機能のExcelインポート機能を用いて、「受付内容.xlsx」の4つのシートをそれぞれ4つのテーブルとして取り込む作業について

'「テーブル作成」ボタンを押して一括インポート処理を行うためのコードです。

Private Sub テーブル作成_Click()

    '「strFile」というデータ型:文字列(String)の変数を作る

    Dim strFile As String

    'Excelファイルのパスを取得(&は文字列を連結する役割を持つ。この場合はExcelファイル名)

    strFile = CurrentProject.Path & "\受付内容.xlsx"

    'ExcelとAccessでデータをやり取りするための命令句

    DoCmd.TransferSpreadsheet _

        'acImportはインポート(取込)、acExportはエクスポート（出力）

        'acSpreadsheetTypeExcel12Xml, _・・・Excelの形式を知らせる

        'strFile・・・コード冒頭で宣言したデータ型:文字列(String)の箱へExcelデータを格納する

        ' True, ・・・Excel1行目を「フィールド名」とする場合はTrue,　フィールド名を持たせず1行目からデータとして引用する場合はFalse(無名のフィールド名)

        '"○○○○○$"・・・シート名

        acImport , _

        acSpreadsheetTypeExcel12Xml, _

        "T_顧客マスタ", _

        strFile, _

        True, _

        "顧客マスタ$"

    DoCmd.TransferSpreadsheet _

        acImport, _

        acSpreadsheetTypeExcel12Xml, _

        "T_従業員マスタ", _

        strFile, _

        True, _

        "従業員マスタ$"

    DoCmd.TransferSpreadsheet _

        acImport, _

        acSpreadsheetTypeExcel12Xml, _

        "T_申請トラン", _

        strFile, _

        True, _

        "【顧客】申請トラン$"

    DoCmd.TransferSpreadsheet _

        acImport, _

        acSpreadsheetTypeExcel12Xml, _

        "T_受付トラン", _

        strFile, _

        True, _

        "【当社】受付トラン$"

    MsgBox "取込完了"

End Sub