SQLiteデータベースに接続するSQLiteDatabaseConnectionクラス

Excel VBAからSQLiteデータベースを操作するのにSQLite ODBC Driverを使っています。

リンクからsqliteodbc.exe(32bit用)をダウンロードしてWindows
10にインストールしました。64bit用のsqliteodbc_w64.exeもあるのですが、試していません。多分、64bit OSならどちらでもよいと思います。 64bitは動きませんでした(2017-12-02 追記)。

SQLite ODBC Driverをインストールすると、インストールフォルダにsqlite3.exeがあるので、データベースファイルをコマンドプロンプトで作成します。

C:\Program Files (x86)\SQLite ODBC Driver>sqlite3.exe test.db
SQLite version 3.15.2 2016-11-28 19:13:37
Enter ".help" for usage hints.
sqlite> .quit

作成したデータベースtest.dbをExcelワークブックと同じフォルダに移動します。

SQLiteデータベースとの接続を行うのでクラスモジュールの名前はSQLiteDatabaseConnectionにしました。クラスモジュール内のコードは次の通りです。

Option Explicit

' プライベート定数定義
Private Const DB_FILE_NAME As String = "\test.db"

' プライベート変数定義
Private m_DatabaseFilePath As String
Private m_RecordSet As ADODB.RecordSet
Private m_Connection As ADODB.Connection

' オブジェクト生成または初回メソッドコールで発生するイベント
Private Sub Class_Initialize()
  m_DatabaseFilePath = ThisWorkbook.path & DB_FILE_NAME
  Connect
End Sub

' オブジェクト消滅で発生するイベント
Private Sub Class_Terminate()
  Disconnect
End Sub

' データベースファイルのパスを返す
 Public Property Get FilePath() As String
  FilePath = m_DatabaseFilePath
End Property

' 問い合わせた結果のレコードセットを返す
Public Property Get RecordSet() As ADODB.RecordSet
  Set RecordSet = m_RecordSet
End Property

' データベースの接続を開く
Private Sub Connect()
  Set m_Connection = New ADODB.Connection
  ' ファイルm_DatabaseFilePathに対してDB接続条件を設定する
  m_Connection.ConnectionString = "DRIVER=SQLite3 ODBC Driver;Database=" & m_DatabaseFilePath
  m_Connection.Open
End Sub

' データベースの接続を閉じる
Private Sub Disconnect()
  m_Connection.Close
  Set m_RecordSet = Nothing
  Set m_Connection = Nothing
End Sub

' SQL問い合わせ
Public Sub Query(ByRef Statement As String)
  If Statement Like "select*" Then
    ' SELECT文の場合はクエリを実行してレコードセットをプライベート変数m_RecordSetに代入する
    Set m_RecordSet = m_Connection.Execute(Statement)
  Else
    ' UPDATE/INSERT/DELETE文の場合はクエリを実行するだけ
    m_Connection.Execute Statement
  End If
End Sub

クラス内のプライベート定数DB_FILE_NAMEにデータベースファイル名を決め打ちしています。コマンドプロンプトでsqlite3.exeで作成したデータベースファイルのファイル名をマニュアルで入力します。

ADODB.Connectionオブジェクトのプロバイダに"DRIVER=SQLite ODBC"を設定して、SQLiteデータベース.\DB_FILE_NAMEに接続しています。

Excel VBAのIDEでインテリセンスを使うために、ツール > 設定 > 参照設定でMicrosoft ActiveX Data Objects 6.1 Libraryにチェックを入れました。

参照設定

このSQliteDatabaeConnectionクラスを使うには、他のモジュールで次のようなプロシージャを作ります。

Sub Test_MakeTable()

  Dim SQLiteDatabaseConnection As New SQLiteDatabaseConnection
  
  SQLiteDatabaseConnection.Query "create table test_table (col1 int, col2 text)"
  SQLiteDatabaseConnection.Query "insert into test_table (col1, col2) values (99, 'new')"

End Sub

クラスのオブジェクトが生成されると自動でClass_Initialize()メソッドが実行され、そこでデータベース接続をオープンするConnect()メソッドがコールされるので、クラスのクライアントはオブジェクトを生成したらすぐにRDBMSにSQL文でクエリできます。

ここでは試しにテーブルtest_table (col1 int, col2 text)を新規作成して、行(99, 'new')をテーブルに追加しています。

クエリが失敗するとエラーメッセージウィンドウが現れ、成功するとメッセージはありません。

テーブルにデータが追加できたかDB Browser for SQLiteで確認します。

データベース接続テスト結果

ちゃんとデータベースに反映されています。

Excel VBAでテーブルのデータを取得するには次のようにします。

Sub Test_GetRecord()
  
  Dim SQLiteDatabaseConnection As New SQLiteDatabaseConnection

  SQLiteDatabaseConnection.Query "select * from test_table"
  Debug.Print SQLiteDatabaseConnection.RecordSet(0)
  Debug.Print SQLiteDatabaseConnection.RecordSet(1)

End Sub
99
new
Reference

ADOの使い方はADO API リファレンス - Microsoft MSDNを読んでいます。

2017-08-06 データベースファイルの自動作成について補足

ADODB.ConnectionOpen()メソッドを実行するとデータベースファイルが存在しない場合は自動でファイルが作成されることがわかりました。sqlite3.exeを使ってデータベースをマニュアルで作らなくてもよいです。存在しないデータベースファイルのパスをConnectionStringプロパティに設定すればOKです。ただし、パスの親フォルダが存在しない場合は「Connection failedエラー」が生じます。

Kosuke Maeda / まえだこうすけ

「機械学習で競馬予想して勝てるのか?」をテーマに活動中! QiitaにはR、VBAなどのTipsを投稿しています。