PowerShellããMS Accessãžã®æ¥ç¶ã¯äœçš®é¡ãæ¹æ³ããã
ã¿ã€ãã«ã®ãšãããªã®ã§ãããå
æ¥ã®ADOXã®ä»¶ä»¥æ¥ãçŽåŸããããªããŠèª¿ã¹ãŠãŸããã
ADOXã®ä»¶ã¯çµè«ãããããšçŽæ¥äœ¿çšã§ã¯ãªããŠCOMã.Netã®ã©ãããŒçµç±ã§åŒã³åºãããã«ãªãããã§ããããèãããRCWãšãããã€ãtlbimp.exeãªã©ã§äœããªããšãã¡ã§ããã ãšããããšã¯ãADOXã¯dllãšãããCOMã®ç»é²ãæ£åžžã§ããã°çµå±ã¯COMãšããŠåŒã³åºãã.Netã§ãRCWçµç±ã§åŒã³åºãå¿
èŠãããã®ã§èå³ã倱ã£ãŠããŸããŸããã
ãŸããtlbimp.exeã¯VisualStudioã€ã³ã¹ããŒã«æã«å°å
¥ãããã®ã§ããŠã³ããŒãããæéãåŸ
ãŠãŸããã§ããã ãã®ãã¡ãããŸãã
ä»åã¯ãã£ããAccessã©ã³ã¿ã€ã ãå°å
¥ããã®ã§ããŒãã«ãäœã£ãŠããŒã¿ã远å ãããåç
§ãããããŠã¿ãŸãã
COMçµç±ã§ADODB.Connectionã䜿ã
ïŒïŒïŒïŒå¹Žã«ããªã£ãŠãVBAãVBS以å€ããããã䜿ããšã¯æã£ãŠãã¿ãŸããã§ãããã䜿ãããšãã§ããŸãã
$db = Join-Path (pwd).Path "test.accdb" try { $con = New-Object -ComObject "ADODB.Connection" $con.Open("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$db;") ## SQLã§ããŒã¿ç»é²ãã $con.Execute("create table PSUser (id number not null, name text not null)") $con.Execute("insert into PSUser values (1, 'm0t0k1')") | Out-Null $con.Execute("insert into PSUser values (2, 'm0t0k1x2')") | Out-Null $con.Execute("insert into PSUser values (3, 'm0t0k1w')") | Out-Null ## åé€ãããšãã«ã€ãã ## $con.Execute("delete from PSUser") | Out-Null ## ããŒã¿ãåç
§ãã $rs = New-Object -ComObject "ADODB.Recordset" $rs.Open("select * from PSUser", $con, 3, 3) if($rs.EOF -eq $false){ $rs.MoveFirst() while($rs.EOF -ne $true){ Write-Host ("ããŒã¿ -> {0}`t{1}" -f $rs.Fields.Item("id").Value, $rs.Fields.Item("name").Value) $rs.MoveNext() } } else { "ããŒã¿ã1ä»¶ããããŸããã" } $rs.Close() $con.Close() } catch { $Error } finally { [System.Runtime.InteropServices.Marshal]::ReleaseComObject($rs) | Out-Null [System.Runtime.InteropServices.Marshal]::ReleaseComObject($con) | Out-Null }
ã»ãšãã©VBAãVBSãšåãã§ããã
ãã¶ãããµã©ãªãŒãã³æä»£ã¯ããã䜿ã£ãŠãããããªæ°ãããŸããããå¿ããŠããŸããŸãããã ãã§ã«PowerShellã«ã¯åå空éã§èªã¿èŸŒãŸããŠããã®ã§ãç¹ã«ãã«ããããšãªãNew-Objectããããšãã§ããŸãã ãªããæŽæ°ç³»ã®SQLã¯ExecuteNonQueryã¡ãœããã䜿ããŸãããããŸããä»åã¯ãã©ã³ã¶ã¯ã·ã§ã³ããŸã£ããèæ
®ããŠããŸããããMS Accessã§ãäžæ¬ã§æŽæ°ãããšãã¯ãã©ã³ã¶ã¯ã·ã§ã³ã䜿ã£ãã»ããåŠçéåºŠãæ©ãã®ã§äœ¿ã£ãã»ããããã§ãããã
$db = Join-Path (pwd).Path "test.accdb" try{ $db = New-Object System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$db;") $cmd = New-Object System.Data.OleDb.OleDbCommand("select * from PSUser", $db) $db.Open() $reader = $cmd.ExecuteReader() while($reader.Read()){ Write-Host ("{0}`t{1}" -f $reader[0], $reader[1]) } }catch{ $Error }finally{ $reader.Close() }
COMã䜿ããªãã®ã§ãããŸããªããžã§ã¯ãã®è§£æŸã«æ°ãã€ãã£ãŠããŸãããPowerShellã§ãusingã£ãŠäœ¿ãããããã®ããªâŠ
ãã¡ããã»ãŒOleDBãšåãã§ããäœ¿ãæ¹ãã»ãŒåãã§ãã å¯äžéãã®ããæ¥ç¶æååããããã€ãã§ã¯ãªããã©ã€ããŒã«ãªã£ãŠãããšããã§ãããã
$db = Join-Path (pwd).Path "test.accdb" try{ $db = New-Object System.Data.Odbc.OdbcConnection("Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=$db;") $cmd = New-Object System.Data.Odbc.OdbcCommand("select * from PSUser", $db) $db.Open() $reader = $cmd.ExecuteReader() while($reader.Read()){ Write-Host ("{0}`t{1}" -f $reader[0], $reader[1]) } }catch{ $Error }finally{ $reader.Close() }
PowerShellã§ã¯.Netãªã®ã§ç¡çã«COMã䜿ãå¿
èŠã¯ãããŸãããExcelãªã©ã®æäœã§ã©ãããŠãããšããããã¯éå»ã«äœ¿ã£ãŠããã®ã§ããªã©ããããããããŸããã®ã§ãããããšãã«éå®ããã»ãããããããããŸãããé¢åã§ããããã
å
æ¥æçš¿ããã¢ã³ãããŒãžãdllã«ã€ããŠããã©ãããŠããšããããšããªããã°.Netã䜿ã£ãã»ãã粟ç¥è¡çäžããããããããŸããã
ã¯ãããã Windows PowerShell
èè
ïŒæå åºèŠ
äŸ¡æ ŒïŒ500å
楜倩ããã¯ã¹ã§è©³çްãèŠã