Import-AccessDatabase.ps1

<#
.SYNOPSIS
    Imports all tables from a Microsoft Access database (.accdb) into SQL Server.

.DESCRIPTION
    Enumerates all non-system tables in a Microsoft Access .accdb database using ADODB COM
    objects, reads each table into a DataTable, and bulk-loads the data into SQL Server using
    the dbatools Write-DbaDbTableData cmdlet with AutoCreateTable. Each target table is dropped
    and recreated on every run, making this a full-refresh import.

    With -CreateConstraints, the script also reads primary key and foreign key definitions
    from the Access database and creates matching constraints in SQL Server. This makes the
    script suitable for Access-to-SQL-Server database migrations, not just staging imports.

    Optionally, a staleness check can be enabled with -CheckStaleness: if the source Access
    file's last modified time is older than -MaxStalenessHours, the import is skipped. This is
    useful when the Access file is deposited by an upstream process and you only want to
    import fresh data.

    Fields whose names contain 'password' are automatically excluded from the import to
    avoid transferring credential data into SQL Server.

.PREREQUISITES
    - dbatools module v2.0+ (Install-Module dbatools)
    - SQL Server 2012+ as the target instance
    - Permissions: db_owner or db_ddladmin + db_datawriter on the target database
      (the script drops and creates tables)
    - Microsoft Access Database Engine (ACE OLEDB provider) must be installed on the
      machine running this script. Download from Microsoft:
      https://www.microsoft.com/en-us/download/details.aspx?id=54920
    - The ACE provider bitness (32-bit or 64-bit) must match the PowerShell session bitness

.PARAMETER SqlInstance
    The SQL Server instance to import data into. Can be a hostname, hostname\instance,
    or hostname,port.

.PARAMETER SqlCredential
    Optional PSCredential object for SQL Server authentication. If omitted, Windows
    authentication is used via the current security context.

.PARAMETER Database
    The target database on the SQL Server instance where tables will be created.

.PARAMETER AccessFilePath
    Full path to the Microsoft Access .accdb file to import.

.PARAMETER CheckStaleness
    When specified, checks the Access file's last modified time against -MaxStalenessHours
    and skips the import if the file is too old.

.PARAMETER MaxStalenessHours
    Maximum age of the Access file in hours. Only used when -CheckStaleness is specified.
    Defaults to 12.

.PARAMETER CreateConstraints
    When specified, reads primary key and foreign key definitions from the Access database
    and creates matching constraints in SQL Server after all tables are imported. Primary
    keys are created first, then foreign keys. Foreign keys are only created when both the
    referencing and referenced tables were successfully imported.

.PARAMETER ExcludeFieldPattern
    A wildcard pattern for field names to exclude from the import. Fields matching this
    pattern are silently skipped. Defaults to '*password*'.

.USAGE NOTES
    - Every target table is dropped and recreated on each run. Without -CreateConstraints,
      tables are created as heaps suitable for staging/landing zone scenarios. With
      -CreateConstraints, primary and foreign keys are preserved for migration scenarios.
    - The ADODB COM objects used to read Access require the ACE OLEDB provider. If you
      receive "Provider cannot be found" errors, install the Access Database Engine
      redistributable matching your PowerShell bitness.
    - Large Access tables are read entirely into memory before bulk loading. For very
      large databases, ensure adequate memory on the machine running this script.
    - String column widths are dynamically sized by Write-DbaDbTableData (-UseDynamicStringLength).

.EXAMPLE
    .\Import-AccessDatabase.ps1 -SqlInstance "sqlserver01" -Database "Staging" -AccessFilePath "C:\Data\MyDatabase.accdb"
    Connects to sqlserver01 using Windows auth and imports all non-system tables from
    MyDatabase.accdb into the Staging database.

.EXAMPLE
    $cred = Get-Credential
    .\Import-AccessDatabase.ps1 -SqlInstance "sqlserver01\DEV" -SqlCredential $cred -Database "Import" -AccessFilePath "D:\Landing\App.accdb" -CheckStaleness -MaxStalenessHours 24
    Connects using SQL authentication, imports tables from App.accdb into the Import
    database, and skips the import if the file is older than 24 hours.

.EXAMPLE
    .\Import-AccessDatabase.ps1 -SqlInstance "sqlserver01" -Database "Staging" -AccessFilePath "C:\Data\MyDatabase.accdb" -CheckStaleness
    Imports tables but skips the import if the Access file is older than 12 hours (the default).

