SQL 联接查询在 PowerShell 中不起作用

Question

我正在尝试将Python脚本转换为PS脚本。该脚本读取访问数据库文件并运行简单查询以将结果发送到 CSV 文件。查询在Python和MS Access执行时没有问题。

查询：

SELECT
  TABLEA.SITE_CODE,
  TABLEB.REG_TITLE,
  TABLEC.G_ORG_TITLE,
  TABLED.BRD_TITLE,
  TABLEA.SITE_TYPE
FROM ((TABLEA
  LEFT JOIN [TABLEC] ON TABLEA.[G_ORG_ID] = TABLEC.[ID])
  LEFT JOIN TABLEB ON TABLEA.[REGION_ID] = TABLEB.[ID])
  LEFT JOIN TABLED ON TABLEA.[BRAND_ID] = TABLED.[ID]
ORDER BY TABLEA.SITE_CODE

这是我正在使用的代码：

$path = "PathtoFile.mdb"
$QuerySites = "SELECT TABLEA.SITE_CODE, TABLEB.REG_TITLE, TABLEC.G_ORG_TITLE, TABLED.BRD_TITLE, TABLEA.SITE_TYPE FROM ((TABLEA LEFT JOIN [TABLEC] ON TABLEA.[G_ORG_ID] = TABLEC.[ID]) LEFT JOIN TABLEB ON TABLEA.[REGION_ID] = TABLEB.[ID]) LEFT JOIN TABLED ON TABLEA.[BRAND_ID] = TABLED.[ID] ORDER BY TABLEA.SITE_CODE"
$csv = "C:pathOutfile.csv"
$connection = New-Object -TypeName System.Data.OleDb.OleDbConnection
$connection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= $path"
$delimiter = ','
$command2 = $connection.CreateCommand()
$command2.CommandText = $QuerySites
$adapter2 = New-Object -TypeName System.Data.OleDb.OleDbDataAdapter $command2
$dataset2 = New-Object -TypeName System.Data.DataSet
$adapter2.Fill($dataset2)


$connection.Close()

我收到以下错误：

Exception calling "Fill" with "1" argument(s): "IErrorInfo.GetDescription failed with E_FAIL(0x80004005)."
At line:14 char:1
+ $adapter2.Fill($dataset2)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : OleDbException

正如我所说，查询在 Python 脚本中运行没有问题，并且直接从 Access 运行。

我

还没有找到指导我找到根本原因的资源。

Answer 1

将所有表和字段名称括在括号中以确保关键字安全。

SELECT
    [TABLEA].[SITE_CODE],
    [TABLEB].[REG_TITLE],
    [TABLEC].[G_ORG_TITLE],
    [TABLED].[BRD_TITLE],
    [TABLEA].[SITE_TYPE]
FROM (([TABLEA]
    LEFT JOIN [TABLEC] ON [TABLEA].[G_ORG_ID] = [TABLEC].[ID])
    LEFT JOIN [TABLEB] ON [TABLEA].[REGION_ID] = [TABLEB].[ID])
    LEFT JOIN [TABLED] ON [TABLEA].[BRAND_ID] = [TABLED].[ID]
ORDER BY [TABLEA].[SITE_CODE]