Access VBA DAOで写真１万枚を管理するデータベースを作成
DAOによる添付ファイルの取り扱い

Accessによる写真管理の実情とこのページでやりたいこと

　Accessで写真を管理しようとした場合、データの保存容量が問題となってきます。Accessデータベースの保存容量の限界は僅か２ギガバイトです。しかも、この２ギガバイトという数字は、データ部分だけではなくオブジェクトの容量も合計した数字になります。 数値データや文字列データを管理するのであれば２ギガバイトというのはとてつもなく大きな容量で、Accessで個人利用のデスクトップデータベースを作成することに限って考えれば、この２ギガバイトというサイズを使いきることは想定しなくても良いと思います。

　一方、写真の場合はどうでしょうか。ここで議論している写真は一枚が５メガバイト程度のもので、デジタル一眼レフカメラで撮影したレベルの本格的な写真です。（ただしJPEG形式に限ります。） 簡単な割り算です。２ギガバイトを５メガバイトで割れば、だいたい４００枚になります。僅か４００枚の写真を管理することに意味が有るでしょうか。 このことから分かるのは、Accessは写真を直接管理することを想定して作られていないということです。困ったものです。

　Accessのライバルと言われることもあるFileMakerはどうでしょうか。FileMakerの最大容量はいくらなのか、知らなかったので調べてみたのですが、FileMaker Pro 17 Advanced の技術仕様によると、 ファイルサイズは「ディスクスペースによってのみ制限されます。ハードディスクおよびOSのAPI能力に応じて、最大８TB（テラバイト）です。」とのことです。 つまり、FileMakerは容量無制限ということです。

　FileMakerは、以前から写真を管理するデータベースに力を入れている印象があります。写真管理についてはAccessよりも遥かに進んでいるようですが、私はFileMakerの使い方を知りませんし、新しいことが苦手です。しかもAccessの使い方なら良く知っています。 仕方がないので、このページで、無理矢理Accessで写真を管理するデータベースを作っていきます。

　対象となる写真は、一枚当たり５メガバイトとか８メガバイトとかのサイズのJPEG形式、管理する枚数は最低１万枚、できれば１０万枚です。 んで、最終的にはこのページの最下部で示しましたが、実際に１万枚の写真を管理するところまでを実現し、計算上は２０万枚ぐらいまでなら管理できるであろうとの結論となりました。

　そんなのできるわけがないと思われた方はAccessをよくご存じの方です。おっしゃる通り普通はできないので、少し工夫しています。どんな方法か興味を持たれた方は、かなり長いページですが最後までご覧ください。

概要説明

　Accessで写真管理をするデータベースを作っていく様子をYouTube動画で解説していきます。まずは概要説明です。

Accessで写真を管理する場合の問題点

　Accessで写真を管理するデータベースを作る場合、Accessデータベースのファイルサイズの制限が問題となってきます。上でも書きましたがファイルサイズは２ギガバイトに制限されています。

よくある解決策とその駄目な点

　Accessで写真を管理するデータベースを作る場合、よくある手法はデータベースには写真ファイルそのものは保存せず、写真ファイルのファイル名を保存することで代替えしようというものです。 データベースに写真を保存しないわけですから、容量の問題はクリアできますが、写真の一覧表示をすることができないことに不満が有ります。 写真を管理するデータベースを作りましたと言って、写真の一覧表示が出来ないというのは致命的な欠陥です。 写真管理のデータベースで、ファイル名を見て写真を選ぶのでしょうか。画像を見て選ぶでしょ。ってこと。

　それならば、イメージコントロールのコントロールソースに写真のファイル名を設定することで、写真の一覧表示は出来るようになるから、その方法では駄目なのかという意見もあるでしょう。 しかし、そのような方法では写真の表示が遅く使い物になりません。１０枚、２０枚の写真を一覧表示したいのではないのです。最低１万枚だって言ってるでしょ。 パソコンの性能を上げて、ビデオカードを変えて、とか言い出す人が出てきそうですが、ハードウェアに頼るのではなくてソフトウェアを工夫したいのです。

