Categories

12264

Access: Remove Stored Procedure

We have seen how to create and use a stored procedure – but as part of our usage patterns we need to be able to delete stored procedures as well.

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

 

Simple function to pass the name of the procedure and call a DROP ROCEDURE statement.

 

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: ,

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: ,,

View Access table definitions part 2

 

Following on from the last post we wanted to be able to look at the table definitions

001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
017
018
019
020
021
022
023
024
025
026
027
028
029
030
031
032
$datatype = DATA {
ConvertFrom-StringData -StringData @'
3 = Integer
7 = Date
130 = String
'@

}
function Get-AccessTableDefinition {
param (
    [string]$name,
    [string]$path,
    [string]$table = ""
)
    $file = Join-Path -Path $path -ChildPath $name 
    if (!(Test-Path $file)){Throw "File Does Not Exists"}

    $conn = New-Object -ComObject ADODB.Connection
    $conn.Open("Provider = Microsoft.JET.OLEDB.4.0; Data Source = $file")
    $cat = New-Object -ComObject ADOX.Catalog
    $cat.ActiveConnection = $conn

## view tables
## note user tables are of type TABLE
    if ($table) {
        $actable = $cat.Tables | where {$_.Name -eq $table}
        $actable.Columns | Format-Table Name, DefinedSize, 
        @{Name="Data Type"; Expression={$datatype["$($_.Type)"]}}  -AutoSize
    }
    else {$cat.tables | select Name, DateCreated, DateModified}
       
    $conn.Close()
}

 

We modify our function by defining a hash table to contain the data types and adding a table parameter to the function.

We test if the table parameter has been specified and if so get the particular table and dump the column information.  Note the use of the calculated expression for the column data type.  I’ve only covered the three types I know are in this table.  The others will be added later or see http://msdn.microsoft.com/en-us/library/ms675318(VS.85).aspx if you can’t wait.

The function can be used like this:

PS> Import-Module accessfunctions -Force
PS> Get-AccessTableDefinition -name test03.mdb -path c:\test

Name                                    DateCreated                             DateModified
----                                    -----------                             ------------
MSysAccessStorage                       23/11/2009 17:22:56                     23/11/2009 17:22:56
MSysACEs                                23/11/2009 17:18:33                     23/11/2009 17:18:33
MSysNameMap                             30/11/2009 10:44:44                     30/11/2009 10:44:44
MSysNavPaneGroupCategories              23/11/2009 17:22:56                     23/11/2009 17:22:56
MSysNavPaneGroups                       23/11/2009 17:22:56                     23/11/2009 17:22:56
MSysNavPaneGroupToObjects               23/11/2009 17:22:56                     23/11/2009 17:22:56
MSysNavPaneObjectIDs                    23/11/2009 17:22:58                     23/11/2009 17:22:58
MSysObjects                             23/11/2009 17:18:33                     23/11/2009 17:18:33
MSysQueries                             23/11/2009 17:18:33                     23/11/2009 17:18:33
MSysRelationships                       23/11/2009 17:18:33                     23/11/2009 17:18:33
test1                                   23/11/2009 17:22:41                     10/01/2010 15:47:10

PS> Get-AccessTableDefinition -name test03.mdb -path c:\test -table test1

Name      DefinedSize Data Type
----      ----------- ---------
DOB                 0 Date
FirstName         255 String
ID                  0 Integer
LastName          255 String

 

Technorati Tags: ,,

View Access table definitions

We’ve looked quite a bit at how we can work with Access databases but how do we investigate the structure of the database.  The first thing we need to know is the tables in our database

001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
017
018
function Get-AccessTableDefinition {
param (
    [string]$name,
    [string]$path
)
    $file = Join-Path -Path $path -ChildPath $name 
    if (!(Test-Path $file)){Throw "File Does Not Exists"}

    $conn = New-Object -ComObject ADODB.Connection
    $conn.Open("Provider = Microsoft.JET.OLEDB.4.0; Data Source = $file")
    $cat = New-Object -ComObject ADOX.Catalog
    $cat.ActiveConnection = $conn

## view tables
## note user tables are of type TABLE
    $cat.tables | select Name, DateCreated, DateModified
    $conn.Close()
}

 

