asaworks

自分の技術備忘録と雑談

【accessVBA】accessシステム構築入門 ~第二回 テーブルの役割分担~

 

f:id:asa-works:20161013113815p:plain

 

【accessVBA】accessシステム構築入門 ~第二回 テーブルの役割分担~

 

accessシステム構築入門第二回です。
今回はテーブルの役割分担について考えていきたいと思います。

前回、「トランザクションテーブル」「ワークテーブル」「マスタテーブル」
という3つのテーブルを例に出しました。
それぞれの役割について解説していきます。

 

asa-works.hatenablog.com

 

 

それぞれのテーブルの役割

マスタテーブル

一意なもの、繰り返し使用するものはマスタテーブルとして扱います。

下図の例では「社員マスタ」「部署マスタ」「資格マスタ」を挙げています。

マスタテーブルはあまり変更しないものとして、メンテナンスできる人は一部の人にしておくと良いでしょう。

トランザクションテーブル

蓄積されていく明細データを保管するテーブルです。

マスタにデータがある場合は、マスタのIDや番号など、一意で不変なものを入力します。

なぜこういった作りにするかというと、繰り返されるデータというのは更新があった場合に更新作業がとても大変になってしまうからです。

例えば、部署名を「総務部」、とトランザクションテーブルににそのまま入れていたとします。部署名変更があって「総務人事部」となってしまった場合はどうでしょうか。

「総務部」というデータを全て「総務人事部」に更新するのはとても手間ですよね。

マスタを参照するようにしておけば、こういった更新の際も、マスタ上でのみ変更すれば完了となります。

 

ただし、履歴管理したい場合などは現在のマスタデータと紐付けたデータを退避するなどの事前処理が必要になりますので注意が必要です。

 

トランザクションテーブルだけを見ると内容がサッパリ・・・と思いますが、利用する際はマスタと紐付けることで、見てわかるようなデータになりますので、このテーブルは極力シンプルにしておいて大丈夫です。

 

f:id:asa-works:20170225112143p:plain

 

ワークテーブル

新規データやデータに変更が発生した場合に利用します。

私の場合ですが、ワークテーブルをフォームのソースデータとして利用しています。

新規データの場合→ワークテーブルに投入したデータをイベントでトランザクションテーブルにINSERTする。

更新データの場合トランザクションテーブルから更新するデータを取得し、ワークテーブルにINSERTする。ユーザーはそれを更新し、更新後のデータをイベントでトランザクションテーブルにUPDATEする。

 

ユーザーの意思でアップデート処理を行う為、access特有の「誤って保存してしまった」「うっかりデータ更新してしまった」を防止することができます。

 

accessは便利で作りやすい反面、データを正しく保つことが意外とむずかしいソフトです。このようにテーブルに役割をつけてあげることで、ある程度の正確性は確保できますので、ぜひ参考にしてみてください。

 

 

【情報セキュリティ】もっとも危険なパスワード2016年版

 

f:id:asa-works:20161013230543j:plain

【情報セキュリティ】もっとも危険なパスワード2016年版

 

 

パスワード管理アプリなどを提供する「Keeper」が、2016年にパスワード漏洩してしまった1000万件のデータを分析、危険なパスワードランキングとして発表しました。

今回は、過去の危険なパスワードランキング(2015年、2014年のデータは米SplashDataが発表のもの、
2013年はAdobeから流出したパスワードデータの上位リスト)と比較してみました。

 

f:id:asa-works:20170118161520j:plain

 

栄えあるランキング1位は「123456」。殿堂入りする勢いです。
これに設定してる方はもはや情報を守る気ナシというカンジでしょうか。
また、25位中13が数字の羅列という結果です。こんな単純なパスワード・・・と思いますが、
結局エンドユーザーには「単純なパスワードが危険である」という意識が薄い結果ですね。

講師の仕事をしている中で、office製品の講習要望は多いのですが、セキュリティについては
ほぼ頼まれたことがありません。もっともっとこういった情報を多くの人に知ってもらい、
興味を持ってもらえるように、自分から発信して行きたいと思います。

【accessVBA】accessシステム構築入門 ~第一回 処理の流れを考える~

f:id:asa-works:20161013113815p:plain

【accessVBA】accessシステム構築入門~第一回 処理の流れを考える~

 

access入門と題して、これからaccessでデータ管理をしてみたい!という方向けに、自己復習の意味も兼ねて、色々まとめていきたいと思います。
(という私も独学なので初心者のかもしれませんが・・・)
今回は第一回ということで、大雑把な処理の流れについてです。

最初に、accessでシステムを作ろうと思ったら、データベースをある程度理解しておいたほうがいいのですが、よくわからんー!と投げ出したくなる方が多いはず。

というわけで、私がシステムを作るときに意識しているテーブルと、処理の流れを案内したいと思います。


私の場合、だいたい↓のような流れにすることが多いです。
つくりは人それぞれですが、参考のひとつとしていただけると幸いです。

トランザクションテーブル(ホンモノのデータ。あまり生でいじりたくない。)

②ワークテーブル(①の構造をコピーしたテーブル。データの一時保管場所として使用する。)

③ユーザーフォーム(データソースを②としておく。ユーザーの操作画面。)

処理の流れ=====================================================
新規登録の場合=======
②の全データ削除処理

③のフォームにて入力させる

