2019/11/19

Excelシート上にDBを作り、SQLを使ってデータを入出力する

0.はじめに

Excelはもともとデータベース（DB）に適しており、フィルターや関数、メソッドやプロパティも充実しています。しかし、１つのExcelブックの上でDBを使っている分には良いのですが、いざ複数の人が同時に使おうとするとちょっと大変です。
Excelにも「共同編集（共有ブック）」という機能があって、サーバにファイルを置いて複数人で使用することができますが、「みんなで一つの資料を作成」みたいな場面が多いのではと思います。
そこで、外部DBを使うことを考えたいと思います。Excelには外部DBと接続させる機能があります。外部DBというとSQL ServerやOracle等いろいろありますが、外部DBをインストールしたりメンテナンスするにはある程度の知識が必要です。
そこで、今回は、Excelそのものを外部DBとして使用していきます。多少制約はありますが、簡易なシステムであれば支障ありません。（Excel以外の外部DBを使用したシステムについては、別項目として説明していきます。）

当サイトで、SQLを使用した「具体的なアプリ」としては以下のものがあります。参考にして下さい。
「ExcelシートDBを使った会議室予約システム」
「ExcelシートDBとSQLを使った倉庫管理システム」
「共有資料の登録と閲覧ができるサーバーシステム」
「複数の備品を同時予約可能な貸出台帳」

なお、今回はExcelのシートをデータベーステーブルにしていますが、Accessのデータベースファイルを使ったシステム等については、下記を参照して下さい。
「ExcelからAccessデータベースを作成・操作」
「Accessデータベースを使用した売上台帳」
「設備の稼働状態を入力し、グラフで確認」

また、データベースへの接続にはDAOやADOなどを経由しますが、それらについての詳しい紹介については以下を参照下さい。
　・DAOでAccess･Excel･CSVへ接続・操作
　・ADOでAccess･Excel･CSVへ接続・操作(JET･ACEプロバイダ編)
　・ADOでAccess･Excel･CSVへ接続・操作(MSDASQLプロバイダ編)
　・ODBCでAccess･Excel･CSVへ接続・操作

1.データベースの基本的な知識

ここで言うデータベースは、リレーショナルデータベースを指します。
リレーショナルデータベースとは、テーブルと呼ばれる表から条件に合ったデータを引っ張ってくるものです。表（テーブル）は概念的にはExcelの表（シート）と同じで、横を列（項目）、縦を行（データ）と呼んでデータが並んでいる、と言うものです。

データベースに対するアクセスはSQLと言う言語を使用します。その中でデータを操作するコマンドとして「SELECT」「INSERT」「UPDATE」「DELETE」の４つがあります。基本的な記述方法は以下の通りです。

SELECT：テーブルからデータを取り出す（見に行く、という表現の方が正しいかもしれません）
例：SELECT column1,column2 FROM table WHERE column3=constant
（表tableから、列column1、列column2を取り出す。条件は列column3がconstantの時）

INSERT：データを新たにテーブルに追加する。
例：INSERT　INTO table VALUES (column1= constant1,column2= constant2)

UPDATE：既にあるデータを書き換える。
例：UPDATE table　SET column1= constant1 WHERE column3=constant

DELETE：既にあるデータを削除する。
例：DELETE　FROM　 table　 WHERE column3=constant

テーブルは通常複数存在し、テーブルを連結させたりして操作します。また、複数の人が同時にアクセスしてもデータがおかしくならないようにデータベースシステム（RDBMS）は作られています。

2.データベース用シートの作成

「データベース用シート」と言っても、普通のExcelシートを使います。複数のテーブルを１つのExcelシート上に作ることは可能ですが、後で説明する行の削除やシステム改造で列を増す場合もあるため、「１つのテーブルは１つのシートで」作った方が良いでしょう。また、シート名は「Sheet1」等そのまま使っても良いですが、テーブル名らしい名前に変更しても問題ありません。

次に１行目に列名を連続して記載します。「漢字かな」もOK、大文字小文字はSQL文の中でも無視されるので混ぜてもOKです（大文字小文字を無視するのはWindowsの仕様で、Unix・Linux系は別物として扱われます。）が、将来システムを別のO/S、DBに移行する事もあるかもしれませんので、他のデータベースと合わせて、①大文字を使わない、②先頭は数字以外の文字、③記号はアンダスコア「_」のみ、④SQLの予約語は避ける（select や from 等）、⑤2バイト文字（ひらがな、漢字など）は使わない　が無難と思います。尚、同じ列名を一つのテーブルに設定するのはＮＧです。（別のテーブルならOK）