The first part of the function tests that the file exists – we’ve seen this before when opening databases. Previous functions have mainly relied on SQL and ADO.NET capability.  In this case we are using ADO (the older brother of ADO.NET). We need to create a COM object for the connection and open it using the path to our database.  This will fail if we already have the database open!!  We will see how to cope with this later.  We then need to create a ADO Active X catalog representing the database. From that we can list our tables. This list includes the system and user tables.

Next stages are to put in some error handling and decide how we will display the structure of an individual table.

Technorati Tags: ,,

Getting Access table definitions

So far we have only created a single table but in a database with a number of tables we need to be able to view the table definitions.

001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
##
## connect to database
##
$conn = New-Object -ComObject ADODB.Connection
$mydb = "c:\test\test03.mdb"
$conn.Open("Provider = Microsoft.JET.OLEDB.4.0; Data Source = $mydb")
$cat = New-Object -ComObject ADOX.Catalog
$cat.ActiveConnection = $conn

## view tables
## note user tables are of type TABLE
$cat.tables | select Name, DateCreated, DateModified

## view columns
$table = $cat.Tables | where {$_.Name -eq "test1"}
$table.Columns | Format-Table  Name, DefinedSize, typeAutoSize

 

Unfortunately we can’t use the SQL methods we have been doing so far.  We need to dip into ADODB and ADOX to get this information.

MAKE SURE THAT THE DATABASE IS CLOSED BEFORE ATTEMPTING TO DO THIS

Create an ADODB connection and open the database using the JET provider and the path to your database.

We then create a ADOX catalog and set its ActiveConnection property to the connection

We have a tables collection where we can view basic table information. For an individual table we can view the table definitions

Name      DefinedSize Type
----      ----------- ----
DOB                 0    7
FirstName         255  130
ID                  0    3
LastName          255  130

 

Next we need to dig into the type definitions so we can understand the listing and see how we can dump this information for all our tables

Delete an index

We have seen how to add indexes. We also need to be able to remove them

001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
function Remove-AccessIndex {
# .ExternalHelp Maml-AccessFunctions.XML
[CmdletBinding()]
param (
    [string]$table,
    [string]$index,
    [System.Data.OleDb.OleDbConnection]$connection
)

    $sql = "DROP INDEX $index ON $table"
    Write-Debug $sql
   
    $cmd = New-Object System.Data.OleDb.OleDbCommand($sql, $connection)
    $cmd.ExecuteNonQuery()
}

 

The function accepts connection plus table and index names.   Use the function as

remove-accessindex –table test1 –connection $db –index index_name

If you need to remove multiple indexes on a table try

“index1”, “index2”, “index3” | foreach {remove-accessindex –table test1 –connection $db –index $_ }

Technorati Tags: ,,

Access Indexes Part 1

We have some data in our table – now we need to think about indexing it

001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
017
018
019
020
021
022
function New-AccessIndex {
# .ExternalHelp Maml-AccessFunctions.XML
[CmdletBinding()]
param (
    [string]$table,
    [string]$index,
    [string]$field,
    [System.Data.OleDb.OleDbConnection]$connection,
    [switch]$unique,
    [switch]$descend
)
    if ($unique) {$sql = " CREATE UNIQUE INDEX $index ON $table"}
    else {$sql = " CREATE INDEX $index ON $table"}
   
    if ($descend){$sql += " ($field DESC)"}
    else {$sql += " ($field)"}
   
    Write-Debug $sql
   
    $cmd = New-Object System.Data.OleDb.OleDbCommand($sql, $connection)
    $cmd.ExecuteNonQuery()
}

 

Our index function starts like this.  Following the usual pattern we have a couple of switches that determine how the SQL is put together and then we run the query.

The cmdletbinding enables the –debug parameter (among other things)

This needs a bit more work yet and we also need to discover how to find what indexes are available and how we can remove indexes

Technorati Tags: ,,

Creating External help

In the previous post I included a line

#  .ExternalHelp   Maml-AccessFunctions.XML 

in the Add-AccessRecord function.

I have used comment based help in the past but as James found http://blogs.technet.com/jamesone/archive/2009/07/24/powershell-on-line-help-a-change-you-should-make-for-v2-3-and-how-to-author-maml-help-files-for-powershell.aspx it does increase the length of functions and I think makes them harder to understand.

