前回はExcelとは異なるデータベースの独自要素を見てみた。
今回からはその点を踏まえつつ実際にSQLiteデータベースの作成をしてみたいと思います。
ExcelでSQLiteデータベースを操作
上記を実現するためにExcel for SQLite使用する。
コレについては弊blogで過去に紹介しているのでこちらを参照して頂きたい。
今回は2つのDLLとマクロコードを記載する1つのExcelファイルを同一のフォルダに保存している。
データベースの作成
コレについても過去に紹介しているのでご参考ください。
上記内容をふまえてコードを記載してみると
Sub SQLiteDatabase_Make() Dim SQLiteDB_Handle As Long Dim ReturnValue As Long Dim SQLiteFullPath As String Dim myStmtHandle As Long Dim ErrCheck As Long 'DLLへ接続 SQLite3Initialize 'データベース作成 SQLiteFullPath = ThisWorkbook.Path & "\家計簿.db3" SQLite3Open SQLiteFullPath, SQLiteDB_Handle '①テーブル作成 ErrCheck = SQLite3PrepareV2(SQLiteDB_Handle, _ "CREATE TABLE 仕訳(" & _ "仕訳ID INTEGER PRIMARY KEY," & _ "日付 TEXT," & _ "項目 TEXT," & _ "内訳 TEXT," & _ "品名 TEXT," & _ "お店 TEXT," & _ "収入 INTEGER," & _ "支出 INTEGER," & _ "口座 TEXT," & _ "メモ TEXT)", _ myStmtHandle) SQLite3Step myStmtHandle SQLite3Finalize myStmtHandle '②インデックス作成 SQLite3PrepareV2 SQLiteDB_Handle, _ "Create Index 日付index On 仕訳(日付)", _ myStmtHandle SQLite3Step myStmtHandle SQLite3Finalize myStmtHandle SQLite3PrepareV2 SQLiteDB_Handle, _ "Create Index 項目index On 仕訳(項目)", _ myStmtHandle SQLite3Step myStmtHandle SQLite3Finalize myStmtHandle 'データベース接続終了 SQLite3Close SQLiteDB_Handle Select Case ErrCheck Case 0 MsgBox "データベース作成完了" Case Else MsgBox "テーブル作成時にエラーが発生しました" End Select End Sub
上記で、コードを記載したExcelと同じフォルダにSQLite3データベースが作成されます。
SQLite3Openでデータベース作成
SQLite3Open関数を使用するだけで作成できます。
Public Function SQLite3Open(ByVal fileName As String, ByRef dbHandle As Long) As Long
・引数 dbHandle は事前に宣言しておいたLong型変数を指定すれば大丈夫です。
Create Table構文でテーブル作成
テーブルはSQLite3PrepareV2の引数 sql でSQLのCreate文を指定すれば作成されます。
Public Function SQLite3PrepareV2(ByVal dbHandle As Long, ByVal sql As String, ByRef stmtHandle As Long) As Long
・引数 dbHandleはSQLite3Open関数の引数に使用したものと同じものを指定してください。
・事前に宣言しておいたLong型変数を指定します。
Create Table テーブル名(列名 列の型)
・必要な列の数だけ列名・列の型を記載します。
ここで前回紹介したときには登場しなかった記述があります。
PRIMARY KEYです。
主キーの設定
主キーとは、リレーショナルデータベース(RDB)のテーブル内でレコードを一意に識別することができるように指定される項目(列/カラム)。
データベースは通常、Excelのように目で見て任意の行をマウス等で指定して処理することが出来ません。
そこで各行を識別するための値、主キーを設定して識別できるようにします。
主キーとする列は以下の条件満たすようにする。
・他の行と値が重複しない
・必ず何らかのデータが格納される(NULL以外)
自分で注意して上記条件を満たすようにしても良いのですが、当然ながら間違いが起こる可能性もあります。
そこでテーブルを作成する際にPRIMARY KEY制約を設定すると上記条件を強制することが出来ます。
VBAの変数におけるOption Explicitみたいなものですね。
主キー用の列として家計簿データには無い「仕訳ID」列を追加しました。
もう1つ前回には登場しなかった記述があります。
インデックスです。
インデックスの設定
インデックスとは、索引、見出し、添字、指数などの意味を持つ英単語。ITの分野では、書籍の巻末の索引のように、多数のものの中から特定の対象をすばやく見つけ出すため識別情報や、整列された所在情報の一覧などを指すことが多い。
データベースに対して「すばやく見つけ出すための識別情報」としてつけるのがインデックスでしょうか。
・インデックスは列に対して指定する
・インデックスが設定されている列の検索が高速化する(条件によっては高速化しない)
・データベースの容量が増える
・更新系のSQL文の処理が遅くなる
取り敢えず今回は日付と項目の列に設定してみました。
このあたりは実際にインデックスの有無での検索スピードの対比はしてみたいですね。
まとめ
以前と違ってデータベース独自の要素を盛り込みながらデータベースを作成してみました。
これでデータベースの世界へ少し足を踏み込めたでしょうか。
次回はもう少しテーブル周りのことを勉強したいと思います。
こちらの本でSQL文、データベースのお作法のことを勉強しています。

スッキリわかるSQL入門 第2版 ドリル222問付き! (スッキリシリーズ)
- 作者: 中山清喬,飯田理恵子,株式会社フレアリンク
- 出版社/メーカー: インプレス
- 発売日: 2018/11/30
- メディア: 単行本(ソフトカバー)
- この商品を含むブログを見る