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