私が提案する解決策

　私が提案する解決策は縮小写真をAccessの添付ファイル型フィールドに保存することです。 写真を一覧表示する場合に必要となる大きさはサムネイルサイズの写真で構わないわけです。小さく縮小した写真をテーブルに保存することで問題は解決します。

写真を縮小して添付ファイルにするには

　写真の縮小にはデジカメまとめてリサイズを使用します。デジカメまとめてリサイズは私が開発したフリーソフトです。 フォルダに保存されている沢山のデジカメ写真を指定のサイズに縮小し、ファイル名のフルパスの一覧をCSV形式のテキストファイルを保存することができます。 このCSVファイルをAccessにインポートし、インポートした縮小写真ファイルパスを元にAccess VBAのDAOを使用して縮小写真を自動で添付ファイル型フィールドに保存します。

テーブル作成

　テーブルを設計していきます。

CSVファイルをリンクテーブルにする

　デジカメまとめてリサイズで写真を縮小すると同時にCSV形式のテキストファイルを作成し、CSVファイルをAccessのリンクテーブルとしました。

リンクテーブルを元に追加クエリを作成

　リンクテーブルを元に追加クエリを作成しました。CSVファイルを上書きすることでリンクテーブルが更新されますので、何やら実用的な予感がします。

フォームの作成と選択クエリの作成

　フォームに配置したテキストボックスに日付を入力し、その日付に合致する撮影日の写真を抽出する選択クエリを作成しました。 その選択クエリをレコードソースとしたフォームを作ることで何だかうまくいきました。 タマゴが先かニワトリが先か、といった状況になっているのかなと思います。

撮影日の日付範囲を指定して抽出するクエリ

　撮影日の範囲を指定して抽出できるようにしました。これは選択クエリで日付範囲を指定するだけなので、このページをご覧になっている皆様のレベルでは簡単すぎる内容だと思います。

フォームのレコードソースを変更するVBA

　選択クエリの点数を増やしてみました。 「撮影日」だけではなく、「撮影場所」や「分類」フィールドの入力で抽出できる選択クエリを作成し、それらをフォームのレコードソースにしました。 レコードソースは「日付」「撮影場所」「分類」の入力で切り替わりますので、ユーザー目線ではレコードソースが切り替わっていることには気づかないと思います。 フィルターしているように見えるのではないでしょうか。

フォームのレコードソースをチェックボックスで切り替える

　選択クエリを複数作成しておき、チェックボックスの状態によってフォームのレコードソースをどの選択クエリにするかを切り替えることにしました。 簡単なVBAコードで複雑な処理ができるようになったと思います。 難しいところは何もありませんが、面白いのはReQueryと書くことでフォームが再描画されるところですね。ホームタブにある「すべて更新」ボタンを押したのと同様の効果が得られます。

　VBAのコードの中でSQLを使用して選択クエリを作るほうが良いという意見もあるかと思いますが、私はVBA以外の方法で実現可能なことはなるべくVBAを使わないようにしようと考えています。 なるべくオブジェクトの組み合わせで作った方が分かりやすいのではないでしょうか。VBAって分かり難いんですよね。

縮小写真のクリックで縮小前の写真を表示する

　縮小写真が表示されているイメージコントロールをクリックすることで、縮小前の写真をWindowsに付属のフォトというソフトウェアで開くようにしました。

　　　CreateObject("Shell.Application").ShellExecute Me."写真ファイルパス"

　このようにすることで、写真ファイルパステキストボックスに表示されているファイル名のJPEG画像をフォトで開くことができます。 フォトで開いてしまえば、写真の拡大表示が出来ます。あたかもAccessの機能の一部のようにフォトを使うことで、何だかレベルの高いデータベースになった気がします。

