EXCEL VBAメモ - SQLite3データベースのアクセス(SQLite ODBC使用)
ExcelからODBC経由でSQLite3へアクセスする方法
準備
- 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*.*というファイルがコピーされるが通常のアンインストールで削除されないファイルがあるので完全にアンインストールを行う場合には手動で削除する必要がある。
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