見出し画像

【Access VBA】データベースのBLOB型カラムを実ファイルに書き込みして保存しよう！

株式会社エアリー：技術ブログ

みなさんはじめまして！エアリー社員のKです！
VBAを使っていて「データベース上に入ってるBLOB型のデータ(以下、バイナリデータ)を実ファイルで保存したいけど、やりかたがわからない！」僕もいろいろと検索しましたが、なかなか見つかりませんでした…。
でももう大丈夫！これを見ればすぐできます！
今回は例としてSQL Serverからバイナリデータを書き込み保存するVBAをご紹介します！


1.準備

SQLServerテーブルの説明

まずは、今回引っ張ってくる元データのテーブルを説明します。

BINARY_DATAテーブル

今回は、物理ファイル名が入ったPHYSICS_FILE_NAMEカラム(varchar(20))とバイナリデータが入っているFILE_DATAカラム(varbinary(max))の２つが追加されたBINARY_DATAテーブルを使用します。
ちなみに、FILE_DATA内のフィールドが全て同じ中身になっていますが、テストデータ用にcsvファイルを作ってコピーしたものなので、本来であれば皆さまそれぞれで異なった値が入っていると思います。
もちろんですが、csvファイル以外のバイナリファイルも保存できますのでご安心ください！

Access VBAの参照設定

Visual Basicを開いて、上部の「ツール」から「参照設定」を押してください。


Access VBA:参照設定画面

今回は、画面の通り3つのライブラリにチェックが入っていれば正常に動きます。

Cドライブにフォルダを作成


パス C:￥Binary

本来はAccessのフォームでフォルダパスを打ち込んでVBAに反映させる方法が望ましいですが、今回は簡易版として直接Cドライブ内にBinaryフォルダを作成しておきます。

それでは、とうとうVBAを作成していきましょう。


2.バイナリデータを書き込みして保存するVBA

ここからが本題です。こちらがVisual Basicのモジュールで書いたVBAです。

Sub saveBinaryFile()
'変数定義
Dim cnSQL As ADODB.Connection 'SQLServer用のコネクション
Dim rsAcs As ADODB.Recordset  'ADODB Recordsetオブジェクト
Dim strSQL 'SQL文一時格納用
Dim adoStrm As Object 'ADODB.Stream用オブジェクト
Dim objFso As Object 'FileSystemObject用オブジェクト
Dim folderPath As String 'フォルダパス文字列格納用
    folderPath = "C:\Binary"
Dim filePath As String 'ファイルパス文字列格納用


Set cnSQL = New ADODB.Connection
'接続文字列
'接続文字列
cnSQL.ConnectionString = "Provider=SQLOLEDB;" _
                        & "Data Source=サーバ名;" _
                        & "Initial Catalog=データベース名;" _
                        & "User ID=ユーザーID;" _
                        & "Password=パスワード;"
cnSQL.Open

strSQL = "SELECT * FROM BINARY_DATA" 'バイナリデータを格納している列を検索するSQL

'Recordsetオープン
Set rsAcs = New ADODB.Recordset
rsAcs.Open strSQL, cnSQL, adOpenForwardOnly, adLockReadOnly

Do Until rsAcs.EOF
    
    Set objFso = CreateObject("Scripting.FileSystemObject")
    
    filePath = folderPath & "\" & rsAcs.Fields("PHYSICS_FILE_NAME").Value
    'ファイルが存在しない場合
    If objFso.FileExists(filePath) = False Then
        'ファイル作成
        objFso.CreateTextFile (filePath)
    End If
   
    Set adoStrm = CreateObject("ADODB.Stream")
    
        adoStrm.Type = "1" 'バイナリモード
        adoStrm.Open
        adoStrm.Write rsAcs.Fields("FILE_DATA").Value 'BLOB列の値をADODB.Streamオブジェクトに書き込み
        adoStrm.SaveToFile filePath, 2 'ADODB.Streamオブジェクトを、ファイルとして保存。
        adoStrm.Close
    
    rsAcs.MoveNext
