AccessDB Module

# Functions for connecting to and working with Access databases
# Original code by Matt Wilson (May 2009)
# Modified extensively by Dale Thompson (Sept 2012)
Set-StrictMode -Version Latest

# [system.reflection.assembly]::LoadWithPartialName("ADODB")

function Open-AccessDB {
    <#
    .SYNOPSIS
    Opens an Access database
    .DESCRIPTION
    Opens an Access database
    .PARAMETER Path
    The file path to the database, which must exist
    .EXAMPLE
    $Conn = Open-AccessDB '\\amsfofil02\discipline\it\inventory(programs).accdb'
    .INPUTS
    None. You cannot pipe objects to this function.
    .OUTPUTS
    __ComObject
    .LINK
    Close-AccessRecordSet
    Test-AcessDB
    .NOTES
    Author: Dale Thompson
    LastEdit: 01/09/13
    #Requires -Version 2.0
    #>
    [CmdletBinding()][OutputType([__ComObject])]
    Param (
        [Parameter(Position = 0, Mandatory = $true)][ValidateNotNullOrEmpty()][string]$Path
    ) #param

    # Test to ensure valid path to database file was supplied
    if (-not (Test-Path $Path)) {
        throw "Invalid Access database path specified ($Path). Please supply full absolute path to database file!"
    }
    $FullPath = (Resolve-Path $Path).ProviderPath

    # TO-DO: Add check to ensure file is either MDB or ACCDB

    # Create a new ADO DB connection COM object, which will give us useful methods & properties such as "Execute"!
    $Connection = New-Object -ComObject ADODB.Connection
    try {
        $Connection.Open("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=`"$FullPath`";Persist Security Info=False;")
<#
    # Actually open the database so we can start working with its contents
    # Access 00-03 (MDB) format has a different connection string than 2007
    if ((Split-Path $Path -Leaf) -match [regex]"\.mdb$") {
        Write-Verbose "Access 2000-2003 format (MDB) detected!  Using Microsoft.Jet.OLEDB.4.0."
        $AccessConnection.Provider = "Microsoft.Jet.OLEDB.4.0"
    }

    # Here's the check for if 2007 connection is necessary
    if ((Split-Path $Path -Leaf) -match [regex]"\.accdb$") {
        Write-Verbose "Access 2007 format (ACCDB) detected!  Using Microsoft.Ace.OLEDB.12.0."
        $AccessConnection.Provider = "Microsoft.ACE.OLEDB.12.0"
    }
    $AccessConnection.Open($Path)
#>
        $Connection
    } catch { }
}

function Test-AccessDB {
    <#
    .SYNOPSIS
    Test that a database was opened properly
    .DESCRIPTION
    Test that a database was opened properly. If so, the connection object itself is sent down the pipeline.
    .PARAMETER Connection
    The connection to the database as returned by Open-AccessDB. Can be specified via the pipeline as well.
    .EXAMPLE
    Test-AccessDB $Connection
    True

    This example tests the variable $Connection to see if it was opened properly.
    .EXAMPLE
    $Connection | Test-AccessDB
    True

    This example demonstrates the variable $Connection to be specified via the pipeline.
    .INPUTS
    __ComObject
    .OUTPUTS
    __ComObject
    .LINK
    Open-AccessDB
    Close-AccessRecordSet
    .NOTES
    Author: Dale Thompson
    LastEdit: 01/09/13
    #Requires -Version 2.0
    #>
    [CmdletBinding()][OutputType([__ComObject])]
    Param (
        [Parameter(Position = 0, Mandatory = $true, ValueFromPipeline = $true)][ValidateNotNullOrEmpty()][__ComObject]$Connection
    )
    PROCESS {
        try { if ($Connection.State -eq 1) { Write-Output $Connection } } catch { }
    } # PROCESS
} # Test-AccessDB

function Open-AccessRecordSet {
    <#
    .SYNOPSIS
    Open an Access RecordSet
    .DESCRIPTION
    Returns an Access RecordSet object representing the query.
    .PARAMETER Query
    The SQL query, specified in standard MS Access SQL format
    .PARAMETER Connection
    The connection to the database as returned by Open-AccessDB
    .PARAMETER CursorType
    The CursorType attribute for the recordset. A number 0-3. Default is 3.
    .PARAMETER LockType
    The LockType attribute for the recordset. A number 1-4. Default is 3.
    .EXAMPLE
    $RecordSet = Open-AccessRecordSet 'SELECT * FROM Computers;' $Connection

    Gets all records and fields from the Computers table of the database $Connection.
    .INPUTS
    None. You cannot pipe objects to this function.
    .OUTPUTS
    __ComObject
    .LINK
    Close-AccessRecordSet
    Test-AccessRecordSet
    .NOTES
    Author: Dale Thompson
    LastEdit: 01/09/13
    #Requires -Version 2.0
    #>
    [CmdletBinding()][OutputType([__ComObject])]
    Param (
        [Parameter(Position = 0, Mandatory = $true)][ValidateScript({ $_.Length -gt 1 })][string]$Query,
        [Parameter(Position = 1, Mandatory = $true)][ValidateNotNullOrEmpty()][__ComObject]$Connection,
        [Parameter(Position = 2)][ValidateSet(0, 1, 2, 3)][int]$CursorType = 3,
        [Parameter(Position = 3)][ValidateSet(1, 2, 3, 4)][int]$LockType = 3
    ) #param

    # Create the recordset object using the ADO DB COM object
    $RecordSet = New-Object -ComObject ADODB.Recordset

    # Finally, go and get some records from the DB!
    Write-Verbose "$Query, $($Connection.Provider), $CursorType, $LockType"
    $RecordSet.Open($Query, $Connection, $CursorType, $LockType)
    return ($RecordSet)
}

function Test-AccessRecordSet {
    <#
    .SYNOPSIS
    Test a RecordSet object
    .DESCRIPTION
    Tests an object as returned by Open-AccessRecordSet to see that it is a valid RecordSet and contains at least one record.

    If the object passes the tests it is sent down the pipeline.
    .PARAMETER RecordSet
    The RecordSet object to test
    .EXAMPLE
    Test-AccessRecordSet $RecordSet
    True

    This examples tests the recordset stored in the variable $RecordSet.
    .INPUTS
    __ComObject
    .OUTPUTS
    __ComObject
    .LINK
    Close-AccessRecordSet
    Open-AccessRecordSet
    .NOTES
    Author: Dale Thompson
    LastEdit: 01/09/13
    #Requires -Version 2.0
    #>
    [CmdletBinding()][OutputType([__ComObject])]
    Param (
        [Parameter(Position = 0, ValueFromPipeline = $true)][__ComObject]$RecordSet
    )
    PROCESS { try { if ($RecordSet.RecordCount -gt 0) { return $RecordSet } } catch { } }
} # Test-AccessRecordSet

function Get-AccessRecordSetStructure {
    <#
    .SYNOPSIS
    Get the structure of an Access RecordSet
    .DESCRIPTION
    Returns an array of PSObjects, one for each field present. Each PSObject contains the following NoteProperties:

        Name        - The Name of the field
        Attributes  - The Attributes of the field
        DefinedSize - The size of the field
        Type        - The datatype of the field. The function Convert-AccessTypeCode can be used
                      to convert from this number to a human-readable datatype

    .PARAMETER RecordSet
    The RecordSet object from which to get the structure
    .EXAMPLE
    Get-AccessRecordSetStructure $RecordSet

    Name   Attributes DefinedSize Type
    ----   ---------- ----------- ----
    ID             90           4    3
    Handle        102          32  202
    Site          118           2    2
    User          102          50  202

    This example shows the objects and properties of the recordset defined by the $RecordSet variable.
    .INPUTS
    None. You cannot pipe objects to this function.
    .OUTPUTS
    PSCustomObject[]
    .LINK
    Close-AccessRecordSet
    Open-AccessRecordSet
    Test-AccessRecordSet
    .NOTES
    Author: Dale Thompson
    LastEdit: 01/09/13
    #Requires -Version 2.0
    #>
    [CmdletBinding()]
    Param (
        [Parameter(Position = 0, Mandatory = $true)][ValidateNotNullOrEmpty()][__ComObject]$RecordSet
    ) #param
    PROCESS {
        $RecordSet | Test-AccessRecordSet | % { $_.Fields | Select-Object Name, Attributes, DefinedSize, Type }
    }
}

function Convert-AccessRecordSetToPSObject {
    <#
    .SYNOPSIS
    Converts an entire RecordSet to a set of PSCustomObjects
    .DESCRIPTION
    This Cmdlet cycles through the given RecordSet and creates a PSCustomObject for each record found. A NoteProperty is added for each field in the recordset, the Name being the field name, and the Value being the value out of that field from the recordset.
    .PARAMETER RecordSet
    The RecordSet object to convert
    .EXAMPLE
    $Computers = Convert-AccessRecordSetToPSObject $RecordSet

    Stores the names and values for each record in the recordset into NoteProperties of an array of PSCustomObjects.
    .INPUTS
    __ComObject
    .OUTPUTS
    PSCustomObject[]
    .NOTES
    Author: Dale Thompson
    LastEdit: 10/11/13
    #Requires -Version 2.0
    #>
    [CmdletBinding()][OutputType([PSCustomObject])]
    Param (
        [Parameter(Position = 0, Mandatory = $true, ValueFromPipeline = $true)][ValidateNotNullOrEmpty()][__ComObject]$RecordSet
    ) #param
    BEGIN { $fields = Get-AccessRecordSetStructure $RecordSet }
    PROCESS {
        try { $RecordSet.MoveFirst() } catch { return }
        do {
            $record = @{ }
            foreach ($field in $fields) {
                $record[$field.Name] = $RecordSet.Fields.Item($field.Name).Value
            }
            Write-Output ([PSCustomObject]$record)
            $RecordSet.MoveNext()
        } until ($RecordSet.EOF -eq $True)
    }
}

function Invoke-AccessSQLStatement {
    <#
    .SYNOPSIS
    Executes a MS Acess SQL statement
    .DESCRIPTION
    Executes a MS Acess SQL statement against a database
    .PARAMETER Query
    The SQL query, specified in standard MS Access SQL format
    .PARAMETER Connection
    The connection to the database as returned by Open-AccessDB
    .EXAMPLE
    Invoke-AccessSQLStatement 'UPDATE Phones SET Phones.Location = 1312 WHERE (Phones.ID=3);' $Connection

    This examples updates the Phones table in the database represented by the $Connection variable.
    .INPUTS
    None. You cannot pipe objects to this function.
    .OUTPUTS
    Any. It's whatever the value of the SQL statement is.
    .LINK
    Open-AccessDB
    .NOTES
    Author: Dale Thompson
    LastEdit: 01/09/13
    #Requires -Version 2.0
    #>
    [CmdletBinding()]
    Param (
        [Parameter(Position = 0, Mandatory = $true)][ValidateNotNullOrEmpty()][string]$Query,
        [Parameter(Position = 1, Mandatory = $true)][ValidateNotNullOrEmpty()][__ComObject]$Connection
    ) #param
    $Connection.Execute($Query)
}

function Convert-AccessTypeCode {
    <#
    .SYNOPSIS
    Converts an Access type code from and to a human-readable value
    .DESCRIPTION
    Every MS Access field in a recordset is a certain data type, and that type is stored in Access as a number. This Cmdlet tranlates that code to a label, and vis versa.

    Here are the possible values. You can use an item from either column and this function will return the corresponding value from the other column.

        Code  Label
        ----  --------------
        202   Text
        203   Memo
          7   Date/Time
          6   Currency
         11   Yes/No
        205   OLE Object
         17   Byte
          2   Integer
          3   Long Integer (also AutoNumber?!?)
          4   Single
          5   Double
    .PARAMETER TypeCode
    The code or label to get the corresponding value of
    .EXAMPLE
    Convert-AccessTypeCode 202
    Text

    This example demonstrates returning the label corresponding to the MS Access type 202
    .EXAMPLE
    Convert-AccessTypeCode Text
    202

    This example demonstrates returning the code corresponding to the label 'Text'
    .INPUTS
    System.String
    .OUTPUTS
    System.String
    .NOTES
    Author: Dale Thompson
    LastEdit: 01/09/13
    #Requires -Version 2.0
    #>
    [CmdletBinding()][OutputType([string])]
    Param (
        [Parameter(Position = 0, Mandatory = $true, ValueFromPipeline = $true)][ValidateNotNullOrEmpty()][string]$TypeCode
    ) #param
    BEGIN {
        # Build some lookup tables for our Access type codes so we can convert values pretty easily
        $labelLookupHash = @{ "AutoNumber" = "3"; "Text" = "202"; "Memo" = "203"; "Date/Time" = "7"; "Currency" = "6"; "Yes/No" = "11"; "OLE Object" = "205"; "Byte" = "17"; "Integer" = "2"; "Long Integer" = "3"; "Single" = "4"; "Double" = "5" }
        $codeLookupHash = @{
            "202" = "Text"; "203" = "Memo"; "7" = "Date/Time"; "6" = "Currency"; "11" = "Yes/No"; `
            "205" = "OLE Object"; "17" = "Byte"; "2" = "Integer"; "3" = "Long Integer"; "4" = "Single"; "5" = "Double"
        }
    }
    PROCESS {
        # Convert a value depending on what type of data was supplied
        if ($TypeCode -match [regex]"^\d{1,3}$") {
            $valueFound = $codeLookupHash.$TypeCode
            if ($valueFound) {
                Write-Output $valueFound
            } else { Write-Output "Unknown" }
        } else {
            $valueFound = $labelLookupHash.$TypeCode
            if ($valueFound) {
                Write-Output $valueFound
            } else { Write-Output "Unknown" }
        }
    }
}

