この記事では、AccessのデータベースファイルにあるクエリのSQL文をすべて取得する方法についてご説明します。
【動画】AccessのデータベースファイルにあるクエリのSQL文をすべて取得する実際の動き
本題に入る前に、まずは次の動画をご覧ください。
Accessのデータベースファイルを開き、クエリ情報を取得します。
取得したクエリ情報の中からクエリのSQL文を取得してExcelのシートに出力しています。
今回のサンプルのマクロでは、指定したフォルダ配下全てのAccessのデータベースファイルに対してクエリの取得・Excelのシートへの出力を行っています。
マクロ作成の流れ
Accessのデータベースファイルは拡張子が「mdb」と「accdb」のファイルです。
ExcelファイルとAccessのデータベースファイルの例
今回は次のExcelファイルを用意しました。
今回Accessデータベースファイルの検索先は「C:¥work¥10_勉強¥10_VBA関連¥0270¥file」配下とし、フォルダは下のとおりです
フォルダ内にあるAccessのデータベースファイルは下の5つが存在します。
- 0270_1.accdb
- 0270_2.mdb
- 0270_3.mdb
- 0270_4.accdb
- 0270_5.mdb
DELETE * FROM T_明細;
SELECT T_商品S.商品区分コード, T_商品S.商品区分 FROM T_商品S;
SELECT T_伝票.伝票番号, T_伝票.売上日, T_伝票.得意先コード FROM T_伝票;
SELECT T_得意先.得意先コード, T_得意先.得意先名, T_得意先.[フリガナ], T_得意先.[〒], T_得意先.住所1, T_得意先.住所2, T_得意先.TEL FROM T_得意先;
SELECT T_社員.項番, T_社員.名前, T_社員.社員番号, T_社員.年齢, T_社員.出身, T_社員.入社年, T_社員.所属部署, T_社員.役職 FROM T_社員;
SELECT T_成績表.名前, T_成績表.国語, T_成績表.数学, T_成績表.物理, T_成績表.化学, T_成績表.世界史, T_成績表.英語 FROM T_成績表;
マクロを実行すると、クエリのSQL文がExcelのシートに出力されます。
なお、今回のマクロではクエリのSQL文だけでなく、どのAccessのデータベースファイルのクエリなのか、どこにあるAccessのデータベースファイルなのかといった情報も合わせて出力させています。
コードの例
Option Explicit
Private Sub btn_exec_Click()
Dim ws As Worksheet 'ワークシート変数
Dim fso As Object 'FileSystemObjectのインスタンス用変数
Dim cellPosCnt As Long 'セルの位置用カウンタ
Dim FolderPath As String 'フォルダとファイルを検索したいフォルダのトップ階層のパス
Dim dbFPathAry() As String '(Accessのデータベース)ファイルのフルパスを格納する配列
Dim dbFPathAryCnt As Long '配列dbFPathAryの要素数カウント用カウンタ
Dim db As Object 'Accessデータベースファイル用インスタンス
Dim qdf As DAO.QueryDef 'クエリの情報用変数
'データを出力する最初の行位置
Const bgnRowPos As Long = 8
'データを出力する最初の行位置を取得する
cellPosCnt = bgnRowPos
'シートを取得する
Set ws = Worksheets("work")
'フォルダとファイルを検索したいフォルダのトップ階層のパスを取得する
FolderPath = Worksheets("work").Range("dirPath").Value
'シートをクリアする
ws.Range("A" & bgnRowPos & ":E1000").ClearContents
'FileSystemObjectのインスタンスを生成する
Set fso = CreateObject("Scripting.FileSystemObject")
'フォルダとファイルを検索するサブルーチンを呼び出す
Call fileSearch(FolderPath, dbFPathAry(), dbFPathAryCnt)
'Accessのデータベースファイルを開く
Set db = CreateObject("Access.Application")
'Accessのデータベースファイルの数分処理を繰り返すFor文
For dbFPathAryCnt = 0 To UBound(dbFPathAry)
'Accessのデータベースファイルを開く
db.OpenCurrentDatabase dbFPathAry(dbFPathAryCnt)
'クエリの数分繰り返すFor文
For Each qdf In db.CurrentDb.QueryDefs
If InStr(qdf.Name, "~") = 0 Then
'クエリ名に「~」が含まれていない場合
'A列のセルに項番の値を出力する
ws.Range("A" & cellPosCnt).Value = cellPosCnt - bgnRowPos + 1
'B列のセルにAccessのデータベースファイルのフォルダパスを出力する
ws.Range("B" & cellPosCnt).Value = fso.GetFile(dbFPathAry(dbFPathAryCnt)).ParentFolder.path
'C列のセルにAccessのデータベースファイル名を出力する
ws.Range("C" & cellPosCnt).Value = fso.GetFile(dbFPathAry(dbFPathAryCnt)).Name
'D列のセルにクエリ名を出力する
ws.Range("D" & cellPosCnt).Value = qdf.Name
'E列のセルにクエリのSQL文を出力する
ws.Range("E" & cellPosCnt).Value = qdf.sql
cellPosCnt = cellPosCnt + 1
End If
DoEvents
Next qdf
'Accessのデータベースファイルを閉じる
db.CloseCurrentDatabase
Next dbFPathAryCnt
Set db = Nothing
End Sub
Sub fileSearch(FolderPath As String, dbFPathAry() As String, dbFPathAryCnt As Long)
Dim fso As Object 'FileSystemObjectのインスタンス用変数
Dim Folder As Object 'フォルダ用変数
Dim FileItem As Object '取得したファイル用変数
Dim SubFolder As Object 'サブフォルダ用変数
'FileSystemObjectのインスタンスを生成する
Set fso = CreateObject("Scripting.FileSystemObject")
'GetFolderメソッドを使用して、指定したパスのフォルダを取得する
Set Folder = fso.GetFolder(FolderPath)
'フォルダ内にあるファイルの数分処理を繰り返す
For Each FileItem In Folder.Files
Select Case fso.GetExtensionName(FileItem.path)
Case "mdb", "accdb"
'ファイル名の拡張子に「mdb」または「accdb」が含まれる場合
'⇒(Accessのデータベースファイルの場合)
'配列dbFPathAryを再定義する
ReDim Preserve dbFPathAry(dbFPathAryCnt)
'(Accessのデータベース)ファイルのフルパスを取得して配列dbFPathAryに格納する
dbFPathAry(dbFPathAryCnt) = FileItem.path
dbFPathAryCnt = dbFPathAryCnt + 1
End Select
DoEvents
Next FileItem
'パスのサブフォルダを対象にループする
For Each SubFolder In Folder.SubFolders
'本サブルーチンを再帰呼び出しする
Call fileSearch(SubFolder.path, dbFPathAry(), dbFPathAryCnt)
Next SubFolder
End Sub
注目すべきコード①
最初に見て頂きたいのは24行目です。
'フォルダとファイルを検索したいフォルダのトップ階層のパスを取得する
FolderPath = Worksheets("work").Range("dirPath").Value
コードの説明
以上のコードは、Accessのデータベースファイルの置き場を取得しているコードです。
Accessのデータベースファイルを検索する対象のトップフォルダのパスの取得は、A5の黄色のセルに入力されたパスから取得します。
※A5の黄色のセルには「dirPath」という名前を付けています。
注目すべきコード②
次に見て頂きたいのは33行目です。
'フォルダとファイルを検索するサブルーチンを呼び出す
Call fileSearch(FolderPath, dbFPathAry(), dbFPathAryCnt)
コードの説明
以上のコードは、サブフォルダ含めて全てのフォルダ内のファイルを検索するサブルーチン「fileSearch」を呼び出す処理です。
「fileSearch」には以下の引数を渡して呼び出します。
- FolderPath：ファイル名の検索先フォルダ
- dbFPathAry()：Accessのデータベースファイル名のフルパスを格納する配列
- dbFPathAryCnt：配列dbFPathAryの要素数用のカウンタ変数
FolderPath
FolderPathにはファイル名の検索先フォルダを指定します。
FolderPathで指定されたフォルダを「fileSearch」に渡し、「fileSearch」内でファイルが存在するか検索します。
dbFPathAry()
dbFPathAryは、サブルーチン「fileSearch」内で取得するAccessのデータベースファイルのフルパスを格納する配列です。
dbFPathAryCnt
dbFPathAryCntは、配列dbFPathAryの要素数用のカウンタ変数です。
注目すべきコード③
次に見て頂きたいのは91行目から94行目です。
'FileSystemObjectのインスタンスを生成する
Set fso = CreateObject("Scripting.FileSystemObject")
'GetFolderメソッドを使用して、指定したパスのフォルダを取得する
Set Folder = fso.GetFolder(FolderPath)
コードの説明
以上のコードは、FileSystemObjectのインスタンスを生成し、フォルダとファイルを検索したいフォルダのトップ階層のパスのフォルダを取得する処理のコードです。
FileSystemObjectのインスタンスのGetFolderメソッドに、フォルダとファイルを検索したいフォルダのトップ階層のパスを指定して実行することで、パス内のフォルダ名やファイル名を取得できます。
注目すべきコード④
次に見て頂きたいのは97行目から118行目です。
'フォルダ内にあるファイルの数分処理を繰り返す
For Each FileItem In Folder.Files
Select Case fso.GetExtensionName(FileItem.path)
Case "mdb", "accdb"
'ファイル名の拡張子に「mdb」または「accdb」が含まれる場合
'⇒(Accessのデータベースファイルの場合)
'配列dbFPathAryを再定義する
ReDim Preserve dbFPathAry(dbFPathAryCnt)
'(Accessのデータベース)ファイルのフルパスを取得して配列dbFPathAryに格納する
dbFPathAry(dbFPathAryCnt) = FileItem.path
dbFPathAryCnt = dbFPathAryCnt + 1
End Select
DoEvents
Next FileItem
コードの説明
以上のコードは、フォルダ内にあるファイルがAccessのデータベースファイルかどうかを判定し、Accessのデータベースファイルの場合はそのファイルのフルパスを配列dbFPathAryに格納する処理のコードです。
フォルダ内にあるファイルがAccessのデータベースファイルかどうかはGetExtensionNameを使い、ファイルの拡張子に「mdb」または「accdb」が含まれるかどうかで判定しています。
コードの詳細
97行目のコードは、フォルダ内にあるファイルの数分処理を繰り返すFor文で、もしファイルの拡張子が「mdb」または「accdb」かどうかを101行目で判定します。
ファイルの拡張子が「mdb」または「accdb」の場合は106行目で配列dbFPathAryを再定義し、110行目でAccessのデータベースファイルのフルパスを取得して配列dbFPathAryに格納します。
注目すべきコード⑤
次に見て頂きたいのは121行目から126行目です。
'パスのサブフォルダを対象にループする
For Each SubFolder In Folder.SubFolders
'本サブルーチンを再帰呼び出しする
Call fileSearch(SubFolder.path, dbFPathAry(), dbFPathAryCnt)
Next SubFolder
コードの説明
以上のコードは、サブフォルダが存在する間は自分自身のサブルーチンを繰り返し呼び出す再帰処理を行っているコードです。
再帰処理とは、サブルーチンの処理が行われている途中で、強制的に自分自身のサブルーチンを呼び出して再度サブルーチンの最初から処理を行わせることを言います。
強制的に自分自身のサブルーチンを呼び出しているのは124行目です。
124行目でfileSearchが呼び出されると、fileSearchの処理の途中で83行目(fileSearchの最初)から強制的に開始されます。
この再帰処理(fileSearch処理中の再呼び出し)は、121行目のFor文内で繰り返し行われますが、このFor文が終わらないかぎりfileSearchが再度呼び出されます。
なお、For文から抜ける条件は、すべてのサブフォルダの参照が終わることです。(引数にサブフォルダを指定してfileSearchを呼び出す)
すべてのサブフォルダを参照すればFor文を抜けます。
For文から抜ければfileSearchが呼び出されることがなくなるので(再帰処理が行われない)、fileSearchのEnd Subまで処理が進みfileSearchの処理を抜けて、fileSearchの呼び出し元の33行目に遷移します。
そのまま33行目の次の行に処理が遷移します。
注目すべきコード⑥
次に見て頂きたいのは36行目から42行目です。
'Accessのデータベースファイルを開く
Set db = CreateObject("Access.Application")
コードの説明
以上のコードは、Accessデータベースファイル用インスタンスを生成するコードです。
ExcelのマクロがAccessのデータベースファイルを開くのにこのインスタンスを使用します。(閉じる時も使います)
注目すべきコード⑦
次に見て頂きたいのは39行目から77行目です。
'Accessのデータベースファイルの数分処理を繰り返すFor文
For dbFPathAryCnt = 0 To UBound(dbFPathAry)
'Accessのデータベースファイルを開く
db.OpenCurrentDatabase dbFPathAry(dbFPathAryCnt)
'クエリの数分繰り返すFor文
For Each qdf In db.CurrentDb.QueryDefs
If InStr(qdf.Name, "~") = 0 Then
'クエリ名に「~」が含まれていない場合
'A列のセルに項番の値を出力する
ws.Range("A" & cellPosCnt).Value = cellPosCnt - bgnRowPos + 1
'B列のセルにAccessのデータベースファイルのフォルダパスを出力する
ws.Range("B" & cellPosCnt).Value = fso.GetFile(dbFPathAry(dbFPathAryCnt)).ParentFolder.path
'C列のセルにAccessのデータベースファイル名を出力する
ws.Range("C" & cellPosCnt).Value = fso.GetFile(dbFPathAry(dbFPathAryCnt)).Name
'D列のセルにクエリ名を出力する
ws.Range("D" & cellPosCnt).Value = qdf.Name
'E列のセルにクエリのSQL文を出力する
ws.Range("E" & cellPosCnt).Value = qdf.sql
cellPosCnt = cellPosCnt + 1
End If
DoEvents
Next qdf
'Accessのデータベースファイルを閉じる
db.CloseCurrentDatabase
Next dbFPathAryCnt
コードの説明
以上のコードは、Accessのデータベースファイルを開いてクエリ情報を取得し、クエリの名称やクエリのSQL文などをシートの出力する処理のコードです。
コードの詳細
39行目のコードは、Accessのデータベースファイルの数分処理を繰り返すFor文です。
42行目のコードでは、OpenCurrentDatabaseメソッドの引数にAccessのデータベースファイルのフルパスを指定して実行すると、Accessのデータベースファイルを開きます。
Accessのデータベースファイルにあるクエリの情報はOpenCurrentDatabaseメソッドを使ってAccessのデータベースファイルを開くことで取得することができます。
45行目のコードは、クエリの数分繰り返すFor文です。
47行目のコードは、クエリの名前に「~」の文字が含まれるかを判定するIFで、もし含まれていなかったら52行目以降の処理を行います。
名前に「~」の文字が含まれているクエリの例は下の通りです。
ここでなぜ「~」の文字の有無を判定しているのかについては説明が長くなるので、後述する「qdfのNameプロパティの値に対して「~」の文字の有無を判定している理由」で詳しくご説明します。
52行目のコードでは、A列のセルに項番の値を出力します。
55行目のコードでは、B列のセルにAccessのデータベースファイルのフォルダパスを出力します。
58行目のコードでは、C列のセルにAccessのデータベースファイル名を出力します。
61行目のコードでは、D列のセルにクエリ名を出力します。
64行目のコードでは、E列のセルにクエリのSQL文を出力します。
74行目のコードでは、開いていたAccessのデータベースファイルを閉じます。
qdfのNameプロパティの値に対して「~」の文字の有無を判定している理由
47行目のコードでqdfのNameプロパティの値に対して「~」の文字の有無を判定していることについては「注目すべきコード⑦」で触れました。
If InStr(qdf.Name, "~") = 0 Then
47行目で、なぜqdfのNameプロパティの値に対して「~」の文字の有無を判定しているのかというと、手動で追加したクエリのSQL文だけを取得したいからです。
というのも、qdfのNameプロパティは手動で追加したクエリのSQL文だけでなく、手動で追加したクエリ以外のSQL文も取得してしまうんです。
そこで、「~」の文字の有無を判定することで手動で追加したクエリのSQL文だけを取得するようにしています。
ただしこの判定では不十分で現在の課題となっています。課題については後述します。
【課題】qdfのNameプロパティの値に対して「~」の文字の有無の判定では不十分
実はqdfのNameプロパティの値に対して「~」の文字の有無の判定では不十分です。
ではなぜ「~」の文字の有無の判定では不十分かというと、手動で追加するクエリの名前には「~」を使うことができるからです。
そもそも、手動で追加したクエリか手動で追加したクエリ以外かを判定する情報がないため、代替案として「~」の文字の有無を判定する方法で仮決めしました。
(正確には、情報がないのではなく、見つけられなかったが正しいかもしれません)
例えば「~Q_社員」のようにクエリに「~」の文字を名前に付けられるため、「~Q_社員」と名前を付けられたクエリは47行目の条件判定でスルーされてしまいます。
なので、クエリの名前には「~Q_社員」のような「~」の文字を使わないようするといったルールを決めることが必要となってきます。
動作確認
「ExcelファイルとAccessのデータベースファイルの例」をご覧ください。
【注意】参照設定が必要です
一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。
参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。
- Microsoft Office 15.0 Access databese engine Object Library(ACEDAO.DLL)
なぜ必要かというと、Excelのマクロのコードの12行目の「DAO.QueryDef」というオブジェクトが「ACEDAO.DLL」というファイルを参照するからです。
Dim qdf As DAO.QueryDef 'クエリの情報用変数
この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。
ここでは「ACEDAO.DLL」は何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「DAO.QueryDef」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。
最後に
この記事では、AccessのデータベースファイルにあるクエリのSQL文をすべて取得する方法についてご説明しました。
AccessのデータベースファイルにあるクエリのSQL文をすべて取得して確認したいときは本記事を参考にしてみてくださいね。
※今回のマクロは課題が一つ残っています。詳しくは「【課題】qdfのNameプロパティの値に対して「~」の文字の有無の判定では不十分」をご覧頂き、マクロのコードをご参照ください。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。
