Powershell for Microsoft Access

ms-access.ps1

# Powershell Functions for MS Access :)
# Here are several functions that cover tasks I tend to do most often in Access (hopefully more to come soon). These are mostly  
# just wrappers around Access VBA methods, but this helps me avoid constant googling every time I need to do something in Access.
# example usage is provided at the bottom of the script


# Import CSV file into MS Access database
# office vba documentation: https://docs.microsoft.com/en-us/office/vba/api/access.docmd.transferspreadsheet

function Import-MsAccessCsv
{
    param ( 
        [Parameter(Mandatory = $True)]  [__ComObject] $Access,
        [Parameter(Mandatory = $True)]  [string] $Path,
        [Parameter(Mandatory = $True)]  [string] $TableName,
        [Parameter(Mandatory = $False)] [switch] $HasFieldNames,
        [Parameter(Mandatory = $False)] [string] $SpecificationName=$null
    )

    $transferType = 0 
    $DoCmd = $Access.DoCmd
    $DoCmd.TransferText( $transferType, $SpecificationName, $TableName, $Path, [bool]$HasFieldNames )
}



# Import Excel file into MS Access database
# office vba documentation: https://docs.microsoft.com/en-us/office/vba/api/access.docmd.transferspreadsheet

function Import-MsAccessExcel
{
    param (
        [Parameter(Mandatory = $True)]  [__ComObject] $Access,
        [Parameter(Mandatory = $True)]  [string] $Path,
        [Parameter(Mandatory = $True)]  [string] $TableName,
        [Parameter(Mandatory = $True)]  [switch] $HasFieldNames,
        [Parameter(Mandatory = $False)] [string] $Range=$null,
        [Parameter(Mandatory = $False)] 
        [ValidateSet('Current','2010','2000','1997','1995','5.0','4.0','3.0')]
        [string[]]$Version='Current'
    )

    # see https://docs.microsoft.com/en-us/office/vba/api/access.acspreadsheettype for acSpreadsheetType
    $acSpreadsheetTypes = @{
        '3.0'  = 0;
        '4.0'  = 6;
        '5.0'  = 5;
        '1995' = 5;
        '1997' = 8;
        '2000' = 8;
        '2010' = 9;
        'Current' = 10;
    }

    $transferType = 0 
    $spreadsheetType = $acSpreadsheetTypes.item( [string]$Version )
    $DoCmd = $Access.DoCmd
    $DoCmd.TransferSpreadsheet( $transferType, $spreadsheetType, $TableName, $Path, [bool]$HasFieldNames, $Range, $null )
}



# Tests whether a QueryDef exists

function Test-MsAccessQueryDef
{
    [OutputType([bool])]
    param (
        [Parameter(Mandatory = $True)]  [__ComObject] $Access,
        [Parameter(Mandatory = $True)]  [string] $QueryName
    )

    $db = $Access.CurrentDb()

    try { 
        $queryDef = $db.QueryDefs.item($QueryName)
    } catch { 
        return $false
    }
    return $true
}



# Create a new MS Access query
# office vba documentation: https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/database-createquerydef-method-dao

function New-MsAccessQuery
{
    [OutputType([__ComObject])]
    param (
        [Parameter(Mandatory = $True)]  [__ComObject] $Access,
        [Parameter(Mandatory = $True)]  [string] $QueryName,
        [Parameter(Mandatory = $False)] [string] $SQL=$null
    )

    # check whether querydef with the same name already exists
    if( Test-MsAccessQueryDef $Access $QueryName ) {
        throw "Error: A query by the name of '$QueryName' already exists."
    } elseif( $SQL -ne $null ) { 
        $db = $Access.CurrentDb()
        return $db.CreateQueryDef( $QueryName, $SQL )
    } else {
        return $db.CreateQueryDef( $QueryName )
    }
}



# Close MS Access query
# office vba documentation: https://docs.microsoft.com/en-us/office/vba/api/access.docmd.close

function Close-MsAccessQuery
{
    param (
        [Parameter(Mandatory = $True)]  [__ComObject] $Access,
        [Parameter(Mandatory = $True)]  [string] $QueryName,
        [Parameter(Mandatory = $False)] [switch] $Save
    )

    $DoCmd = $Access.DoCmd

    if( $Save ) { 
        $DoCmd.close(1, $QueryName, 1)
    } else {
        $DoCmd.close(1, $QueryName, 2)
    }
}



# Open a query in MS Access
# note that if this runs an existing query and includes a sql argument, the existing query will be saved-over
# office vba documentation: https://docs.microsoft.com/en-us/office/vba/api/access.docmd.openquery

function Open-MsAccessQuery
{
    param (
        [Parameter(Mandatory = $True)]  [__ComObject] $Access,
        [Parameter(Mandatory = $True)]  [string] $QueryName,
        [Parameter(Mandatory = $False)] [string] $SQL=$null
    )   

    $DoCmd = $access.DoCmd

    # create new query if not exists
    if( !( Test-MsAccessQueryDef $Access $QueryName )) {
        New-MsAccessQuery $Access $QueryName $SQL
    }

    # update query sql if SQL argument is provided 
    if( $SQL ) {
        $db = $Access.CurrentDb()
        $queryDef = $db.QueryDefs.Item($QueryName)
        $queryDef.sql = $SQL

        # save & close query after updating
        Close-MsAccessQuery -Access $Access -QueryName $QueryName -Save
    }

    # open query in current ms access window
    $Access.Visible = $true
    $DoCmd.OpenQuery($QueryName)
}



