VBAからSQLiteにアクセスしたいお年頃

過去にC#からSQLiteをゴリゴリしたことがあるんですが、今回はVBAからやっちまいます。

C#の時は色々と困りもしましたが、VBAは比較的簡単です。
ちなみにExcel32bit版のみの話です。
最近はついに64bit版にも対応したSQLiteForExcelってライブラリっていうかsqlite3のAPI叩き方のサンプル集?があるっぽい。
SQLCipherを使わないと暗号化できない(んだよね?)ので、自分でライブラリ作らないといけない。
だったらC#でSystem.Data.SQLite使うよ!

準備

OLEコントロール登録

こちらからvbRichClientをダウンロードします。今はversion5.0.10です。
2013/11/17に更新されて、今は2014/4/2なので結構新しいですね。
解凍したファイルを適当なディレクトリに配置して、説明にある通りコマンドプロンプトを管理者モードで起動し、

regsvr32 "配置したパス\vbRichClient5.dll"

と叩いてOLEコントロール登録の成功を確認しておきます。

VBAプロジェクトの参照設定

次にVBA側は参照設定を開いて、『vbRichClient5』をチェックします。

これで準備は完了です!早い!
要するにvbRichClient5はCOMとして配布されているってことですね~
sqlite3.dllをPATH通った場所に置くぐらい必要かと思ったんですが、
手持ちのWin8で試してみたら必要ありませんでした。

DB用意

ま、適当なツール使って、適当なテーブル作って用意して下さい。
以前SQLiteも暗号化できると書きましたが、最近のツールさんは暗号化までやってくれるので便利ですね~!
今回はフリーのPupSQLiteさんを利用させて頂きました!
“sample”をパスワードに暗号化しておきます。

DBもVBAで用意したいなら

読んでみる

Public Sub ReadAll()

On Error GoTo ErrFunc

    Dim objConn As New vbRichClient5.cConnection
    Dim objRecSet As vbRichClient5.cRecordset
    Dim result As String: result = "とれたデータ" & vbCrLf
    Dim i As Long
    If Not objConn.OpenDB("dbファイル保存パス", "sample") Then
        Err.Raise 1, "", "objConn OpenDB Err"
    End If

    Set objRecSet = objConn.OpenRecordset("SELECT * FROM Sample;")
    Do While Not objRecSet.EOF
        For i = 0 To objRecSet.Fields.Count - 1
            If i <> 0 Then
                result = result & " / "
            End If
            result = result & objRecSet.Fields(i).Name & "=" & _
                        objRecSet.Fields(i).Value
        Next i
        result = result & vbCrLf
        objRecSet.MoveNext
    Loop

    MsgBox result

    GoTo ExitFunc
ErrFunc:
    MsgBox Err.Description

ExitFunc:
    Set objConn = Nothing
    Set objRecSet = Nothing
End Sub

ConnectionはvbRichClient5.cConnectionを利用します。
DBのOpenにはOpenDB(“dbファイル保存場所”, “パスワード”)です。
パスワードがかかっていない場合は、単純にOpenDB(“dbファイル保存場所”)でOKです。
OpenDBReadOnly関数もありますが、10万レコードまで試してみても読み取り速度は変化ありませんでした。
逆にレコード数に依存しないってスゴイな!

取り出し方もC#のDataSetに近いでしょうか。
objConn.OpenRecordset(“SQL文”)でvbRichClient5.cRecordsetが取得できます。
ところでインジェクション防ぐのにこのままじゃまずいですよ。

取得したcRecordset.Fieldsに1レコード文のデータが格納されます。
cRecordset.Fillds(i)のNameプロパティにはカラム名、Valueプロパティには格納値が入っているので、上のサンプルソースはループで全レコード、全カラムを抜き出しています。
1レコード読み終われば、MoveNextへ次に、終端チェックはEOFで判定します。
他にもMoveFirst、MoveLast、MovePreviousなどの移動関数が揃ってますね。
Sortプロパティもあるから後からソートもできるのかな?

ところで、vbRichClient5.cConnectionにCloseがないんですよ。
なのでNothingぶち込んで終わらせています。
どうやら、OpenDBした時点でロックがかかるということはなく、cConnectionオブジェクト自体は同時に複数共存しても問題ないみたいです。
ロックがかかるのはあくまでトランザクション開始時、ということで

トランザクション開始時には

objConn.Synchronous = False
objConn.BeginTrans

してあげて、テーブル更新時はエラー処理も忘れずに。

If Not objConn.Execute("更新用SQL文") Then
    objConn.RollbackTrans
    Err.Raise 1, "", "RollBackするよ!"
End If
objConn.CommitTrans
objConn.Synchronous = True

トランザクション中、別のcConnectionからBeginTransしようとするとErrが発生します。
Err.Sourceが”vbRichiClient5″、Err.Descriptionは”Too busy to execute SQL-Statement”でした。

トランザクション中でも別cConnectionからOpenRecordsetによる取得はできます。
もちろんトランザクション中は反映されていないデータですけどね。
トランザクション中のオブジェクトからなら、コミット前でも最新の状態が取れます。


他にもニッチなIT関連要素をまとめていますので、よければ一覧記事もご覧ください。

3件のコメント

返信を残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

日本語が含まれない投稿は無視されますのでご注意ください。(スパム対策)