Accessで一時テーブルを作りファイルを並べ替えをする方法

始めに

　VBAを使っていると、ファイルを操作したいことがあります。（とりわけ日付順に）ネットで検索すると実装例がいくつか紹介されていますが、コードが比較的煩雑ですが、AccessならDBに取り込んでSQLで並べ替えをすれば楽に出来るのでは無いかと思い開発しました。
　しかし、Accessには他のDBにある一時テーブルの作成方法が公式にはありません。そこでRollBack機能を使って擬似的にそうした状態を再現してみました。

実装

基本的なプロセスとしては以下の通りです。

1.テーブルを作成
2.ファイル名を取得
3.並べ替え
4.テーブルを削除

可読性と再利用性を上げるためこのプロセスを実行させるクラスを用意し、標準モジュールから呼び出すことにします。
なお、標準モジュールへ返し値はCollection型とします。

• .Countプロパティが使える
• クラス内でReDim Presaeveを使うよりも可読性が上がる等のメリットが有ります。

ソースコード

クラス

クラス内の標準コードは以下の通りです。

FileSorting.cls

Option Compare Database
Option Explicit

Private file_path_  As String
Private file_exe_   As String
Private all_files   As Long

'フォルダ名を指定する
Public Property Let file_path(ByVal strPath As String)
    Dim strLastword As String
    
    'フォルダ指定にスラッシュが無い場合に自動的に足す
    strLastword = Right(strPath, 1)
    If strLastword <> "\" Then
        strPath = strPath & "\"
    End If
    
    file_path_ = strPath

End Property

'拡張子を指定する
Public Property Let file_exe(ByVal strexe As String)
    file_exe_ = strexe
End Property

'/////////////////////////////////////////////////////
'_ メインルーチン'
'   引数　 -> File_Lifting  ASCまたはDESCと入力（標準はASC)
'   戻り値 -> Collection型
'//////////////////////////////////////////////////

Public Function File_list_up(Optional ByVal File_Lifting As String = "ASC") As Collection
On Error GoTo error
    Dim db          As DAO.Database
    Dim ws          As DAO.Workspace
    Dim tb          As DAO.TableDef
    Dim rs          As DAO.Recordset
    Dim buf         As String
    Dim vSQL        As String
    
    'Collectionの宣言
    Set File_list_up = New Collection

    'ソート引数の検査
    If File_Lifting <> "ASC" And File_Lifting <> "DESC" Then
        MsgBox "ソート関数に不正な値が入力されました", vbExclamation
        Exit Function
    End If
    
    'フォルダの検査
    If file_path_ = "" Then
        MsgBox "フォルダが指定されていません", vbExclamation
        Exit Function
    Else
        If Dir(file_path_) = "" Then
            MsgBox "フォルダが存在していません", vbExclamation
            Exit Function
        End If
    End If
    
    'DBの宣言
    Set db = CurrentDb
    Set ws = DBEngine.Workspaces(0)

    'テーブルを作成
    ws.BeginTrans
    Set tb = db.CreateTableDef("wtbl_Files")
    tb.Fields.Append tb.CreateField("FOL_FILES", dbText)
   'ファイルサイズで入替を行うときには長整数型
    tb.Fields.Append tb.CreateField("FILE_DAYS", dbDate)
    db.TableDefs.Append tb
    
    'ファイルを取得
    Set rs = db.OpenRecordset("wtbl_Files")
    buf = Dir(file_path_ & "*" & file_exe_)
    Do While buf <> ""
        With rs
            .AddNew
            !FOL_FILES = buf
            !File_Days = FileDateTime(file_path_ & buf)
            .Update
        End With
    buf = Dir()
    Loop
    rs.Close
    
    '並べ替え
　vSQL = "SELECT * FROM wtbl_Files ORDER by "
    vSQL = vSQL & "FILE_DAYS " & File_Lifting & ", FOL_FILES " & File_Lifting
  '今回の場合は、日時＞同じであればファイル名で入替を行っている
    Set rs = db.OpenRecordset(vSQL)
    rs.MoveFirst
    Do Until rs.EOF
        File_list_up.Add file_path_ & rs!FOL_FILES
        rs.MoveNext
    Loop
    rs.Close

error:
    ws.Rollback
    db.Close
    ws.Close
End Function

標準モジュールへの実装例

listUp.Bas

Private Sub test_list_up()
    Dim IMG       As FileSorting
    Dim file_img   As Collection
    Dim var         As Variant
    Set IMG = New FileSorting
    
    'フォルダを設定
        IMG.file_path = "D:\"
    '取得する拡張子を設定
        IMG.file_exe = ".jpg"
	'↑は設定しなくても良い
    'ファイルを取得
        Set file_img = IMG.File_list_up
        
    '要素数の出力表示
    Debug.Print "ファイル数:" & file_img.COUNT
    
    If file_img.COUNT = 0 Then Exit Sub
    
    For Each var In file_img
        Debug.Print "ファイル名:" & var
    Next
    
End Sub

参考サイト

Accessで一時テーブル(create temporary table (view))