Accessデータベースでデータ管理

Accessとは？

求職情報を探していると、Accessの技術者を求めているものが散見されるのですが、普通にAccessの事を聞くと、知らないという人が多いと感じます。

最近のパソコンを購入すると、インストールはされているはずなのですが、気づかれないのか、Microsoft Officeの中では知名度の最も低いのではないでしょうか？

社内ＳＥをしている時も、インターネット上での事業展開で、色々データが必要になるのに、Accessの事を知っている人は二人しかいませんでした。

Accessは、データベースアプリケーションになります。

このデータベースというのが、意外と何をするものなのか掴みにくいため、Accessも今ひとつExcelのように万人が使うツールになれないのかなぁ…などと思ったりします。

データベースは、データを登録して蓄積し、要求に沿ってそれを取り出し、計算などの処理を加えて出力するソフトになります。

Excel でデータを管理する限界

なぜ、そんなものが要るのか？　Excelで良いんじゃないか？　と疑問も出ると思いますので、別記事のExcel VBAの果物屋さんのデータの問題点からあげてみましょう。

この記事で紹介した例では、Excel シート3個にデータが分散していて、ＶＢＡで集めてくる例を紹介しました。

しかしこれには例として作成したために、あえて無視した問題があります。

りんご
ぶどう
いちご
メロン
サクランボ

の順で並んでいる事を前提としている事です。

しかし、実際には複数の場所から取り寄せたデータが、必ず同じ順で並んでいるとは限りません。

ちなみに、VLOOKUP関数の場合は、名前の合致で引っ張ってきますが、データが巨大化します。

また、例えば「今月の途中でりんごの仕入れ業者を変えたので、価格が変わった」という場合も、このままでは対応できません。

この場合、対策としてははBA側のプログラムを書き換えて対応する事も可能です。

しかし、社内ＳＥとしては、データの条件が増えてきたら、早めにデータベースへの移行を検討するようにしてきました。

実のところ、VBAを改修に改修を重ねて乗り越えるよりは、データ管理とデータ処理に分業した方が判りやすく、メンテナンスが楽で、拡張性も高いからです。

Accessにデータを移し、クエリで問い合わせる

画像は、ＥｘｃｅｌＶＢＡで例にした果物屋さんのデータをデータベースに移したものです。

「仕入価格」、「卸先」、「入荷」が格納されているのが判ると思います。

このそれぞれは、「テーブル」と呼ばれます。

商品名というテーブルは、果物屋さんのExcel シートにありませんが、データベースはこの例のように、その商品を特定できるデータがあると作業がやりやすくなるので、新設しました。

これをユニークキーといいます。

ユニークキーは、商品名の商品IDのように別項目として付けたり、卸先のように、シリアルナンバーを代用したりする事もできます。

卸先の左の番号はオートインクリメントと言って、卸先を新規登録すると勝手に新しい値がセットされます。このように、そのデータと結びついて、他には重複していないデータがあると、データベースは非常に速くデータを見つける事ができるので、必ず指定するようにします。

こうして、幾つかのテーブルに分けたデータを、クエリーを使って問い合わせします。

クエリ上で繋げられたフィールドは、両方に同じ値がある場合のみ、データを表示します。

上記の例では、「仕入れ価格」テーブルにも「入荷」テーブルにもap-001がありますので、こが結合されます。

「入荷」テーブルと「仕入先」テーブルには、1と2が両方ありますので、合致するデータが引き出されてきます。
結果、クエリ1ではEXCELの時のシートに近いものが表示されています。

何が便利になるのか？

で？　それがどうしたの？　

はい。これだけでは確かにそれほど便利になったとは思えないですね。

データを蓄積する事が出来る事、それを表示する事が出来ることが判ったけれど、これの活用が今ひとつだと思います。

では、この問い合わせが、EXCELなど他のアプリケーションから送信できて、結果を受け取れるとしたらどうでしょう？
実は、このクエリというのはSQL言語で出来ていて、色々と共通化されています。

SELECT
 [商品名].[商品ID]
, [商品名].[商品名]
, [入荷].[仕入数]
, [仕入価格].[仕入価格]
, [卸先].[仕入先]
 FROM
 (
   (
     [商品名] INNER JOIN [入荷] ON [商品名].[商品ID] = [入荷].[商品ID]
   ) INNER JOIN [仕入価格] ON [商品名].[商品ID] = [仕入価格].[商品ID]
 ) INNER JOIN [卸先] ON [入荷].[仕入先] = [卸先].[Serial]
ORDER BY 商品名.Serial
;

これが上の画面のクエリをSQLにしたものです。
これを何か別のアプリケーションからAccessに届ければ、Accessはデータを纏めた結果を送り返してくれます。

例えば、ExcelをAccessに接続し、このSQLを送信。受け取った結果を表示するVBAを組むと、このようになります。

  Dim myConn As New ADODB.Connection
  Dim myRS As New ADODB.Recordset

  Dim wkb00 As Workbook
  Set wkb00 = ThisWorkbook
  
  Dim wsh00 As Worksheet
  Set wsh00 = wkb00.Worksheets(1)
  
  Dim Rag00 As Range
  
  Dim sql1 As String
  Dim n As Integer
  n = 1

  Dim dbfm As String
  dbfm = ThisWorkbook.Path
  dbfm = dbfm & "\"
  
  ''Accessのmdbと接続
    myConn.Open ConnectionString:= _
      "Provider=Microsoft.ACE.OLEDB.12.0;" & _
      "Data Source=" & dbfm & "Database01.accdb"

      sql1 = "SELECT"
      sql1 = sql1 & "[商品名].[商品ID]"
      sql1 = sql1 & ", [商品名].[商品名]"
      sql1 = sql1 & ", [入荷].[仕入数]"
      sql1 = sql1 & ", [仕入価格].[仕入価格]"
      sql1 = sql1 & ", [卸先].[仕入先]"
      sql1 = sql1 & "FROM"
      sql1 = sql1 & "("
      sql1 = sql1 & "   ("
      sql1 = sql1 & "     [商品名] INNER JOIN [入荷] ON [商品名].[商品ID] = [入荷].[商品ID]"
      sql1 = sql1 & "   ) INNER JOIN [仕入価格] ON [商品名].[商品ID] = [仕入価格].[商品ID]"
      sql1 = sql1 & ") INNER JOIN [卸先] ON [入荷].[仕入先] = [卸先].[Serial]"
      sql1 = sql1 & "ORDER BY 商品名.Serial"
      sql1 = sql1 & ";"

      myRS.Open Source:=sql1, ActiveConnection:=myConn
      Do Until myRS.EOF = True
        
        Set Rag00 = wsh00.Cells(n + 2, 2) '品名
        Rag00.Value = myRS![商品名]
      
        Set Rag00 = wsh00.Cells(n + 2, 3)   '単価
        Rag00.Value = myRS![仕入価格]
      
        Set Rag00 = wsh00.Cells(n + 2, 4)   '仕入
        Rag00.Value = myRS![仕入数]
        
        Set Rag00 = wsh00.Cells(n + 2, 5)   '金額
        Rag00.Value = myRS![仕入価格] * myRS![仕入数]
        
        Set Rag00 = wsh00.Cells(n + 2, 6)   '取引先
        Rag00.Value = myRS![仕入先]
        
        
        n = n + 1
        myRS.MoveNext
      Loop
    myRS.Close

先ほどのSQL文を指定したAccessに送ると、回答としてデータが戻ってきますので、そのデータをループ処理でシートに展開します。