使用Powershell导出访问数据库的所有查询 [英] Export all queries of an access database with powershell

问题描述

此问题演示了如何使用VBA进行操作，但是我想使用PowerShell进行操作，因此可以对其进行脚本编写和计划.

This question demonstrates how to do it with VBA, but I would like to do it with PowerShell, so it can be scripted and scheduled.

我希望实现应该是完全相同的，因为据称它们都使用相同的COM，但是看来 QueryDefs 在PowerShell访问的COM中未定义，因为它不返回任何内容.

I expected the implementation should be identical as they both allegedly use the same COM but it seems QueryDefs is not defined in the COM that PowerShell accesses as it returns nothing.

# Open file in Access.
$accessCOM = New-Object -ComObject Access.Application
$accessCOM.OpenCurrentDataBase($dbPath, $false)

# get list of queries
$queries = $accessCOM.CurrentDB.QueryDefs # returns nothing.

# export query to file
ForEach ($query in $queries) {
    $name = $query.name
    $sql = $query.sql
    $dest = "$queryDest\$name.sql"
    Write-Output $sql > $dest
}

我可以使用 .CurrentData.AllQueries ，但这会返回AccessObjects的集合，我不知道如何从它们中获取SQL.还有其他方法可以从名称中获取QueryDef或至少是SQL吗?

I can use .CurrentData.AllQueries but that returns a collection of AccessObjects and I don't know how to get the SQL from them. Is there any other way to get the QueryDefs or at least the SQL from the name?

推荐答案

这适用于Access2010.它只是将非系统QueryDefs的名称转储到控制台，但应该可以帮助您开始:

This works for me with Access 2010. It just dumps the names of the non-system QueryDefs to the console, but it should get you started:

$dbe = New-Object -com DAO.DBEngine.120
$db = $dbe.OpenDatabase("C:\Users\Public\Database1.accdb")
$queries = $db.QueryDefs
ForEach ($query in $queries) {
    $name = $query.Name
    If (!$name.StartsWith("~")) {
        $name
    }
}

这篇关于使用Powershell导出访问数据库的所有查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！