# Convert MS Access RecordSet (table or query) to Powershell object array having the same field names
# Note that this is very inefficient/slow because each individual row and column must be iterated through, so it is a good idea 
# to only use this with a limited amount of data

function Get-MsAccessData
{
    [OutputType([Object[]])]
    param (
        [Parameter(Mandatory = $True)] [__ComObject] $Access,
        [Parameter(Mandatory = $True)] [ValidateSet('Query','Table')] [string[]]$ObjectType,
        [Parameter(Mandatory = $True)] [string] $Name,
        [Parameter(Mandatory = $False)] [ValidateRange(0,[int]::MaxValue)] [int] $Limit
    )

    $data = @() 
    $db = $access.CurrentDb()
    
    # open recordset from query/table, as applicable
    switch($ObjectType) {
        'Query' {
            $queryDef = $db.QueryDefs.item($Name)
            $recordSet = $queryDef.OpenRecordset()
        }
        'Table' {
            $tableDef = $db.TableDefs.Item($Name)
            $recordSet = $tableDef.OpenRecordset()
        }
    }

    $fieldNames = $recordSet.Fields | Select-Object -ExpandProperty name

    # loop through each row of recordset & convert row to custom object, then add to data array
    $i = 0
    while( !$recordSet.EOF ) { 

        $record = [psCustomObject]@{}

        foreach( $name in $fieldNames ) { 

            $record | Add-Member -MemberType NoteProperty `
                                 -name $name `
                                 -value $recordSet.Fields.item($name).value
        }

        $data += $record
        $recordSet.MoveNext()
        
        # if limit is given exit loop when limit is reached
        if( $Limit -and ++$i -ge $Limit ) { break }
    }

    $recordSet.Close()
    return $data 
}


# ----------------------------------------------------------------------------------------
# Examples:

# open an existing ms access database:
$dbPath = 'C:\path\to\database.accdb'
$access = New-Object -ComObject Access.Application
$access.OpenCurrentDatabase($dbPath)
$access.Visible = $true

# import csv file into a new table
Import-MsAccessCsv -Access $access -Path 'C:\path\to\csv_file.csv' -TableName 'csv_data' -HasFieldNames

# import excel file into a new table 
Import-MsAccessExcel -Access $access -Path 'C:\path\to\excel_file.xlsx' -TableName 'excel_data' -HasFieldNames

# run a query (this creates & saves a new query but also work using an existing query)
Open-MsAccessQuery -Access $access -QueryName 'new_query' -SQL 'SELECT top 20 * FROM excel_data'

# pull  records of table/query data into powershell object arrays (careful, very slow!)
Get-MsAccessData -Access $access -ObjectType Table -Name 'csv_data' -Limit 10 | Out-GridView
Get-MsAccessData -Access $access -ObjectType Query -Name 'new_query' -Limit 20 | Out-GridView

# close access
$access.Quit()

Hi! Any idea how can I use "CodePage" because the code won't work with utf-8 (codepage 65001)? Thanks!

Is this related to the CodePage parameter in the call to DoCmd.TransferText? I actually don't think I've ever used this. I found this StackOverflow question which may be addressing the same thing, though let me know if it's not?

Exactly! Is related to "DoCmd.TransferText". Would be very nice to be able to set CodePage (optional).

Hi there, I'm trying your Import-CSV to Access function, and I'm getting the following error:

The Microsoft Access database engine could not find the object '20.28.5_TEST AC 224.pdf.txt.csv'. Make sure the object exists and that you spell its name and the path name correctly. If '20.28.5_TEST AC 224.pdf.txt.csv' is not a
local object, check your network connection or contact the server administrator.
At line:363 char:5
$DoCmd.TransferText( $transferType, $SpecificationName, $TableNam ...
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CategoryInfo : OperationStopped: (:) [], COMException
FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

Here's my snippet of code:

$dbPath = "C:\Utils\Transaction Log.accdb"
$access = New-Object -ComObject Access.Application
$access.OpenCurrentDatabase($dbPath)
$access.Visible = $false

function Import-MsAccessCsv
{
    param ( 
        [Parameter(Mandatory = $True)]  [__ComObject] $Access,
        [Parameter(Mandatory = $True)]  [string] $Path,
        [Parameter(Mandatory = $True)]  [string] $TableName,
        [Parameter(Mandatory = $False)] [switch] $HasFieldNames,
        [Parameter(Mandatory = $False)] [string] $SpecificationName=$null
    )

    $transferType = 0 
    $DoCmd = $Access.DoCmd
    $DoCmd.TransferText($transferType, $SpecificationName, $TableName, $Path, [bool]$HasFieldNames )
}

Get-ChildItem "C:\Utils\temp\" -File -Filter *.csv  |
Foreach-Object {
	$input = Get-Content $_.FullName
	[string]$csvLoc = $_.FullName
	Import-MsAccessCsv -Access $access -Path $csvLoc -TableName "Completed Request Data" -HasFieldNames
}

Do you happen to have any ideas why it would be unable to locate the CSV file found with the Get-ChildItem cmdlet?

Thanks a lot! This whole git has been very helpful!

The Import-MsAccessCsv function works for me but the data is imported into Access in a single column.
The file I am trying to import has 30 columns separated by semicolons.

Has anyone had the same problem?

I have found the following information on stackoverflow where the use of a schema.ini file is commented but I don't know if it is only for vba because in my case it doesn't work:

https://stackoverflow.com/questions/59765800/docmd-transfertext-where-delimiter-is-semicolon-and-decimal-is-comma