.EXAMPLE
    .\Import-AccessDatabase.ps1 -SqlInstance "sqlserver01" -Database "MigratedApp" -AccessFilePath "C:\Data\MyDatabase.accdb" -CreateConstraints
    Imports all tables with primary keys and foreign keys preserved. Suitable for migrating
    an Access database to SQL Server.

.LICENSE
    MIT License - https://opensource.org/licenses/MIT

.LINK
    https://github.com/mbentham/sql-server-scripts
#>

[CmdletBinding()]
param (
    # -- Connection parameters --
    [Parameter(Mandatory)]
    [ValidateNotNullOrEmpty()]
    [string]$SqlInstance,

    [Parameter()]
    [PSCredential]$SqlCredential,

    [Parameter(Mandatory)]
    [ValidateNotNullOrEmpty()]
    [string]$Database,

    # -- Source parameters --
    [Parameter(Mandatory)]
    [ValidateNotNullOrEmpty()]
    [ValidateScript({ Test-Path -Path $_ -PathType Leaf })]
    [string]$AccessFilePath,

    # -- Behaviour parameters --
    [switch]$CreateConstraints,

    [switch]$CheckStaleness,

    [Parameter()]
    [ValidateRange(1, 8760)]
    [int]$MaxStalenessHours = 12,

    [Parameter()]
    [ValidateNotNullOrEmpty()]
    [string]$ExcludeFieldPattern = '*password*'
)

$ErrorActionPreference = 'Stop'
Set-StrictMode -Version Latest

# ---------------------------------------------------------------------------
# ADODB COM objects for cleanup in the finally block
# ---------------------------------------------------------------------------
$accessConnection = $null
$schemaRecordset  = $null
$tableRecordset   = $null

