examples/07-Convert-Format.ps1
|
# 07 - Copy a whole database into a new file of another Access format: tables, column types, keys and rows. # Here a modern .accdb becomes an Access 2000 .mdb that an old application can open - or the other way round. # ./07-Convert-Format.ps1 -Source ./Sales.accdb -Target ./Sales2000.mdb -Format V2000 param([string] $Source = (Join-Path (Join-Path ([IO.Path]::GetTempPath()) 'kAccess-examples') 'Sales.accdb'), [string] $Target = (Join-Path (Join-Path ([IO.Path]::GetTempPath()) 'kAccess-examples') 'Sales2000.mdb'), [ValidateSet('V2000', 'V2003', 'V2007', 'V2010', 'V2016', 'V2019')] [string] $Format = 'V2000') Import-Module kAccess Remove-Item $Target -ErrorAction Ignore New-kAccessDatabase $Target -Format $Format | Out-Null foreach ($Table in Get-kAccessTable $Source) { $Columns = @($Table.Columns | ForEach-Object { $Column = @{ Name = $_.Name; Type = $_.Type } if ($_.Type -eq 'TEXT') { $Column.Length = [int] ($_.Length / 2) } # Length is in bytes, TEXT wants characters $Column }) $Indexes = @() if ($Table.Indexes -contains 'PrimaryKey') { $Indexes += @{ Name = 'PrimaryKey'; Columns = @($Table.Columns[0].Name); PrimaryKey = $true } } New-kAccessTable $Target $Table.Name -Column $Columns -Index $Indexes $Names = @($Table.Columns.Name) Get-kAccessRow $Source $Table.Name | ForEach-Object { $Row = @{}; foreach ($Name in $Names) { if ($null -ne $_.$Name) { $Row[$Name] = $_.$Name } }; $Row } | Add-kAccessRow $Target $Table.Name "{0,-12} {1,5} rows copied" -f $Table.Name, $Table.RowCount } Get-kAccessTable $Target | Format-Table Name, RowCount |