Created
May 14, 2019 03:46
-
-
Save tniedbala/8b21b9cf08aefacb210cbcd573ddf0b7 to your computer and use it in GitHub Desktop.
Powershell for Microsoft Access
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # 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() |
Author
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:
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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?