try {
    # -------------------------------------------------------------------
    # Staleness check
    # -------------------------------------------------------------------
    if ($CheckStaleness) {
        $fileLastModified = (Get-Item -Path $AccessFilePath).LastWriteTime
        $ageHours = ((Get-Date) - $fileLastModified).TotalHours

        if ($ageHours -gt $MaxStalenessHours) {
            Write-Warning "Access file is $([math]::Round($ageHours, 1)) hours old (threshold: $MaxStalenessHours hours). Skipping import."
            return
        }

        Write-Verbose "Access file last modified: $fileLastModified ($([math]::Round($ageHours, 1)) hours ago)"
    }

    # -------------------------------------------------------------------
    # Connect to SQL Server via dbatools
    # -------------------------------------------------------------------
    Write-Verbose "Connecting to SQL Server instance: $SqlInstance"

    $connectParams = @{
        SqlInstance = $SqlInstance
    }
    if ($SqlCredential) {
        $connectParams['SqlCredential'] = $SqlCredential
    }
    $sqlConnection = Connect-DbaInstance @connectParams

    # -------------------------------------------------------------------
    # Open the Access database via ADODB
    # -------------------------------------------------------------------
    Write-Verbose "Opening Access database: $AccessFilePath"

    $accessConnection = New-Object -ComObject ADODB.Connection
    $tableRecordset   = New-Object -ComObject ADODB.Recordset

    $connectionString = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=$AccessFilePath"
    $accessConnection.Open($connectionString)

    # -------------------------------------------------------------------
    # Enumerate non-system tables (schema type 20 = adSchemaTables)
    # -------------------------------------------------------------------
    Write-Verbose 'Enumerating tables in Access database'
    $schemaRecordset = $accessConnection.OpenSchema(20)

    $cursorType = 3   # adOpenStatic
    $lockType   = 1   # adLockReadOnly

    $tableCount = 0
    $importedTables = [System.Collections.Generic.List[string]]::new()

    while (-not $schemaRecordset.EOF) {
        $tableName = $schemaRecordset.Fields.Item("TABLE_NAME").Value

        if ($tableName -like 'MSys*') {
            $schemaRecordset.MoveNext()
            continue
        }

        Write-Verbose "Processing table: $tableName"

        # ---------------------------------------------------------------
        # Read all rows from the Access table
        # ---------------------------------------------------------------
        $query = "SELECT * FROM [$tableName]"
        $tableRecordset.Open($query, $accessConnection, $cursorType, $lockType)

        $dataset = [System.Collections.Generic.List[PSCustomObject]]::new()

        while (-not $tableRecordset.EOF) {
            $record = [ordered]@{}

            foreach ($field in $tableRecordset.Fields) {
                if ($field.Name -notlike $ExcludeFieldPattern) {
                    $record[$field.Name] = $field.Value
                }
            }

            $dataset.Add([PSCustomObject]$record)
            $tableRecordset.MoveNext()
        }

        $tableRecordset.Close()

        # ---------------------------------------------------------------
        # Drop existing table and bulk-load into SQL Server
        # ---------------------------------------------------------------
        if ($dataset.Count -eq 0) {
            Write-Verbose "Skipping empty table: $tableName"
            $schemaRecordset.MoveNext()
            continue
        }

        Write-Verbose "Dropping and recreating table: $tableName ($($dataset.Count) rows)"

        Invoke-DbaQuery -SqlInstance $sqlConnection -Database $Database `
            -Query "DROP TABLE IF EXISTS [$tableName]" -EnableException

        $dataset | Write-DbaDbTableData -SqlInstance $sqlConnection -Database $Database `
            -Table $tableName -UseDynamicStringLength -AutoCreateTable -EnableException

        $importedTables.Add($tableName)
        $tableCount++
        Write-Verbose "Completed table: $tableName"

        $schemaRecordset.MoveNext()
    }

    # -------------------------------------------------------------------
    # Create primary keys and foreign keys (optional)
    # -------------------------------------------------------------------
    $pkCount = 0
    $fkCount = 0

    if ($CreateConstraints -and $tableCount -gt 0) {
        Write-Verbose 'Reading primary key definitions from Access database'
        $pkRecordset = $accessConnection.OpenSchema(28)  # adSchemaPrimaryKeys
        $primaryKeys = @{}

        while (-not $pkRecordset.EOF) {
            $pkTable   = $pkRecordset.Fields.Item("TABLE_NAME").Value
            $pkColumn  = $pkRecordset.Fields.Item("COLUMN_NAME").Value
            $pkName    = $pkRecordset.Fields.Item("PK_NAME").Value
            $pkOrdinal = $pkRecordset.Fields.Item("ORDINAL").Value

            if ($importedTables -contains $pkTable) {
                $key = "$pkTable|$pkName"
                if (-not $primaryKeys.ContainsKey($key)) {
                    $primaryKeys[$key] = @{
                        TableName = $pkTable
                        PkName    = $pkName
                        Columns   = [System.Collections.Generic.List[object]]::new()
                    }
                }
                $primaryKeys[$key].Columns.Add(@{ Name = $pkColumn; Ordinal = $pkOrdinal })
            }

            $pkRecordset.MoveNext()
        }
        try { $pkRecordset.Close() } catch { }
        [System.Runtime.InteropServices.Marshal]::ReleaseComObject($pkRecordset) | Out-Null

        foreach ($pk in $primaryKeys.Values) {
            # AutoCreateTable may create PK columns as NULLable; fix before adding constraint
            $pkColNames = ($pk.Columns | ForEach-Object { "'$($_.Name -replace "'", "''")'" }) -join ', '
            $escapedTable = $pk.TableName -replace "'", "''"

            $nullableColsSql = @"
SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '$escapedTable'
  AND COLUMN_NAME IN ($pkColNames)
  AND IS_NULLABLE = 'YES';
"@
            $nullableCols = Invoke-DbaQuery -SqlInstance $sqlConnection -Database $Database -Query $nullableColsSql

            foreach ($col in $nullableCols) {
                $typeDef = $col.DATA_TYPE
                if ($col.DATA_TYPE -in 'varchar','nvarchar','char','nchar','varbinary','binary') {
                    $len = if ($col.CHARACTER_MAXIMUM_LENGTH -eq -1) { 'MAX' } else { $col.CHARACTER_MAXIMUM_LENGTH }
                    $typeDef += "($len)"
                }
                elseif ($col.DATA_TYPE -in 'decimal','numeric') {
                    $typeDef += "($($col.NUMERIC_PRECISION),$($col.NUMERIC_SCALE))"
                }

                $alterSql = "ALTER TABLE [$($pk.TableName)] ALTER COLUMN [$($col.COLUMN_NAME)] $typeDef NOT NULL;"
                Write-Verbose "  Setting NOT NULL: [$($pk.TableName)].[$($col.COLUMN_NAME)]"
                Invoke-DbaQuery -SqlInstance $sqlConnection -Database $Database -Query $alterSql -EnableException
            }

            $sortedCols = ($pk.Columns | Sort-Object { $_.Ordinal } | ForEach-Object { "[$($_.Name)]" }) -join ', '
            $sql = "ALTER TABLE [$($pk.TableName)] ADD CONSTRAINT [$($pk.PkName)] PRIMARY KEY ($sortedCols);"

            Write-Verbose "Creating primary key: $($pk.PkName) on $($pk.TableName)"
            Invoke-DbaQuery -SqlInstance $sqlConnection -Database $Database -Query $sql -EnableException
        }
        $pkCount = $primaryKeys.Count

        Write-Verbose 'Reading foreign key definitions from Access database'
        $fkRecordset = $accessConnection.OpenSchema(27)  # adSchemaForeignKeys
        $foreignKeys = @{}

        while (-not $fkRecordset.EOF) {
            $fkTable   = $fkRecordset.Fields.Item("FK_TABLE_NAME").Value
            $fkColumn  = $fkRecordset.Fields.Item("FK_COLUMN_NAME").Value
            $fkName    = $fkRecordset.Fields.Item("FK_NAME").Value
            $pkTable   = $fkRecordset.Fields.Item("PK_TABLE_NAME").Value
            $pkColumn  = $fkRecordset.Fields.Item("PK_COLUMN_NAME").Value
            $fkOrdinal = $fkRecordset.Fields.Item("ORDINAL").Value

            # Only create FKs where both tables were imported
            if ($importedTables -contains $fkTable -and $importedTables -contains $pkTable) {
                if (-not $foreignKeys.ContainsKey($fkName)) {
                    $foreignKeys[$fkName] = @{
                        FkName      = $fkName
                        FkTableName = $fkTable
                        PkTableName = $pkTable
                        Columns     = [System.Collections.Generic.List[object]]::new()
                    }
                }
                $foreignKeys[$fkName].Columns.Add(@{
                    FkColumn = $fkColumn
                    PkColumn = $pkColumn
                    Ordinal  = $fkOrdinal
                })
            }

            $fkRecordset.MoveNext()
        }
        try { $fkRecordset.Close() } catch { }
        [System.Runtime.InteropServices.Marshal]::ReleaseComObject($fkRecordset) | Out-Null

        foreach ($fk in $foreignKeys.Values) {
            $sortedCols = $fk.Columns | Sort-Object { $_.Ordinal }
            $fkCols = ($sortedCols | ForEach-Object { "[$($_.FkColumn)]" }) -join ', '
            $pkCols = ($sortedCols | ForEach-Object { "[$($_.PkColumn)]" }) -join ', '

            $sql = "ALTER TABLE [$($fk.FkTableName)] ADD CONSTRAINT [$($fk.FkName)] FOREIGN KEY ($fkCols) REFERENCES [$($fk.PkTableName)] ($pkCols);"

            Write-Verbose "Creating foreign key: $($fk.FkName) on $($fk.FkTableName)"
            Invoke-DbaQuery -SqlInstance $sqlConnection -Database $Database -Query $sql -EnableException
        }
        $fkCount = $foreignKeys.Count

        Write-Verbose "Constraints created: $pkCount primary key(s), $fkCount foreign key(s)"
    }

    Write-Verbose "Import completed. $tableCount table(s) imported into $Database on $SqlInstance$(if ($CreateConstraints) { " ($pkCount PK, $fkCount FK)" })."
}
catch {
    Write-Error "Import failed: $($_.Exception.Message)"
    throw
}
finally {
    # -------------------------------------------------------------------
    # Clean up ADODB COM objects
    # -------------------------------------------------------------------
    if ($null -ne $tableRecordset) {
        try { $tableRecordset.Close() } catch { }
        [System.Runtime.InteropServices.Marshal]::ReleaseComObject($tableRecordset) | Out-Null
    }

    if ($null -ne $schemaRecordset) {
        try { $schemaRecordset.Close() } catch { }
        [System.Runtime.InteropServices.Marshal]::ReleaseComObject($schemaRecordset) | Out-Null
    }

    if ($null -ne $accessConnection -and $accessConnection.State -ne 0) {
        try { $accessConnection.Close() } catch { }
    }

    if ($null -ne $accessConnection) {
        [System.Runtime.InteropServices.Marshal]::ReleaseComObject($accessConnection) | Out-Null
    }
}