Categories

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

Leave a Reply