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