あるSEのつぶやき・改

ITやシステム開発などの技術に関する話題を、取り上げたりしています。

PowerShell, Access データベースで ADO のトランザクションを使用してデータ追加する

PowerShell

PowerShell を使って Access のデータベースにアクセスするには、実行環境や接続文字列に注意点があります。この点の詳しい内容は以下の記事を参照してください。

Access データベースに、既存の技術である ADO のトランザクションを用いて、テーブルにデータを追加するサンプルを提示します。

詳細はコメントを付けてあるので分かると思いますが、スクリプトと同じディレクトリにある Access のデータベースファイル (test.mdb or test.accdb) にアクセスし、トランザクションを用いて以下のテーブルにデータを追加します。

  • テーブル名：tblUsers
  • フィールド情報
    • USER_ID (varchar (255))
    • USER_NAME (varchar (255))

トランザクションは更新時にエラーが発生しなかったらデータ更新を確定（コミット）します。逆にエラーが発生したら元の状態に戻します（ロールバック）。これはデータベースの重要な機能です。

サンプルスクリプトは以下のようになります。

#
#  PowerShell から ADO のトランザクションを使用してデータ追加する
#


# 変数を宣言する
$adVarChar = 200
$adParamInput = 1
$con = New-Object -ComObject ADODB.Connection  

# スクリプトのカレントディレクトリを取得
$path = Split-Path $MyInvocation.MyCommand.Path -Parent

# スクリプトのカレントディレクトリのデータベースパスを作成
$path = Join-Path $path "test.mdb" # test.accdb でも可


# 拡張子によって接続文字列を切り替える

# MDB 形式の場合
if (([System.IO.Path]::GetExtension($path)) -eq ".mdb") {
  $connectionString = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = $path"

# ACCDB 形式の場合
} elseif(([System.IO.Path]::GetExtension($path)) -eq ".accdb") {
  $connectionString = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = $path"

# 上記以外は例外をスロー
} else {
  throw New-Object System.Exception("Access 拡張子エラー")
}

try {
  # 接続をオープン
  $con.Open($connectionString)

  # パラメータクエリの SQL 宣言
  $sql = "INSERT INTO tblUsers VALUES (?, ?)"

  # ADO のコマンドオブジェクトを生成
  $cmd = New-Object -ComObject ADODB.Command
  $cmd.ActiveConnection = $con
  $cmd.CommandType = 1 #adCmdText:変数を割り当てるを型エラーになる
  $cmd.CommandText = $sql
  $cmd.Prepared = $true

  # パラメータを生成して追加
  $param = $cmd.CreateParameter("USER_ID", $adVarChar, $adParamInput, 255)
  $cmd.Parameters.Append($param)

  $param = $cmd.CreateParameter("NAME", $adVarChar, $adParamInput, 255)
  $cmd.Parameters.Append($param)


  # パラメータに値を設定
  $cmd.Parameters["USER_ID"].Value = "005"
  $cmd.Parameters["NAME"].Value = "田中太郎"

  # トランザクションを開始する
  [void]$con.BeginTrans()

  # SQL を実行する
  [void]$cmd.Execute()

  # コミットしてトランザクションを終了する
  $con.CommitTrans()

} catch {
  
  # 例外が発生した場合はロールバックしてトランザクションを終了する
  $con.RollbackTrans()

} finally {
 
  # 接続を閉じる 
  $con.Close()

}

# COM オブジェクトをリリース(Excelと違ってなくても問題ないけど一応) 
[void][System.Runtime.InteropServices.Marshal]::ReleaseComObject($con)