Powershell for Microsoft Access
https://gist.github.com/tniedbala/8b21b9cf08aefacb210cbcd573ddf0b7#file-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()
#创建数据库语句
create database if not exists student;
#语句后面最好加上字符编码，实际操作中基本上都是使用utf8字母编码，（但是在控制台中---默认编码是简体中文格式GBK）
create database if not exists student charset=gbk
刷Python小屋的时候连续遇到五六道题关于——求array类型二维数组下元素中数值等于特定值的整数之和，这类的问题，整理一下。传统方法：用for循环来遍历，使用if取特定值但是这样效率比较低，（Python小屋的题目里不让用for循环），所以本文使用array类型的简便方法。原理：#arr为一个array类型的数组
def f(arr):
return arr%3==0
flag=f(a
