examples/04-Maintain-Data.ps1
|
# 04 - Day-to-day data work on an existing database: change many rows, delete old ones, archive a table under # a new name and drop what is not needed. Every change goes straight into the file - no Access, no driver. # Run 03-Sales-Database.ps1 first (it makes Sales.accdb), or pass your own -Database. param([string] $Database = (Join-Path (Join-Path ([IO.Path]::GetTempPath()) 'kAccess-examples') 'Sales.accdb')) Import-Module kAccess if (-not (Test-Path $Database)) { throw "Run 03-Sales-Database.ps1 first, or give -Database" } # Mark every order older than 180 days as paid - one pipeline, one index update. $Cutoff = (Get-Date).Date.AddDays(-180) $Old = @(Get-kAccessRow $Database Orders | Where-Object { $_.OrderDate -lt $Cutoff -and -not $_.Paid }) $Old | Set-kAccessRow $Database Orders -Value @{ Paid = $true } "$($Old.Count) old orders marked as paid" # Give every Swedish customer a new country text. Get-kAccessRow $Database Customers | Where-Object Country -eq 'Sweden' | Set-kAccessRow $Database Customers -Value @{ Country = 'Sverige' } # Delete tiny test orders. $Tiny = @(Get-kAccessRow $Database Orders | Where-Object Amount -lt 100) $Tiny | Remove-kAccessRow $Database Orders "$($Tiny.Count) orders under 100 deleted" # Keep this year's state as an archive table, then drop a table that is not needed any more. New-kAccessTable $Database Scratch -Column @{ Name = 'Note'; Type = 'TEXT' } Rename-kAccessTable $Database Scratch -NewName ScratchArchive Remove-kAccessTable $Database ScratchArchive -Confirm:$false Get-kAccessTable $Database | Format-Table Name, RowCount, Indexes |