DAOで縮小写真の自動添付

　前置きが長くなりました。ここからDAOを使って添付ファイル型のフィールドに自動でファイルを添付していくVBAコードを書いていきます。

　動画の中で紹介しているVBAのコードを示します。インテンドがうまくできなかったので、見難いかもしれませんがご容赦ください。

Dim db As DAO.Database
Dim rsImageTable As DAO.Recordset2
Dim rsPictures As DAO.Recordset2
Dim sFileMei As String
Dim bTenpu As Boolean

Set db = CurrentDb
Set rsImageTable = db.OpenRecordset("写真テーブル")

Do Until rsImageTable.EOF

rsImageTable.Edit
Set rsPictures = rsImageTable.Fields("縮小写真").Value
bTenpu = rsImageTable.Fields("添付済").Value

If bTenpu = False Then

sFileMei = rsImageTable.Fields("縮小写真ファイルパス").Value

rsPictures.AddNew
rsPictures("FileData").LoadFromFile sFileMei
rsImageTable.Fields("添付済").Value = True

rsPictures.Update
rsImageTable.Update

End If

rsImageTable.MoveNext

Loop

　詳しい説明は動画の中で行っていますので動画を参照していただきたいのですが、この例ではbTenpuとう変数の値によって添付ファイルを付ける処理をするかどうかを決めています。 bTenpuとう変数はAccessテーブルの「添付済」フィールドの値です。このような回りくどいことをしているのは、この動画の作成時点では、添付ファイル型のフィールドに何個のファイルが添付されているのかを調べるVBAコードを書くことが私にはできなかったためです。 本来であれば添付ファイルが何個付いているかを見て、新たにファイルを添付するかしないかを決めればよいことなのです。 その方法は下の動画で解説しています。

DAOで縮小写真の自動添付

　ここで説明したいことはAccessのDAOで 「添付ファイル数の数を見る方法」 です。 添付ファイル型のフィールドというのはDAOのレコードセットとして取り出すことができるというのがポイントです。

　動画の中で紹介しているVBAのコードを示します。

Dim db As DAO.Database
Dim rsImageTable As DAO.Recordset2
Dim rsPictures As DAO.Recordset2
Dim sFileMei As String

Set db = CurrentDb
Set rsImageTable = db.OpenRecordset("写真テーブル")

Do Until rsImageTable.EOF

rsImageTable.Edit
Set rsPictures = rsImageTable.Fields("縮小写真").Value

Dim attachCount As Long
attachCount = 0

Do Until rsPictures.EOF
attachCount = attachCount + 1
rsPictures.MoveNext
Loop

If attachCount = 0 Then

sFileMei = rsImageTable.Fields("縮小写真ファイルパス").Value

rsPictures.AddNew
rsPictures("FileData").LoadFromFile sFileMei

rsPictures.Update
rsImageTable.Update

End If

rsImageTable.MoveNext

Loop

　ポイントは

Set rsPictures = rsImageTable.Fields("縮小写真").Value

　として、rsPicturesという変数に「縮小写真」フィールドをレコードセットとして読み込んでいることです。 「縮小写真」フィールドは添付ファイル型なのですが、添付ファイル型フィールドはレコードセットとして読み込むことができます。

　rsPicturesがレコードセットですから、以下のコードでrsPicturesのレコード数をカウントします。

attachCount = 0

Do Until rsPictures.EOF
attachCount = attachCount + 1
rsPictures.MoveNext
Loop
>

　レコード数が０の場合は写真を添付し、レコード数が１の場合は既に写真が添付されてますから何も処理をしないというわけです。

If attachCount = 0 Then

　で縮小写真を添付するかしないかを決めます。

　以下の一行でsFileMeiといういファイル名のファイルを添付ファイルにします。

rsPictures("FileData").LoadFromFile sFileMei

　詳しくは動画を参照してください。