③のフォーム上の登録ボタンをクリック

②から①へのinsert処理を行う。


更新の場合=======
②の全データ削除処理

③に設けた検索ボックスにて、検索値を入力

①のテーブルを検索、①から②へinsert処理を行う

呼び出したデータの修正を行い、登録(更新)ボタンをクリック

②から①へのupdate処理を行う。

削除の場合=======
②の全データ削除処理

③に設けた検索ボックスにて、検索値を入力

①のテーブルを検索、①から②へinsert処理を行う

削除ボタンをクリックすると、表示されたデータと同じデータを条件に、
①にてdelete処理を行う

===============================================================

その他、上の処理には出てきませんでしたが
④マスタテーブル
⑤マスタと①を紐付けるクエリ

を使いながら、処理を作っていきます。ざっくりとこの辺の流れを把握しておけば、データが不用意に消えてしまったり、上書かれてしまうことはないはず。

 

今回このカテゴリをaccessVBAとしましたが、この処理の流れを意識すれば、ある程度クエリでデータの出し入れをすることも可能と思います。


さて、ここまででトランザクションテーブル、ワークテーブル、マスタテーブル、と
3つの違うテーブルの名前がでてきましたよね。

テーブルを用途別に使い分けることは、実は結構重要なポイントになってきます。次回はテーブルの役割についてまとめたいと思います。

 

asa-works.hatenablog.com

【Excel】ゼロ値のセルにゼロを表示する。空白セルを参照した際に数式の結果を出さないようにする。

f:id:asa-works:20161107230811p:plain

Excel】ゼロ値のセルにゼロを表示する。空白セルを参照した際に数式の結果を出さないようにする。

 C列にはB列を参照する式が入っており、C列には書式設定で曜日が表示されるようにしてあります。


下図の場合、B列が空白であるのに、C列には「土」とはいっています。これはなぜでしょう。


f:id:asa-works:20170108083151j:plain

 

実はExcelでは空白セルは「null」ではなく、「ゼロ」らしいのです。


そして、空白セルを「ゼロ」にするか、しないか、オプションにて指定ができます。

[ファイル]→[オプション]→[詳細設定]この中に「ゼロ値のセルにゼロを表示する」という項目があるので、このチェックを外すことにより、空白セルが完全な「null」になるようです。


f:id:asa-works:20170108083122j:plain

 

項目チェックをはずし、シートに戻ると先程「土」と表示されていた部分が空白になりましたね。

f:id:asa-works:20170108083213j:plain

 

ただし、この方法はブック全体に設定をかけることになりますので注意が必要です。


代替手段としては

①書式設定を「aaa」→「aaa;;」とする

②if関数で空白の場合は空白とする条件式を入れておく


といったような方法があります。(この2つの方法のほうが断然メジャーですよね…)

f:id:asa-works:20170108083229j:plain

 

ちなみに、空白セルってこのゼロ値設定でどう変化するのか気になったので調べてみました。

 

①こちらはゼロ値をゼロと表示する(デフォルト設定)の場合です。

値はもちろん0、type1なので数値です。

f:id:asa-works:20170108083252j:plain

 

②つぎにゼロ値をゼロとしない(チェックをはずした)場合


値は空白になりました。数値はそのままのようです。

f:id:asa-works:20170108083300j:plain


Excel上での日付開始日が1990/1/1、0ですからその一日前が土曜日ってことで、デフォルトだと(土)と計算されてしまうみたいですね。


【ExcelVBA】ExcelにてADOを使用してSQLを使用する

f:id:asa-works:20161107230811p:plain

ExcelでADO接続する

    
接続定義のメモ

  With cn
    .Provider = "Microsoft.ACE.OLEDB.12.0"'------------①
    .Properties("Extended Properties") = "Excel 12.0;HDR=No;IMEX=1"'--②
    .Open ThisWorkbook.FullName
  End With

①Provider ProviderはProvider for Microsoft Jet を指定する。 Excel 2003までの形式は8.0、Excel2007形式には12.0 ②接続プロパティ ▼Excel 12.0 ISAMバージョンを記載。 Jetと同様で2003以前の形式の場合は8.0,、2007形式の場合は12.0 ▼HDR=No HDR=Yes No 1行目をヘッダーとするならYESに。ヘッダーが無い場合はNOに。 以降のSQL文でヘッダー無しの場合は「A列、B列、C列」を順に「F1,F2,F3・・・」とする。 ▼IMEX=1 IMEXを指定しない場合、文字と数字のどちらが多いかを判断し、 自動でデータ型が解釈されるらしい。 数字が多かった場合、数字解釈されない文字列はNULLとなってしまう模様。 1としておくとデータ型が全て文字列として処理されるらしい。 以降、SQLをセットしてレコードセットへ読み込み、別シートへ書き出す処理を行う。 このへんはAccessでADOを使用する際と同様の処理になる。 SQL文のメモ シート、セルの指定→FROM [Sheet1$A1:C100]・・・このようにSheet名の後ろに「$」をつける。 セル指定の無い場合は[Sheet1$」でよい。 レコードセットの値を別シートへ貼り付けの例
ここでのシート、セルの指定は普通に。↓ Sheet2.Cells(1, 1).CopyFromRecordset rs


Accessで処理すればいいじゃん。って・・・
内情により、Excelになってしまうことが多々あります。。。