PowerShellでADO経由でMS-Accessのデータを扱う
PowerShellを使ってAccessに接続する情報が意外と少なかったので書き留めておきます。基本的なアクセスの仕方から、クエリの呼び出し方、Excelへのデータの吐き出し方などを書いておきます。ご参考になれば。
MS-AccessはSQL文をガリガリ書かなくても、データベースにアクセスできるのが楽でSE現場にいた頃良く使っていました。今回はPowerShellを使ってAccess処理を自動化してみよう、という試み。
目次
PowerShellからアクセスへのコネクションを作る
2018年にもなってmdb形式を使ってる人もそういないんじゃないかと思うので、accdbファイルを前提に話を進めます。この場合、ADOのConnectionオブジェクトを使ってコネクションを作ります。
ADODB.ConnectionでComObjectを作ります。
|
1 |
$con = New-Object -ComObject ADODB.Connection |
ConnectionオブジェクトはConnectionStringが必要なので、対象のアクセスファイルへの接続文字列を設定してやります。
|
1 2 3 |
$AccessFile = "利用状況.accdb"
$AccessPath = Join-Path $Path $AccessFile
$connectionString = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = $AccessPath" |
このConnectionStringを使ってコネクションをオープンします。
|
1 |
$con.Open($connectionString) |
一応、ここまででコネクションは完了です
参考）
ADO Connectionオブジェクト MSDN
データを呼び出す
Accessでは、Recordsetオブジェクト単位でデータを呼び出します。
書籍の貸出簿がAccessに入っていて、貸出状況を読み込むとしてみましょう。
|
1 2 3 4 5 |
$Table= "貸出簿"
$sql ="SELECT * FROM $Table where 貸出日 is not null;"
$rs = New-Object -ComObject ADODB.Recordset
# レコードセットをオープン
$rs.Open($sql, $con, $adOpenStatic, $adLockOptimistic) |
Recordsetには返ってきたレコードのなかでどこを参照しているかっていうCurrentRecord（現在位置）の概念があります。それをどう参照するかというのでいくつか種類があります。
カーソルの種類
・静的 呼び出した時点でのデータのみ。レポートなどに使う。
・動的 新規追加、変更、および削除を反映する。
ロックの種類
・レコードを読み込んでる間、他のユーザが読み込めるか・更新できるかなどの動作が変わる
このあたりの指定はVBの定数で行うのですが、外部で使おうとすると定数が設定されてないので、指定する必要があるらしいです。今回は下記２つを指定します。
|
1 2 |
$adOpenStatic = 3
$adLockOptimistic = 3 |
ADOのRecordsetオブジェクトのパラメータとして指定できそうなのですが、試せてないので割愛。
ここまでで貸出簿を読み込めたので、Title,所属,名前を抽出して画面に表示してみます。
|
1 2 3 4 5 |
$rs.MoveFirst()
while($rs.EOF -eq $False)
{
Write-Host $rs.Fields.Item("Title").Value $rs.Fields.Item("所属").Value $rs.Fields.Item("名前").Value
$rs.MoveNext()
} |
処理が終わったら後片付けをしましょう。
|
1 2 3 4 |
$rs.Close()
$con.Close()
$rs = $null
$con = $null |
パラメータを使ってクエリを参照する
貸出簿からある一定期間に借りている人数の推移を見ることにするとします。日毎に借りられているか推移をだすというのを例にします。貸出簿には、貸出日と返却日があります。
日付をパラメータで外から与えてやるとすると、ある日付に借りられていた一覧をだすには下記の条件で抽出すれば出せます。
貸出日<=[日付]
返却日>=[日付] or 返却日 Is Null
SQLで特定時点の貸出人数を出すクエリを組むのは簡単なのですが、推移を出そうとすると、日付を指定してLoopで回す必要があります。
Loopは次の節で説明することにして、クエリのパラメータ付呼び出しを先に説明します。
[日付]をパラメータで外部から与えるクエリを作って、外からパラメータを与えてPowershell上で回してみましょう。
クエリを直接呼び出す場合は、ADOのCommandオブジェクトを使います。
具体的には下記の３つでパラメータ付きクエリを呼び出せます。
・CommandTypeはテーブルにする。クエリはオブジェクト的にはテーブルと同じなので、テーブルとしてデータを読み出してやれば、Recordsetが取り出せます。
・CommandTextプロパティにクエリ名を入れる。
・Commande.Parametersプロパティに、[日付]を代入する。
こんな感じ
|
1 2 3 4 5 6 7 8 9 10 11 |
#CommandTypeEnum
$adCmdText =1 #SQL
$adCmdTable =2 #テーブル
$adDate =7
$QueryName ="指定日別貸出状況"
$cmd = New-Object -ComObject ADODB.Command
$cmd.ActiveConnection = $con
$cmd.CommandType = $adCmdTable
$cmd.CommandText = $QueryName
$cmd.Prepared = $true
$param = $cmd.CreateParameter("日付", $adDate, $adParamInput, 255)
$cmd.Parameters.Append($param) |
なんとなくわかってもらえれば…（汗）
Excelと連携する
次にLoopで回しつつExcelに集計表を出す、ということをしてみます。
PowerShellでExcelを扱うことについては、別記事を参照ください。
貼り付ける先のExcelにAccessクエリと同名のシートを用意して呼び出します。
（ヘッダが設定済みの集計表表示用のフォーマットです。）
|
1 2 3 4 5 |
$ExcelFile = "利用状況集計.xlsx"
$ExcelPath= Join-Path $Path $ExcelFile $Excel = New-Object -ComObject Excel.Application
$Book= $Excel.Workbooks.Open($ExcelPath)
$集計貼り付けシート =$Book.Worksheets.Item($集計表) |
ExcelのRangeオブジェクトのCopyFromRecordsetメソッドを呼び出すと、そのRangeにRecordsetの内容が貼り付けできます。
２０１７年度の毎月月初日について、１２ヶ月間出力しようとすると、こんな感じ。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
$年度 =2017
$集計基準日 =Get-Date -Date "$年度/4/1"
$使用行数=1
for($m =0 ;$m -le 11 ;$m++)
{
$cmd.Parameters.Item("日付") =$集計基準日.AddMonths($m).ToShortDateString()
$rs = $cmd.Execute()
$rs.MoveFirst()
$集計表新規行 = $使用行数+1
if($集計表新規行-lt 2){$集計表新規行=2} #ヘッダ列が消えないための予防
$貼付対象セル =$集計貼り付けシート.Cells.item($集計表新規行,1)
$使用行数+=$貼付対象セル.CopyFromRecordset($rs) } #For文ここまで |
貼り付けるとRecord数が返ってくるので、貼り付ける場所をコントロールする為に変数に格納してLoopを回してます。
いろんなデータベースが無料で使えるようになってきてるので、Accessを使う人も減ってきてるかなぁ、とは思いますがExcel,Access連携みたいなOfficeアプリ横断作業を.Netで自動化できるのはメリットかなぁと思いました。
RPAが大流行ですが、Windowsの中に閉じた自動化ならPowerShellで充分なんじゃないかと思いますねー。基本無料ですし。
accdbじゃなくて、mdbを扱いたいというレガシー資産をお持ちの方はこちらを参照してみてください。
