Categories

Access: Stored Procedure

Next stop on our trip around Access functionality is the stored procedure.  An SP is a piece of code that we have defined, and saved in the database. It may take parameters or may just be a straight select statement.

As with any Access object we have to start with creation

001
002
003
004
005
006
007
008
009
010
011
012
013
014
function New-AccessStoredProcedure {
# .ExternalHelp Maml-AccessFunctions.XML
[CmdletBinding()]
param (
    [System.Data.OleDb.OleDbConnection]$connection,
    [string]$name,
    [string]$proc
)
    $sql = "CREATE PROCEDURE $name AS $proc"
    Write-Debug $sql
   
    $cmd = New-Object System.Data.OleDb.OleDbCommand($sql, $connection)
    $cmd.ExecuteNonQuery()   
}

 

As before we’ll build up the function.  We start with a simple procedure where we give the connection, a procedure name and the SQL statement for the procedure.

The function is used like this:

PS> Import-Module accessfunctions -Force
PS> $db = Open-AccessDatabase -name test03.mdb -path c:\test
PS> New-AccessStoredProcedure -connection $db -name "proc1" -proc "select * from test1"
0
PS> Close-AccessDatabase $db

Next time we will see how to use a stored procedure in our PowerShell code

 

Technorati Tags: ,,

Leave a Reply