RecordCountプロパティ

　レコードセットのレコード数を数える方法をくどくどと説明してしまいましたが、レコードセットのレコード数はRecordCountプロパティを見ることで簡単に分かりました。詳しくは以下の動画を参照してください。

　動画の中で紹介しているVBAのコードを示します。

Dim db As DAO.Database
Dim rsImageTable As DAO.Recordset2
Dim rsPictures As DAO.Recordset2
Dim sFileMei As String

Set db = CurrentDb
Set rsImageTable = db.OpenRecordset("写真テーブル")

Do Until rsImageTable.EOF

rsImageTable.Edit
Set rsPictures = rsImageTable.Fields("縮小写真").Value

If rsPictures.RecordCount = 0 Then

sFileMei = rsImageTable.Fields("縮小写真ファイルパス").Value

rsPictures.AddNew
rsPictures("FileData").LoadFromFile sFileMei

rsPictures.Update
rsImageTable.Update

End If

rsImageTable.MoveNext

Loop

FileCopyステートメント

　これまでの処理で写真のファイル名が保存されたテーブルができているわけですが、FileCopyステートメントを使用してファイルをコピーして写真ファイルを取り出す処理を解説しました。 詳しくは以下の動画をご覧ください。

　動画の中で紹介しているVBAのコードを示します。

Dim db As DAO.Database
Dim rsImageTable As DAO.Recordset2
Dim rsPictures As DAO.Recordset2
Dim sFileMei As String

Set db = CurrentDb
Set rsImageTable = db.OpenRecordset("写真テーブル")

Do Until rsImageTable.EOF

rsImageTable.Edit
Set rsPictures = rsImageTable.Fields("縮小写真").Value

If rsPictures.RecordCount = 0 Then

sFileMei = rsImageTable.Fields("縮小写真ファイルパス").Value

rsPictures.AddNew
rsPictures("FileData").LoadFromFile sFileMei

rsPictures.Update
rsImageTable.Update

End If

rsImageTable.MoveNext

Loop

FileDialog

　ファイルのコピー先のフォルダーを選択するダイアログを表示出来るようにしました。

ファイルの更新日を取得する

　写真ファイルの更新日を取得して、撮影日フィールドに入力するようにしました。

　動画の中で紹介しているVBAのコードを示します。

Dim db As DAO.Database
Dim rsImageTable As DAO.Recordset2 '写真テーブルのレコードセット
Dim rsPictures As DAO.Recordset2 '添付ファイルのレコードセット
Dim sFileMei As String '縮小ファイルパス
Dim mFileMei As String '写真ファイルパス
Dim mFile As Object

Set db = CurrentDb
Set rsImageTable = db.OpenRecordset("写真テーブル")
Do Until rsImageTable.EOF

rsImageTable.Edit
Set rsPictures = rsImageTable.Fields("縮小写真").Value

If rsPictures.RecordCount = 0 Then '添付ファイルの数が0ならば

sFileMei = rsImageTable.Fields("縮小写真ファイルパス").Value
mFileMei = rsImageTable.Fields("写真ファイルパス").Value

rsPictures.AddNew
rsPictures("FileData").LoadFromFile sFileMei

Set mFile = CreateObject("Scripting.FileSystemObject").GetFile(mFileMei)
rsImageTable.Fields("撮影日").Value = mFile.DateLastModified
Set mFile = Nothing

rsPictures.Update
rsImageTable.Update

End If

rsImageTable.MoveNext

Loop

写真１０００枚を自動添付する

　写真１００枚、写真１０００枚を自動で添付ファイル型フィールドに添付する処理を示しました。

細かな修正

　細かな点を修正しました。

写真１万枚を添付ファイルにして動作確認

　写真１万枚を添付ファイル型フィールドに添付し、動作確認を行いました。写真１万枚をリストに一覧表示した状態でも実用的な表示速度で動作しました。