A列、B列・・と列名が並んでいますが、この順番には意味があります。分かり易い順番で、抜けが無いように最初の設計時に良く考えて並べて下さい。
というのも、INSERT文でデータを追加する際には、
INSERT　INTO table VALUES (column1= constant1,column2= constant2)　と同じ意味の文を
INSERT　INTO table VALUES (constant1, constant2)　と縮めて書くことができます。（ここでは、短縮版INSERT文　とします）
これは列名がcolumn1,column2　という順番で並んでいる事、およびtable表の全ての列がカッコ内に並んでいる事が前提ですが、Excelのシートを入力シート等にしている場合は上から順に並べればよい、等の利点があります。外部のデータベース等ではテーブルの列名を付けた順（コマンドをタイプした順）で並ぶのが通常ですので、この点ではExcelの方が順序が目に見えるので親切かと思います。

また、列の途中に空きがあってもSELECT文やUPDATE文は動きますが、上記の短縮版INSERT文は使用できなくなります。

さて列名が記入できたら、まずは適当に試用データを入れてみましょう。下の例は出勤簿で時給も一緒になったような表です。列名だけのカラ表の状態からプログラムを組み立てるのもありですが、データを入れるよりは取り出すプログラムを作る方がずっと楽ですし、なにより試用でもデータが入ると完成に近づいてきたという喜びが出てきます。

このブックは最終的には皆がアクセスできるサーバなどに置くことになると思いますが、まずはPCのどこかに保存し、その場所を覚えておきます。

3.取り出しプログラムの作成

このデータを使って、まずSELECT文でデータを取り出してみましょう。
標準モジュールに以下のプログラムコードを記入します。

サンプルコードをコピペする方もいるでしょう。自分でコードをタイプする方もいるでしょう。その時、小文字でタイプしているのに、勝手に大文字になったりしたのに気が付いたでしょうか。
VBEのコード画面上でコードを入力すると、内部関数やオブジェクト・プロパティ・メッソドは勝手に文字体を合わせてくれます。反対に言うと、間違えて入力すると文字体が変わらない、ということで、タイプミスが分かるようになっています。
  1. Sub sql_01()
  2. 　Dim cn As ADODB.Connection
  3. 　Dim rs As ADODB.Recordset
  4. 　Dim File_Name , Sql As String
  5. 　Dim CurRow As Integer
  7. 　　File_Name = "c:¥Users¥User¥db.xlsx" 　'DBのフルネーム。尚、このブック上（ThisWorkbook.FullName）でもOK
  8. 　　curRow =1
  10. 　　Set cn = New ADODB.Connection
  11. 　　Set rs = New ADODB.Recordset
  13. 　　cn.Provider = "MSDASQL"
  14. 　　cn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" & "DBQ=" &　File_Name　& "; ReadOnly=True;"
  16. 　　cn.Open
  18. 　　Sql = "SELECT name,date,hr FROM [Sheet1$] WHERE name='越後屋' "
  19. 　　rs.Open Sql , cn, adOpenStatic
  21. 　　Do Until rs.EOF
  22. 　　Sheets("Sheet1").cells(CurRow,1).Value = rs!name
  23. 　　Sheets("Sheet1"). cells(CurRow,2).Value = datevalue(rs!date)
  24. 　　Sheets("Sheet1"). cells(CurRow,3).Value = rs!hr
  25. 　　rs.MoveNext
  26. 　　CurRow = CurRow + 1
  27. 　　Loop
  29. 　　rs.Close
  30. 　　cn.Close
  31. 　Set rs = Nothing
  32. 　Set cn = Nothing
  33. End Sub

4.参照設定への登録

プログラムを実行する前に、「Microsoft ActiveX Data Objects 2.8 Library」というライブラリを参照させる必要があります。この作業は、標準のVBAの機能だけではデータベースを扱えないため、このファイルに「VBAには無い機能（オブジェクト）」を収めたファイルをつなげる、という手続きです。

手順：VBＥのタグから、ツール→参照設定→リストから「Microsoft ActiveX Data Objects 2.8 Library」を探しチェック（レ点）をつける。
　　


5.プログラムの実行

では、実行してみましょう。実行するには、以下のどの方法でもＯＫです。
・VBEのコード画面で、実行したいコード上にカーソルを置き、①実行(R)をクリック、②▶ボタンをクリック
・Excelシート画面で、開発→マクロ→マクロ名を反転させる→実行

予想した通りに表示されたでしょうか。

6.データの追加