function Close-AccessRecordSet {
    <#
    .SYNOPSIS
    Close an Access RecordSet or Database
    .DESCRIPTION
    This Cmdlet will close an Access RecordSet object or Database object.

    An alias for this Cmdlet is Close-AccessDB, as it handles both.
    .PARAMETER RecordSet
    The RecordSet or Database connection to close. An alias for this is Connection.
    .EXAMPLE
    Close-AccessRecordSet $RecordSet
    .EXAMPLE
    Close-AccessDB $Connection
    .INPUTS
    __ComObject
    .OUTPUTS
    None
    .LINK
    Open-AccessRecordSet
    Test-AccessRecordSet
    .NOTES
    Author: Dale Thompson
    LastEdit: 01/09/13
    #Requires -Version 2.0
    #>
    [CmdletBinding()]
    Param (
        [Parameter(Position = 0, Mandatory = $true, ValueFromPipeline = $true)][ValidateNotNullOrEmpty()][Alias('Connection')][__ComObject]$RecordSet
    ) #param
    try {
        $RecordSet.Close()
        [void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($RecordSet)
    } catch { }
}

Set-Alias Close-AccessDB Close-AccessRecordSet

Export-ModuleMember -Function Open-AccessDB, Test-AccessDB, Open-AccessRecordSet, Test-AccessRecordSet,
                    Get-AccessRecordSetStructure, Convert-AccessRecordSetToPSObject, Invoke-AccessSQLStatement,
                    Convert-AccessTypeCode, Close-AccessRecordSet -Alias Close-AccessDB