Loop

MsgBox "処理が完了しました", vbInformation, "バイナリ変換"

'オブジェクト変数を削除、データベースの接続を終了"
Set objFso = Nothing

If Not (cnSQL Is Nothing) Then
    If cnSQL.State <> 0 Then
        cnSQL.Close
    End If
    Set cnSQL = Nothing
End If

If Not (rsAcs Is Nothing) Then
    If rsAcs.State <> 0 Then
        rsAcs.Close
    End If
    Set rsAcs = Nothing
End If

End Sub

次にそれぞれのセクションで細かく分けて、どういう動作を行っているのかを見ていきましょう。

変数定義

'変数定義
Dim cnSQL As ADODB.Connection 'SQLServer用のコネクション
Dim rsAcs As ADODB.Recordset  'ADODB Recordsetオブジェクト
Dim strSQL 'SQL文一時格納用
Dim adoStrm As Object 'ADODB.Stream用オブジェクト
Dim objFso As Object 'FileSystemObject用オブジェクト
Dim folderPath As String 'フォルダパス文字列格納用
    folderPath = "C:\Binary"
Dim fullPath As String 'フルパス文字列格納用

変数定義の部分はこのように定義しています。それぞれ見ていきましょう。

'変数定義
Dim cnSQL As ADODB.Connection '1行目　SQLServer用のコネクション
Dim rsAcs As ADODB.Recordset  '2行目　ADODB Recordsetオブジェクト

1,2行目はADOのコネクションとレコードセットを定義しています。SQLServer側から情報を参照するためのコネクションとAccess側で操作するためのレコードセットです。

Dim adoStrm As Object '4行目　ADODB.Stream用オブジェクト
Dim objFso As Object  '5行目　FileSystemObject用オブジェクト

一度3行目を飛ばして4,5行目の説明をしていきます。
4,5行目が今回の肝となるオブジェクト2つを設定します。
4行目はADODB.Stream用の変数adoStrm。ファイルの作成や読み込み、書き出しを行うために用いられるオブジェクトです。
5行目がFileSystemObject用の変数objFso。ファイル・フォルダを操作する専用のオブジェクトとなっています。
この2つのフォルダ・ファイルへの操作が行えるオブジェクトを使用して、バイナリデータを書き出し保存するというわけです。

Dim strSQL '3行目　SQL文一時格納用
Dim folderPath As String '6行目　フォルダパス文字列格納用
    folderPath = "C:\Binary"
Dim fullPath As String '8行目　フルパス文字列格納用

そして、3,6,8行目はSQL文やフォルダパス・フルパスなどの文字列を一時的に格納しておくための変数を定義しています。
6行目のfolderPathには事前準備で作成したCドライブのパス「C:\Binary」を格納しておきましょう。

コネクション・レコードセット

Set cnSQL = New ADODB.Connection
'接続文字列
cnSQL.ConnectionString = "Provider=SQLOLEDB;" _
                        & "Data Source=サーバ名;" _
                        & "Initial Catalog=データベース名;" _
                        & "User ID=ユーザーID;" _
                        & "Password=パスワード;"
cnSQL.Open

strSQL = "SELECT * FROM BINARY_DATA" 'バイナリデータを格納している列を検索するSQL

'Recordsetオープン
Set rsAcs = New ADODB.Recordset
rsAcs.Open strSQL, cnSQL, adOpenForwardOnly, adLockReadOnly

次はコネクションとレコードセットの部分です。

Set cnSQL = New ADODB.Connection
'接続文字列
cnSQL.ConnectionString = "Provider=SQLOLEDB;" _
                        & "Data Source=サーバ名;" _
                        & "Initial Catalog=データベース名;" _
                        & "User ID=ユーザーID;" _
                        & "Password=パスワード;"
cnSQL.Open

接続文字列は独自で設定しているものを入力して動かしてください。
次にレコードセットで使用するSQL文を見てみましょう。

strSQL = "SELECT * FROM BINARY_DATA" 'バイナリデータを格納している列を検索するSQL

