PowerShellでmdb,accdbファイル操作(ADO)

PowerShellのスクリプトからADO経由でMicrosoft Accessのデータベース(mdb,accdbファイル）を操作します。実行環境は、以下です。

• Windowsのバージョン　Windows10
• PowerShellは、64ビットまたは、32ビット(x86)用を使用

下記例は、変数$imdb_accdbが0の場合はmdbとして、1の場合はaccdbとして動作します。プログラムと同じフォルダ内にデータベースファイルが存在しない場合は作成されます。ドライバーは、使用環境にあわせて選択してください。

PowerShellのスクリプトによるmdb,accdbファイル使用例（ADO)

# mdb, aacdbファイル操作例

$imdb_accdb = 1              # 0:mdb 1:accdb
$stblName = "tbl7"           # テーブル名

if ($imdb_accdb -eq 0){
   $sdbfile = "mdbtest.mdb"
}else{
   $sdbfile = "mdbtest.accdb"
}
# ドライバー
# Jet.OLEDB.4.0 32bit, mdbのみ
# $sconnStr = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = "

 $sconnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
# $sconnStr = "Provider=Microsoft.ACE.OLEDB.16.0;Data Source="

$sdbf = Split-Path $MyInvocation.MyCommand.Path -Parent
$sdbf = Join-Path $sdbf $sdbfile
$sconnStr = $sconnStr + $sdbf

$Catalog = New-Object -ComObject "ADOX.Catalog"

if ((Test-Path $sdbf) -eq $false){
   $odb = $Catalog.Create($sconnStr)     # mdb,accdbファイル無しの場合は、作成
   $odb.close()
}

$conn = New-Object -ComObject "ADODB.Connection"
$recs = New-Object -ComObject "ADODB.Recordset"
$conn.Open($sconnStr)
$Catalog.ActiveConnection = $conn

$itblf = -1
$sa = ""
foreach($objTbl in $Catalog.Tables){   # テーブル一覧、テーブル存在チェック
   if ($objTbl.Type -eq "TABLE") {
      if ($objTbl.Name -eq $stblName){
         $itblf = 1
      }
      $sa += $objTbl.Name + ","
   }
}
write-output ("テーブル一覧:" + $sa)

if ($itblf -eq -1){           # テーブル新規作成
   $sSQL = "CREATE TABLE " + $stblName + " (" +
          "id int PRIMARY KEY," +
          "tda datetime," +
          "cnote char(20))"
   $ir = $conn.Execute($sSQL)
}

$sSQL = "SELECT count(*) FROM " + $stblName
$ir = $recs.Open($sSQL, $conn)         # レコード数
$ik = $recs.Fields[0].value
$recs.Close()
#Write-Output $ik

$id = 0
if ($ik -gt 0){
   $sSQL = "SELECT max(id) FROM " + $stblName
   $recs.Open($sSQL, $conn)
   $id = $recs.Fields[0].value
   $recs.Close()
}
#write-output $id
$id += 1
$sdt = $(Get-Date).ToString("yyyy/MM/dd HH:mm:ss")
$sSQL = "INSERT INTO " + $stblName + "(id, tda, cnote) VALUES (" +
       [string]$id + ",'" + $sdt + "'," +
       "'日本ﾆﾎﾝ123abc①Ⅱ')"
$ir = $conn.Execute($sSQL)

$sSQL = "SELECT TOP 5 * FROM " + $stblName + " ORDER BY id DESC"
$ir = $recs.Open($sSQL, $conn)
write-output "項目名、型"
$ifk = $recs.Fields.Count                #項目数
#write-output "項目数:" + $ifk
$sa = ""
for($iy = 0; $iy -lt $ifk; $iy++){
   $sa += $recs.Fields[$iy].Name + ":" + [string]$recs.Fields[$iy].Type + ","
}
write-output $sa

write-output "レコード内容"
while($recs.EOF -eq $false){
   $sa = [string]$recs.Fields[0].value + "," +
         [string]$recs.Fields[1].value + "," +
         $recs.Fields[2].value
   write-output $sa
   $ir = $recs.Movenext()
}

$recs.close()
$conn.close()
   

UPDATEの例を以下に示します。項目「cnote」の内容を変更します。「$recs.Open($sSQL, $conn, $adOpenStatic, $adLockOptimistic)」の第3引数には、レコードセットのカーソルタイプを指定します。 第4引数には、レコードセットのロックタイプを指定します。

$adOpenStatic = 3
$adLockOptimistic = 3
$sSQL = "SELECT * FROM " + $stblName + " WHERE id = " + [string]$id
$ir = $recs.Open($sSQL, $conn, $adOpenStatic, $adLockOptimistic)
$ir = $recs.Update("cnote", "千葉県234dfg")     # or $ir = $recs.Update(2, "千葉県dfg456")

# または
$sSQL = "UPDATE " + $stblName + " SET cnote = '千葉県xyz567' WHERE id = " + [string]$id
$ir = $conn.Execute($sSQL)
   

DELETEの例を以下に示します。

$adOpenStatic = 3
$adLockOptimistic = 3
$sSQL = "SELECT * FROM " + $stblName + " WHERE id = " + [string]($id - 2)
$ir = $recs.Open($sSQL, $conn, $adOpenStatic, $adLockOptimistic)
$ir = $recs.Delete()

# または
$sSQL = "DELETE FROM " + $stblName + " WHERE id = " + [string]($id - 2)
$ir = $conn.Execute($sSQL)
   

テーブル削除の例を以下に示します。テーブル「tbl5」を削除します。

$sSQL = "DROP TABLE tbl5"
$ir = $conn.Execute($sSQL)