次に、データを追加してみましょう。
  1. Sub sql_02()
  2. 　Dim cn As ADODB.Connection
  3. 　Dim rs As ADODB.Recordset
  4. 　Dim File_Name ,sql As String
  6. 　File_Name = "c:¥Users¥User¥db.xlsx" 　'DBのフルネーム。尚、このブック上（ThisWorkbook.FullName）でもOK
  7. 　Set cn = New ADODB.Connection
  8. 　Set rs = New ADODB.Recordset
  9. 　cn.Provider = "MSDASQL"
  10. 　cn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" & "DBQ=" & File_Name & "; ReadOnly=False;"
  12. 　cn.Open
  13. 　　sql2 = "insert into [Sheet1$] values('2019/10/10','D',10,500,500)"
  14. 　　rs.Open sql2, cn, adOpenStatic
  15. 　cn.Close
  17. 　Set rs = Nothing
  18. 　Set cn = Nothing
  19. End Sub

7.データの変更

データの変更にはUPDATEを使います。
  1. Sub sql_03()
  2. 　Dim cn As ADODB.Connection
  3. 　Dim rs As ADODB.Recordset
  4. 　Dim File_Name ,sql As String
  6. 　File_Name = "c:¥Users¥User¥db.xlsx" 　'DBのフルネーム。尚、このブック上（ThisWorkbook.FullName）でもOK
  7. 　Set cn = New ADODB.Connection
  8. 　Set rs = New ADODB.Recordset
  9. 　cn.Provider = "MSDASQL"
  10. 　cn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" & "DBQ=" & File_Name & "; ReadOnly=False;"
  12. 　cn.Open
  13. 　　sql3 = "update [Sheet1$] set 勤務時間=6 where 名前='C'"
  14. 　　rs.Open sql3, cn, adOpenStatic
  15. 　cn.Close
  17. 　Set rs = Nothing
  18. 　Set cn = Nothing
  19. End Sub

8.データの削除

「DELETE」コマンドは実はExcelのシートをデータベースにしている時は使えないのです。下の様なコードはエラーが出ます。
  1. 　sql4 = "DELETE from [Sheet1$] where 名前='C'"
でもデータが消せない（DELETE出来ない）のは困りますよね。私見ですがExcelシートで「DELETE」が出来ないのは、番地を持ったセルの上にデータが乗っている（データに順序がある）ので、途中の１つが抜けてしまうとデータ領域が分断してしまうためか、と思っています。

では「DELETE」が出来ないと困る場合はどうすれば良いか。
１つ目の方法は、どこかの列（先頭列か最後列が適当かと思いますが）に、「DELETE」した印をつける方法です。 「UPDATE」コマンドで以下のような命令を実行します。（dele列にdeleteした印をつける事にします）
  1. sql="delete from [Sheet1$] where name='越後屋' and date=’2019/10/3’ " ←本当はこれをやりたい
  2. ⇩
  3. sql="update [Sheet1$] set dele=’D’ where name='越後屋' and date=’2019/10/3’ "
几帳面な方なら、’D’の代わりに削除した日付を入れるのも良いかと思います。
  1. sql=”update [Sheet1$] set dele=” & today() &“ where name='越後屋' and date=" & CDbl(DateValue("2019/10/3"))
上の例で、日付を検索するのにわざわざ「CDbl(DateValue("2019/10/3"))」としているのには訳があります。Excelのセル上は日付ですが、これは見かけだけで、内部的には1900年1月1日からの経過日の値が記録されています。ですので、その値（シリアル値）で検索するために、Cdblという日付を数値に変換する関数を使用しています。


尚、DELETEしたマークが入っている列以外のデータで操作したい場合は、「dele列にDの値がないこと」を条件に付け加えます。
（例1）sql ="update [Sheet1$] set hr=5 where name='越後屋' and dele <>'D'"
（例2）Sql ="SELECT name,date,hr FROM [Sheet1$] WHERE name='越後屋' and dele <>'D'"

このDELETEマーク方式ですと、データの追加・削除が多いデータベースでは、データ行数がどんどん増えてしまい、処理時間も増える可能性があります。どうせ使わないデータ（データベースでDELETEした時はすぐに消えるのですし）ですから、整理したくなると思います。
誰もアクセスしない時間に、データベースとなっているExcelファイルを直接開き、「D」印のついた行を削除していくのですが、その時に一つのシートに複数のデータベースを作成すると、行削除で他のデータベースの必要なデータを消してしまう可能性が発生します。
バックアップをしっかりとするのは当然ですが、万一に備えて「１シートに１データベース」をお勧めします。