使用 powershell 在 MS-Access 中创建查询 [英] Use powershell to create queries in MS-Access

问题描述

我需要从 Microsoft Access DB 中自动提取一些数据.DB 是由第三方提供给我的，因此我无法控制收到 DB 中的内容.我需要使用 Powershell 从数据库中自动提取数据.

I need to automate some data extraction from a Microsoft Access DB. The DB is provided to me by a third party, and so I do not have control over what is in the DB when it is received. I need to use Powershell to automate extraction of data from the DB.

有没有办法使用powershell在accessDB中创建查询?这基本上是我正在寻找的代码的本质:

Is there any way to use powershell to create a query in the accessDB? This is, essentially, the essence of the code I am looking for:

            $l_dbPath = "C:\Path\To\dataBase.accdb"
            $l_accessApp = new-object -com access.application
            $l_accessApp.OpenCurrentDatabase($l_dbPath)

            $l_query = "SELECT SomeTable.SomeField From SomeTable;"
            $l_queryName = "Export_Query"
            $l_accessApp.CurrentDB().CreateQueryDef($l_queryName, $l_query)

            $l_outputFile = "C:\temp\output.csv"
            $e_acExportDelim = 2 #$l_accessApp.Enumerations.AcTextTransferType.acExportDelim #arg.  this does not seem to exist...
            $e_HasFieldNamesYes=-1
            $l_exportSpec = ""
            $l_accessApp.DoCmd.TransferText($e_acExportDelim,$l_exportSpec,$l_queryName,$l_outputFile,$e_HasFieldNamesYes)
            $l_accessApp.CloseCurrentDatabase()

然而，行 $l_accessApp.CurrentDB.CreateQueryDef($l_queryName, $l_query) 失败，因为 $l_accessApp.CurrentDB() 返回 null，而不是当前的数据库.我找不到从 Powershell 访问 CreateQueryDef 的方法.

However, the line $l_accessApp.CurrentDB.CreateQueryDef($l_queryName, $l_query) fails, because $l_accessApp.CurrentDB() returns null, not the current DB. I can find no way to access CreateQueryDef from Powershell.

我查看了以编程方式构建访问查询，通过 MSAccess 2003 [VBA] 中的代码动态创建查询，使用 Powershell 创建访问 2007 查询? 和其他帖子，但没有发现有效.

I have looked at Programmatically Build Access Query, Create a query dynamically through code in MSAccess 2003 [VBA], Use Powershell to create access 2007 Queries?, and other posts, but found nothing that works.

感谢您的帮助.

事实证明，我上面的代码确实有效！一直以来的问题很简单，我没有一台同时安装了 DAO 和 Access 的机器.在不同的机器上测试单独的组件(使用 DAO 和使用 Access)，并获得 IT 支持以给我一台实际安装了 DAO 和 Access 的机器后，上面的代码确实有效.(安装并加载 MS Access，但未安装 DAO，$l_accessApp.CurrentDB() 返回 $null.安装 DAO 后，它返回一个 DBEngine 对象，正如预期的那样.)

It turns out that the code as I had it above does, in fact, work! The issue all along was simply that I did not have a machine on which I had both DAO and Access installed. After testing the separate components (using DAO, and using Access) on different machines, and getting IT support to give me a machine with DAO and Access both actually installed, the above code does in fact work. (With MS Access installed and loaded, but DAO not installed, $l_accessApp.CurrentDB() returns $null. With DAO installed, it returns a DBEngine object, as expected.)

推荐答案

创建查询定义:

$dbe =new-Object -comobject DAO.DBEngine.120
$path="c:\path\to\db.mdb"
$db = $dbe.opendatabase($path)

$l_query = "select table1.* from table1"
$l_queryName="testquery"
$l_outputFile="z:\test.csv"

$db.CreateQueryDef($l_queryName, $l_query)
$db.closedatabase


$l_outputFile = "z:\test.csv"
$e_acExportDelim = 2 
$e_HasFieldNamesYes=-1
$l_exportSpec = ""

$l_accessApp = new-object -com access.application
$l_accessApp.OpenCurrentDatabase($path)
$l_accessApp.DoCmd.TransferText($e_acExportDelim,$l_exportSpec,$l_queryName,$l_outputFile,$e_HasFieldNamesYes)
            $l_accessApp.CloseCurrentDatabase()

$l_accessApp.CloseCurrentDatabase()

这篇关于使用 powershell 在 MS-Access 中创建查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！