Dump tables, relationships and queries from Access database file

dump-access.ps1

# 実行には ACE14.0 以降が必要
# Microsoft Access Database Engine 2010 Redistributable
# http://www.microsoft.com/en-us/download/details.aspx?id=13255


Param (
    [Parameter()]
    [String] $DirPath = ".",
    [String] $FileName = "*",
    [ValidateSet("Table", "Query", "SQL", "Relation")][String] $Type = "Table",
    [Int] $MaxRecords = -1
)


function Write-Line($line, $file) {
    $line | Out-File -FilePath $file -Encoding UTF8 -Append
}


function Export-Table($accfile, $outfile) {

    $dbe = New-Object -ComObject DAO.DBEngine.120
    $db = $dbe.OpenDatabase($accfile)

    $tables = $db.TableDefs
    foreach ($table in $tables) {

        $name = $table.Name

        if (!$name.startsWith("MSys")) {

            $rs = $db.OpenRecordset($name)
            $nf = $rs.Fields.Count

            # 最大出力件数
            if ($MaxRecords -lt 0) {
                $max = $rs.RecordCount
            }
            else {
                $max = $MaxRecords
            }

            # テーブル名
            Write-Line ("[" + $name + "]") $outfile

            # フィールド名
            $line = @()
            $rs.Fields | foreach-object {
                $line += $_.Name
            }
            Write-Line ($line -join "`t") $outfile

            # レコード
            $i = 0
            $rs.MoveFirst()
            while ($rs.EOF -eq $false -and $i -lt $max) {
                $line = @()
                $rs.Fields | foreach-object {
                    $line += $_.Value
                }
                Write-Line ($line -join "`t") $outfile
                $rs.MoveNext()
                $i++
            }

            Write-Line "" $outfile
        }
    }

    $db.Close()
}


function Export-Relation($accfile, $outfile) {

    $dbe = New-Object -ComObject DAO.DBEngine.120
    $db = $dbe.OpenDatabase($accfile)

    $relations = $db.Relations
    foreach ($relation in $relations) {

        $name = $relation.Name

        if (!$name.startsWith("MSys")) {

            # リレーションシップ名
            Write-Line ("[" + $name + "]") $outfile

            # テーブルの対応関係
            $line = $relation.Table + "`t" + $relation.ForeignTable
            Write-Line $line $outfile

            # フィールドの対応関係
            $relation.Fields | foreach-object {
                $line = $_.Name + "`t" + $_.ForeignName
                Write-Line $line $outfile
            }

            Write-Line "" $outfile
        }
    }

    $db.Close()
}


function Export-SQL($accfile, $outfile) {

    $dbe = New-Object -ComObject DAO.DBEngine.120
    $db = $dbe.OpenDatabase($accfile)

    $queries = $db.QueryDefs
    foreach ($query in $queries) {

        $name = $query.Name

        if (!$name.StartsWith("~")) {

            # クエリ名
            Write-Line ("[" + $name + "]") $outfile

            # SQL
            Write-Line $query.SQL $outfile

            Write-Line "" $outfile
        }
    }

    $db.Close()
}


# Get-ChildItem で Include を使う場合
# Path の最後にアスタリスクをつける必要がある
$path = Join-Path $DirPath "*"
$name = $FileName + ".accdb"

# パターンにマッチする Access ファイルを順番に処理
$files = Get-ChildItem -Path $path -File -Include $name
foreach ($file in $files) {

    $accfile = $file.FullName
    $outfile = $file.DirectoryName + "\" + $file.BaseName + "_" + $Type + ".txt"

    Write-Host $accfile

    if (Test-Path $outfile) {
        Remove-Item -LiteralPath $outfile
    }

    switch ($Type) {
        "Table" { Export-Table $accfile $outfile }
        "Query" { Export-SQL $accfile $outfile }
        "SQL" { Export-SQL $accfile $outfile }
        "Relation" { Export-Relation $accfile $outfile }
    }
}