事例1-4:…Accessって？ | 事例紹介

はい。ExcelやWordの話のときは、うんうんとお話を聞いていただいていた方が、急にきょとんとされる事があります。

「Microsoft Access」

最近のパソコンを購入すると、インストールはされているはずなのですが、気づかれないのか、Microsoft Officeの中では知名度の最も低いアプリの一つなのではないかと思います（意見には個人差があります）

Accessは、データベースソフトになります。

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

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

これでけでは判りづらいので、別発言の果物屋さんのデータの問題点からあげてみましょう。

このＶＢＡは、全てのデータが

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

の順で並んでいる事を前提としています。
しかし、複数の場所から取り寄せたデータが、必ず同じ順で並んでいるとは限りません。

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

この場合、もちろんVBA側のプログラムで対応する事も可能ですが、弊社ではデータの条件が増えてきたら、早めにデータベースへの移行をオススメしています。
理由は、小手先の工夫でとりあえず乗り越えるよりは、早めにデータ管理とデータ処理に分業した方が管理が判りやすく、拡張性も高いからです。
そして、一番最初のデータベースとしては、Accessをオススメしています。
こちらの理由は、Officeに同梱されているので、初期投資が少ない事と、データベースの中では判りやすいインターフェイスをしていると思うからです（意見には個人差があります）

画像は、Ｅｘｃｅｌが重い！で例にした果物屋さんのデータをデータベースに移したものです。
商品名は、果物屋さんの中にありませんが、データベースはこの例のように、その商品を特定できるデータがあると作業がやりやすくなるので、新設しました。
これをユニークキーといいます。

ユニークキーは、商品名の商品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
;

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

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

これで、

のようにExcelにAccessのデータを表示できます。

発展としては、例えば日々の売上データを日付を付けて格納しておき、期間で集計するなど、データをAccessに保存、それをEXCELで参照というのは、かなり幅広い使い道があります。

売上のデータや、様々な台帳類など、データベース化して利用が可能になります。

ご興味がおありでしたら、お問い合わせからお気軽にご相談ください。