Categories

Access: Invoke Stored procedure

 

After creating a stored procedure we need to be able to run it

001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
017
018
function Invoke-AccessStoredProcedure {
# .ExternalHelp Maml-AccessFunctions.XML
[CmdletBinding()]
param (
    [System.Data.OleDb.OleDbConnection]$connection,
    [string]$name,
    [switch]$grid
)
    $sql = "EXECUTE $name "
    $cmd = New-Object System.Data.OleDb.OleDbCommand($sql, $connection)
    $reader = $cmd.ExecuteReader()
   
    $dt = New-Object System.Data.DataTable
    $dt.Load($reader)
   
    if ($grid) {$dt | Out-GridView -Title "$sql" }
    else {$dt}
}

 

All we need is the connection and the name of the procedure.  Our SQL command is EXECUTE and then we use similar code to reading the table directly. 

Technorati Tags: ,

Leave a Reply