上の1文はSQLServerからデータを取得するためのSQL文を一時格納用の変数に格納しています。今回はテーブルの情報全てが使用したいフィールドになるため「*」でテーブルの全データをとってきていますが、不必要なデータがある場合は以下の様に取得したい要素のみを書いてください。

strSQL = "SELECT PHYSICS_FILE_NAME, FILE_DATA FROM BINARY_DATA" 'バイナリデータを格納している列を検索するSQL

レコードセットの部分はこのようにしてください。

'Recordsetオープン
Set rsAcs = New ADODB.Recordset
rsAcs.Open strSQL, cnSQL, adOpenForwardOnly, adLockReadOnly

１点注意点としては、実務で使用した際に、cursor type(コード上では「adOpenForwardOnly」となっている箇所)が「adOpenStatic」だとBLOB型の読み込みが出来ない事がありました。
使用するデータベースで違いがあり、設定するパラメータを変える必要もあるため、注意してください。

次は取得した値を使ってバイナリデータを書き込みましょう。

バイナリデータを書き込みして保存する

Do Until rsAcs.EOF
    
    Set objFso = CreateObject("Scripting.FileSystemObject")
    
    fullPath = folderPath & "\" & rsAcs.Fields("PHYSICS_FILE_NAME").Value
    'ファイルが存在しない場合
    If objFso.FileExists(fullPath) = False Then
        'ファイル作成
        objFso.CreateTextFile (fullPath)
    End If

    Set adoStrm = CreateObject("ADODB.Stream")
    
        adoStrm.Type = "1" 'バイナリモード
        adoStrm.Open
        adoStrm.Write rsAcs.Fields("FILE_DATA").Value 'BLOB列の値をADODB.Streamオブジェクトに書き込み
        adoStrm.SaveToFile fullPath, 2 'ADODB.Streamオブジェクトを、ファイルとして保存。
        adoStrm.Close
    
    rsAcs.MoveNext
Loop

Do ～ Loopで1レコードずつ参照して書き出します。
条件は「レコードセットで参照しているSQLで取得した結果の最終列まで」としています。
それでは、Do ～ Loopの中身を見ていきましょう。

FileSystemObjectでファイルを作成する

Set objFso = CreateObject("Scripting.FileSystemObject")
    
fullPath = folderPath & "\" & rsAcs.Fields("PHYSICS_FILE_NAME").Value
'ファイルが存在しない場合
If objFso.FileExists(filePath) = False Then
    'ファイル作成
    objFso.CreateTextFile (fullPath)
End If

ここではFileSystemObjectを使用して、「バイナリデータを入れるための空のファイル(骨組みのイメージ)」を作成しています。

Set objFso = CreateObject("Scripting.FileSystemObject")

まずはCreateObject関数でオブジェクトを生成して変数objFsoへ代入します。

fullPath = folderPath & "\" & rsAcs.Fields("PHYSICS_FILE_NAME").Value

変数filePathにフルパスを代入します。
folderPathの中には上部で事前に代入しておいた「C:\Binary」が、rsAcs.Fields("PHYSICS_FILE_NAME").Valueには「test1.csv」という文字列が入っています。
その間に「\」を挟むように結合して「C:\Binary\test1.csv」というフルパスを作成して、fullPathへ代入します。

'ファイルが存在しない場合
If objFso.FileExists(fullPath) = False Then
    'ファイル作成
    objFso.CreateTextFile (fullPath)
End If

そのfullPathを引数に渡してファイルの存在チェックを行います。ファイルが存在していない場合は新しくファイルを作成する処理をIf文の中に書いています。

ここまでの動作確認を行うために、End Ifまでステップイン実行を行うと…


空のcsvファイル

空のcsvファイルが出力されていることが確認できます！


空のcsvファイルの中身

もちろんですが、中身も空っぽです。この中にデータを書き込んでいきます。またコードへ戻りましょう。