I used the InfoPath template that James supplied and saved the file as accessfunctions.xml.  A modification to the first line in the file needs to occur so that it will function as a help file.  I can see me creating a number of these files so I decided to script the change.

001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
017
018
019
param (
    [switch]$access
)
$line1 = @'
<?xml version="1.0" encoding="UTF-8"?><?mso-infoPathSolution solutionVersion="1.0.0.12" PIVersion="1.0.0.0" href="file:///C:\Users\Jamesone\Documents\windowsPowershell\PSH-Help.xsn" name="urn:schemas-microsoft-com:office:infopath:PSH-Help:" productVersion="14.0.0" ?><?mso-application progid="InfoPath.Document" versionProgid="InfoPath.Document.2"?>
<helpItems xmlns="http://msh" schema="maml"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dev="http://schemas.microsoft.com/maml/dev/2004/10" xmlns:command="http://schemas.microsoft.com/maml/dev/command/2004/10" xmlns:maml="http://schemas.microsoft.com/maml/2004/10" xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2009-07-13T15:24:29" xmlns:xd="http://schemas.microsoft.com/office/infopath/2003" xml:lang="en-gb">
'@


if ($access) {$filepath = "c:\scripts\modules\accessfunctions\accessfunctions.xml"}

if (!(Test-Path $filepath)){Throw "File Not found"}

$file = Get-Content -Path $filepath

$file[0] = $line1

$outpath = Join-Path -Path $(Split-Path -Path $filepath -Parent) -ChildPath $("Maml-" + (Split-Path -Path $filepath -Leaf))
Set-Content -Value $file -Path $outpath

 

The script starts by defining what the first line should be.  The help files seem to be sensitive to the format of this line so I put it into a here string to get the formatting right.  I know what the files are going to be going into this script so I decided to create a switch for each module that sets the path to the xml file produced by InfoPath.

A quick test that the file exists and then we read the content, change the first line and use Set-Content to write it back out.  I added a “maml-“ prefix to the file name so that the modified help file is separate to the InfoPath file.  This makes editing the help file easier.

The help file is used as normal

get-help add-accessrecord –full    etc

InfoPath is one of those products that I’ve never really looked at before.  It looks like it should have a number of uses and as it has an object model it should be possible to work with it in PowerShell.

Modifying the Add-AccessRecord function

As we discovered last time we need to alter the Add-AccessRecord function to enable us to define the fields as well as the values.

001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
017
018
019
020
021
022
023
024
025
026
027
028
029
function Add-AccessRecord {
# .ExternalHelp Maml-AccessFunctions.XML
[CmdletBinding()]
param (
    [parameter(ParameterSetName="sql")]
    [string]$sql,
   
    [System.Data.OleDb.OleDbConnection]$connection,
   
    [parameter(ParameterSetName="field")]
    [parameter(ParameterSetName="value")]
    [string]$table,

    [parameter(ParameterSetName="field")]
    [string]$fields,
   
    [parameter(ParameterSetName="field")]
    [parameter(ParameterSetName="value")]
    [string]$values      
)

    switch ($psCmdlet.ParameterSetName){
        value    {$sql = "INSERT INTO $table VALUES ($values)" } 
        field    {$sql = "INSERT INTO $table ($fields) VALUES ($values)" } 
    }
   
    $cmd = New-Object System.Data.OleDb.OleDbCommand($sql, $connection)
    $cmd.ExecuteNonQuery()
}

 

This is a simple matter of adding the $fields parameter and another parameter set.  A switch statement is used to create the correct SQL statement.  if we use the –sql parameter then that statement is passed straight through.

We now have the flexibility in this function to add records in the manner in which we best feel comfortable.  A couple of examples are provided

Add-AccessRecord -sql "INSERT INTO test1 (Firstname, lastname, DOB) VALUES ('John', 'Blue', '24/12/1981')" -connection $db

Add-AccessRecord -connection $db -table test1 -fields "Firstname, lastname, DOB" -values "'Jo', 'Green', '24/11/1981'"

I think I’m going to make the connection parameter mandatory because I keep forgetting to add it in.

The other point to notice is the

#  .ExternalHelp   Maml-AccessFunctions.XML 

statement.  This is used to link the function to an external help file.  More on that later.

Technorati Tags: ,,