Contents
過去に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件のコメント