ADODB.Streamで書き込みして保存する

 Set adoStrm = CreateObject("ADODB.Stream")
    
  　adoStrm.Type = "1" 'バイナリモード
    adoStrm.Open
    adoStrm.Write rsAcs.Fields("FILE_DATA").Value 'BLOB列の値をADODB.Streamオブジェクトに書き込み
    adoStrm.SaveToFile filePath, 2 'ADODB.Streamオブジェクトを、ファイルとして保存。
    adoStrm.Close
    
rsAcs.MoveNext

骨組みのファイルは作成できたので、次は「バイナリデータを実ファイルとして書き込みして保存する(肉付けのイメージ)」コードを書いていきましょう。

 Set adoStrm = CreateObject("ADODB.Stream")

おなじみCreateObject関数でADODB.Streamを生成して、変数adoStremへ代入します。

adoStrm.Type = "1" 'バイナリモード
adoStrm.Open
adoStrm.Write rsAcs.Fields("FILE_DATA").Value 'BLOB列の値をADODB.Streamオブジェクトに書き込み
adoStrm.SaveToFile filePath, 2 'ADODB.Streamオブジェクトを、ファイルとして保存。
adoStrm.Close
adoStrm.Type = "1" 'バイナリモード
adoStrm.Open

Typeプロパティでは1と2が選択できます。「1」がバイナリモード。「2」がテキストモードです。今回はバイナリデータを扱うため、間違わないように気をつけてください。

adoStrm.Write rsAcs.Fields("FILE_DATA").Value

そしてWriteメソッドを使用して、対象となるデータをパラメータとして渡しStreamオブジェクトへ書き込みます。

adoStrm.SaveToFile 保存したいパス, 1,または2 'ADODB.Streamオブジェクトを、ファイルとして保存。
adoStrm.Close

最後に、書き込まれた内容をどこに保存するかをSaveToFileメソッドを使って指定します。第2パラメータでは1,または2が選択できて、「1」はファイルがない場合に新規作成。「2」はファイルがある場合に上書き保存です。
使用する場合に応じて変更してください。

rsAcs.MoveNext

ここまでで1連の動作は終了です。最後に次のフィールドを参照するためにMoveNextとLoopを書けば、バイナリデータが格納されているフィールド全てを実ファイルとして書き込みして保存することができます。

完了メッセージと終了操作

MsgBox "処理が完了しました", vbInformation, "バイナリ変換"

'オブジェクト変数を削除、データベースの接続を終了"
Set objFso = Nothing

If Not (cnSQL Is Nothing) Then
    If cnSQL.State <> 0 Then
        cnSQL.Close
    End If
    Set cnSQL = Nothing
End If

If Not (rsAcs Is Nothing) Then
    If rsAcs.State <> 0 Then
        rsAcs.Close
    End If
    Set rsAcs = Nothing
End If

動かした後は完了メッセージで教えてほしいですよね！
MsgBoxで完了メッセージを表示させましょう！
あとはFileSystemObjectだけ終了操作していないので終了させて、データベースの接続も終了させましょう！

3.動作確認

コードの記述が終わったらF5キーでコードの実行をしてみてください。


完了メッセージのダイアログ

完了メッセージが表示されて、OKを押したら動作が終了しているはずです。
Cドライブ内のフォルダの中を確認しにいくと…


処理終了後のフォルダ内

全てのファイルが書き込まれています！中身も文字化けせずにしっかり入っていることも確認してください！

まとめ

いかがでしたでしょうか？
Accessってメソッドとかプロパティとか多くて大変ですよね…。でもその代わり、なんでもできちゃうのがいいところ！皆さんも一緒に励みましょう！参考になればいつでも見直しにきてくださいね！
ではまた次の記事でお会いしましょう！

我々、株式会社エアリーは、
千葉で働きたい・頑張りたい人材を募集しております！

新卒採用・キャリア採用どちらも募集しております！
以下HPより、皆様のご応募お待ちしております。

【株式会社エアリー：お問合せフォーム】


この記事が気に入ったらサポートをしてみませんか？