EXCEL VBAメモ - SQLite3データベースのアクセス（SQLite ODBC使用）

準備

• SQLite ODBC Driver からSQLite ODBC Driverをダウンロードしてインストールする。64bit版はsqliteodbc_w64.exe(現時点の最新はVer.0.9996)
• インストールを行うとsqlite3.dllがC:\Windows\System32に見つからないというメッセージが出るのでSQLite Home Pageからダウンロードして別途コピーする。（現時点の最新は、sqlite-dll-win64-x64-3260000.zip）

アンインストール時の注意点

インストールすると、C:\Windows\System32にいくつかのsqlite3*.*というファイルがコピーされるが通常のアンインストールで削除されないファイルがあるので完全にアンインストールを行う場合には手動で削除する必要がある。
　

参考

ODBCの操作については以下を参考にする

• Connection オブジェクト
• Command オブジェクト
• Recordset オブジェクト

SQLite3ODBCクラスの作成

データベースの操作を簡単にするためにクラス化してみる

Option Explicit
' SQLite3ODBC.cls

Dim mCon As Object
Dim mCmd As Object
Dim mRS  As Object
Dim mSql As String

Private Sub Class_Initialize()
    Set mCon = CreateObject("ADODB.Connection")
    Set mCmd = CreateObject("ADODB.Command")
    Set mRS = CreateObject("ADODB.Recordset")
End Sub

Private Sub Class_Terminate()
    If Not mRS Is Nothing Then
        'mRS.Close
        Set mRS = Nothing
    End If
    mCon.Close
    Set mCon = Nothing
    Set mCmd = Nothing
End Sub

Public Sub Prepare(ByVal dbFile As String)
    On Error GoTo Err_Handler
    mCon.Open ConnectionString:="DRIVER=SQLite3 ODBC Driver; DataBase=" & dbFile
    mCmd.ActiveConnection = mCon
    Exit Sub
Err_Handler:
    Err.Raise Number:=999, Description:=Err.Description
End Sub

Public Sub Execute(ByVal sql As String)
    On Error GoTo Err_Handler
    mSql = sql
    mCmd.CommandText = sql
    mCmd.Execute
    Exit Sub
Err_Handler:
    Err.Raise Number:=999, Description:="SQL: " & mSql & vbCrLf & Err.Description
End Sub

Public Function Query(ByVal sql As String) As Object
    On Error GoTo Err_Handler
    mSql = sql
    mCmd.CommandText = sql
    Set mRS = mCmd.Execute
    Set Query = mRS
    Exit Function
Err_Handler:
    Err.Raise Number:=999, Description:="SQL: " & mSql & vbCrLf & Err.Description
End Function

動作サンプル

上記のクラスを用いて以下の操作を実施している。

• トランザクションで1000件のレコードを書き込み
• 1件更新
• 1件削除
• レコード数および最終の9件の内容をシート上のA,B列に書き込む

Option Explicit

Sub test()
    Dim rs As Object
    Dim dbFile As String
    Dim db As SQLite3ODBC
    
    dbFile = ThisWorkbook.Path & "\" & "testODBC.db"
    Set db = New SQLite3ODBC
    db.Prepare dbFile
    
    db.Execute "CREATE TABLE  IF NOT EXISTS aaa(tt text,ii integer);"
    
    Dim i As Long
    db.Execute "BEGIN TRANSACTION;"
    For i = 1 To 1000
        db.Execute "INSERT INTO aaa VALUES('abc" & i & "', " & i & ");"
    Next
    db.Execute "COMMIT TRANSACTION;"
    db.Execute "UPDATE aaa SET tt='zzz' WHERE ii=995;"
    db.Execute "DELETE FROM aaa WHERE ii=996;"


    Dim r As Range
    Set r = Range("A1")
    
    Set rs = db.Query("SELECT COUNT(*) FROM aaa;")
    r.Value = rs(0)
    Set r = r.Offset(1, 0)
    
    Set rs = db.Query("SELECT * FROM aaa WHERE ii>990;")
    If rs.EOF Then
        MsgBox "MSG : RecordSet Empty"
    Else
        Do Until rs.EOF = True
'            r.Value = rs("tt")
'            r.Offset(0, 1).Value = rs("ii")
            r.Value = rs(0)
            r.Offset(0, 1).Value = rs(1)
            Set r = r.Offset(1)
            rs.MoveNext
        Loop
    End If

    Set rs = Nothing
    Set db = Nothing
End Sub