ExcelからODBCなしでSQLiteを操作する(設定編）

ExcelVBAを使って、SQLiteを操作する方法です。
今回はODBCなしで操作する方法を書いていきます。

どんな時に使う？

• Excelにインターフェースもデータも全部まとめててExcelブックそのものが重い時
• Excelにデータをまとめているけど、複雑な検索をしたい時
• 上記２つの時、データとアプリを分けたいけどインターフェースはExcelのままにしたい時

等など...

準備するもの

1. SQLite for Excel：SQLiteForExcel-0.9.zip
   こちらのサイトからダウンロードできます。
2. SQLiteの本体(DLL版)：sqlite-dll-win32-x86-3100200.zip
   こちらのサイトからダウンロードできます。
   今回は32bit版で紹介します。

環境

	使用環境	Bit
OS	Windows 10	64bit
Office	Excel 2013	32bit
DB	SQLite	32bit
ツール	SQLiteForExcel	32bit

手順

SQLiteForExcel-0.9.zipを解凍

中身は以下のような感じ。
パス：SQLiteForExcel\Distribution

パス：SQLiteForExcel\Source\SQLite3VBAModules

その他にもSQLite3_StdCall.dllのソースとか入ってますけど、割愛します。
sqlite3.dllも入ってますが、今回はDLしてきた最新版を使います。
なので用するのは以下の4つ。

• SQLite3_StdCall.dll
• Sqlite3_64.bas
• Sqlite3Demo_64.bas
• SQLiteForExcel_64.xlsm（サンプルExel）

ファイルを集約

上記のファイルを1箇所にまとめます。そこにDLしてきた32bitのSQLiteを追加します。

なんで、Excelとかは64bitって書いてあるの使うのかってところなんですが、
32bit版のサンプルがxlsだったので、xlsmで実験してみました。

ちなみに、.xlsmと.basの64の表記は32bitと64bit両方に対応するために、分岐プログラムが書かれてます。

VBA

    #If Win64 Then
    #Else
    If hSQLiteStdCallLibrary = 0 Then
        hSQLiteStdCallLibrary = LoadLibrary(libDir + "SQLite3_StdCall.dll")
        If hSQLiteStdCallLibrary = 0 Then
            Debug.Print "SQLite3Initialize Error Loading " + libDir + "SQLite3_StdCall.dll:", Err.LastDllError
            SQLite3Initialize = SQLITE_INIT_ERROR
            Exit Function
        End If
    End If
    #End If

分岐は書いてあるけど、Excel本体が64bitだった場合SQLite3_StdCall.dllを読み込んでない用に見えますが・・・

モジュール読み込み

本来ならExcelを起動して、VBAのエディタ画面で Sqlite3_64.basとSqlite3Demo_64.basを読み込みます。
今回、サンプルExelのSQLiteForExcel_64.xlsmを使うので最初から読み込まれてます。

実際に使用する際は、必要なのはSqlite3なのでDemoはどちらでもOKです。

テスト実行

Sqlite3DemoのAllTestsはテストモジュールなので当然このまま動くのですが
デフォルトでは、DBファイルの作成先がC:\TestSqlite3ForExcel.db3になってます。
予めC:のパスを変更します。

パス変更をしたら、AllTestsを実行してみます。

testlog

SQLite3Close returned 0
----- TestError Start -----
SQLite3Open returned 14
SQLite3Open error messagE:\sqlite\db unable to open database file
SQLite3Close returned 0
----- TestError End -----
----- TestInsert Start -----
SQLite3Open returned 0
SQLite3PrepareV2 returned 0
SQLite3Step Done
SQLite3Finalize returned 0
SQLite3PrepareV2 returned 0
SQLite3Step Done
SQLite3Finalize returned 0
SQLite3Execute - Insert affected 1 record(s).
----- TestInsert End -----
----- TestSelect Start -----
SQLite3Open returned 0
SQLite3PrepareV2 returned 0
SQLite3Step Done
SQLite3Finalize returned 0
SQLite3PrepareV2 returned 0
SQLite3Step Done
SQLite3Finalize returned 0
SQLite3PrepareV2 returned 0
SQLite3Step Done
SQLite3Finalize returned 0
SQLite3PrepareV2 returned 0
SQLite3Step Row Ready
Column count: 3
Column 0:     TheId         INTEGER        123 
Column 1:     TheText       TEXT          ABC
Column 2:     TheValue      FLOAT          42.1 
SQLite3Step Row Ready
Column count: 3
Column 0:     TheId         INTEGER        987654 
Column 1:     TheText       TEXT          ZXCVBNM
Column 2:     TheValue      NULL          Null
SQLite3Step Done
SQLite3Finalize returned 0
----- TestSelect End -----
----- TestBinding Start -----
SQLite3Open returned 0
SQLite3Finalize returned 0
Insert Elapsed: 00:00:02
SQLite3PrepareV2 returned 0
At row 1
------------
Column count: 4
Column 0:     TheId         INTEGER        42501 
Column 1:     datetime(TheDate)           TEXT          2001-05-16 00:00:00
Column 2:     TheText       TEXT          The quick brown fox jumped over the lazy dog.
Column 3:     TheValue      FLOAT          333.240926265717 
============
At row 10001
------------
Column count: 4
Column 0:     TheId         INTEGER        42501 
Column 1:     datetime(TheDate)           TEXT          2001-05-16 00:00:00
Column 2:     TheText       TEXT          The quick brown fox jumped over the lazy dog.
Column 3:     TheValue      FLOAT          333.240926265717 
============
At row 20001
------------
Column count: 4
Column 0:     TheId         INTEGER        42501 
Column 1:     datetime(TheDate)           TEXT          2001-05-16 00:00:00
Column 2:     TheText       TEXT          The quick brown fox jumped over the lazy dog.
Column 3:     TheValue      FLOAT          333.240926265717 
============
At row 30001
------------
Column count: 4
Column 0:     TheId         INTEGER        42501 
Column 1:     datetime(TheDate)           TEXT          2001-05-16 00:00:00
Column 2:     TheText       TEXT          The quick brown fox jumped over the lazy dog.
Column 3:     TheValue      FLOAT          333.240926265717 
============
At row 40001
------------
Column count: 4
Column 0:     TheId         INTEGER        42501 
Column 1:     datetime(TheDate)           TEXT          2001-05-16 00:00:00
Column 2:     TheText       TEXT          The quick brown fox jumped over the lazy dog.
Column 3:     TheValue      FLOAT          333.240926265717 
============
At row 50001
------------
Column count: 4
Column 0:     TheId         INTEGER        42501 
Column 1:     datetime(TheDate)           TEXT          2001-05-16 00:00:00
Column 2:     TheText       TEXT          The quick brown fox jumped over the lazy dog.
Column 3:     TheValue      FLOAT          333.240926265717 
============
At row 60001
------------
Column count: 4
Column 0:     TheId         INTEGER        42501 
Column 1:     datetime(TheDate)           TEXT          2001-05-16 00:00:00
Column 2:     TheText       TEXT          The quick brown fox jumped over the lazy dog.
Column 3:     TheValue      FLOAT          333.240926265717 
============
At row 70001
------------
Column count: 4
Column 0:     TheId         INTEGER        42501 
Column 1:     datetime(TheDate)           TEXT          2001-05-16 00:00:00
Column 2:     TheText       TEXT          The quick brown fox jumped over the lazy dog.
Column 3:     TheValue      FLOAT          333.240926265717 
============
At row 80001
------------
Column count: 4
Column 0:     TheId         INTEGER        42501 
Column 1:     datetime(TheDate)           TEXT          2001-05-16 00:00:00
Column 2:     TheText       TEXT          The quick brown fox jumped over the lazy dog.
Column 3:     TheValue      FLOAT          333.240926265717 
============
At row 90001
------------
Column count: 4
Column 0:     TheId         INTEGER        42501 
Column 1:     datetime(TheDate)           TEXT          2001-05-16 00:00:00
Column 2:     TheText       TEXT          The quick brown fox jumped over the lazy dog.
Column 3:     TheValue      FLOAT          333.240926265717 
============
SQLite3Finalize returned 0
Select Elapsed: 00:00:04
----- TestBinding End -----
----- TestDates Start -----
SQLite3Open returned 0
SQLite3Finalize returned 0
SQLite3PrepareV2 returned 0
Event: Nice trip somewhere  DatE:\sqlite\db 2010/06/19
SQLite3Finalize returned 0
----- TestDates End -----
----- TestStrings Start -----
SQLite3Open returned 0
SQLite3Finalize returned 0
SQLite3PrepareV2 returned 0
Result1: 
Result2: 
Long String is the samE:\sqlite\db True
SQLite3Finalize returned 0
----- TestStrings End -----
----- TestBackup Start -----
SQLite3Open returned 0
SQLite3PrepareV2 returned 0
SQLite3Step Row Ready
Column count: 2
Column 0:     Key           INTEGER        1 
Column 1:     Value         TEXT          First
SQLite3Step Row Ready
Column count: 2
Column 0:     Key           INTEGER        2 
Column 1:     Value         TEXT          Second
SQLite3Step Done
SQLite3Finalize returned 0
SQLite3Open returned 0
SQLite3BackupStep returned 101
SQLite3BackupFinish returned 0
Backup result 0
Selecting from backup:
SQLite3PrepareV2 returned 0
SQLite3Step Row Ready
Column count: 2
Column 0:     Key           INTEGER        1 
Column 1:     Value         TEXT          First
SQLite3Step Row Ready
Column count: 2
Column 0:     Key           INTEGER        2 
Column 1:     Value         TEXT          Second
SQLite3Step Done
SQLite3Finalize returned 0
----- TestBackup End -----
----- TestBlob Start -----
SQLite3Open returned 0
SQLite3Finalize returned 0
SQLite3PrepareV2 returned 0
Blob byte 0: 90
Blob byte 1: 91
Blob byte 2: 92
SQLite3Finalize returned 0
----- TestBlob End -----
SQLite3Open returned 0
SQLite3OpenV2 returned 0
SQLite3PrepareV2 returned 0
SQLite3Step returned 101
SQLite3Finalize returned 0
SQLite3PrepareV2 returned 0
SQLite3Step returned 8
Cannot Write in Read Only database
SQLite3Finalize returned 8
SQLite3PrepareV2 returned 0
SQLite3Step returned 101
But Reading is granted on Read Only database
SQLite3Finalize returned 0
SQLite3Close V2 returned 0
SQLite3Close returned 0

正常にテストができれば、これで疎通は問題ありません。
あとはテストのモジュールからどの関数が何をやっているのか
紐解いていけば好きに使えると思います！

まとめ

今回は環境を整えるまでの内容です。
実際にテーブル作ったり、SELECTやINSERT等の説明は
また次回にしようとおもいます！
テストモジュール内でCREATEやINSERT、SELECTはやっているので
今はそれを紐解きつつ、手元のデータをDB化してます。