如何使用OLEDB.12执行MS Access查询 [英] How To Execute MS Access Query with OLEDB.12

问题描述

我正在寻找使用Microsoft.ACE.OLEDB.12.0命令对象执行MS Access命名查询的语法.

I am looking for the syntax for executing MS Access named query using Microsoft.ACE.OLEDB.12.0 command object.

我看到了很多使用表的示例，但还没有用于查询的示例.将表名替换为查询名似乎不起作用.即从"myquery"中选择*

I see lots of examples using tables but non for queries yet. Swapping out the table name for the query name seems not to work. i.e. select * from 'myquery'

这是我的代码段:

$OleDbConn = New-Object "System.Data.OleDb.OleDbConnection";
$OleDbCmd = New-Object "System.Data.OleDb.OleDbCommand";
$OleDbAdapter = New-Object "System.Data.OleDb.OleDbDataAdapter";
$DataTable = New-Object "System.Data.DataTable";

$OleDbConn.Open();

$OleDbCmd.Connection = $OleDbConn;
$OleDbCmd.CommandText = "'myQuery'"; # name query in MS Access db
$OleDbCmd.CommandType = [System.Data.CommandType]::StoredProcedure;
$OleDbAdapter.SelectCommand = $OleDbCmd;

$RowsReturned = $OleDbAdapter.Fill($DataTable);
Write-Host $RowsReturned;

错误:使用"1"参数调用填充"的异常:"Microsoft Access数据库引擎找不到输入表或查询实验室手册".请确保它存在并且其名称拼写正确."

Error: Exception calling "Fill" with "1" argument(s): "The Microsoft Access database engine cannot find the input table or query ''Lab Manual''. Make sure it exists and that its name is spelled correctly."

推荐答案

诀窍是在查询名称之前附加命令"Execute"，并在查询名称周围使用方括号.

The trick was to append the command 'Execute' before the query name and use square brackets around the query name.

$OleDbConn = New-Object "System.Data.OleDb.OleDbConnection";
$OleDbCmd = New-Object "System.Data.OleDb.OleDbCommand";
$OleDbAdapter = New-Object "System.Data.OleDb.OleDbDataAdapter";
$DataTable = New-Object "System.Data.DataTable";

$OleDbConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\temp\labmanual.mdb;";
$OleDbCmd.Connection = $OleDbConn;
$OleDbCmd.CommandText = "Execute [myQuery]";

$OleDbAdapter.SelectCommand = $OleDbCmd;

$OleDbConn.Open();
$RowsReturned = $OleDbAdapter.Fill($DataTable);


Write-Host $RowsReturned;

$OleDbConn.Close();

这篇关于如何使用OLEDB.12执行MS Access查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！