Search code examples
powershellms-accessoledb

How To Execute MS Access Query with OLEDB.12

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

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'

Here is my code snippet:

$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;

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."

Solution

  • 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();