Code to Produce Results from Access Named Query RRS feed

  • Question

  • Hi,

      I am (re)new to Access database and haven't used it for many years.  I use PowerShell extensively.  I am trying to execute a named query defined in an Access database and capture results into a variable/object.  I cannot seem to find an answer to this.  I would prefer to get an example using a -ComObject access.application object but can use any of the other data objects.  I also may be able to translate another language example.  I think my first problem is knowing what a named query is called.  Thank you for your time and help!!


    I'm thinking the code would be something like:

    $Z = New-Object -ComObject access.application;
    $DS1 = New-Object -TypeName System.Data.DataSet;

    $DS1 = $Z.DoCmd.OpenQuery('queryNameHere');

    This works - but would like an access.application example:

    $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=xxx;";
    $OleDbCmd.Connection = $OleDbConn;
    $OleDbCmd.CommandText = "Execute [NamedQueryHere]";
    $OleDbAdapter.SelectCommand = $OleDbCmd;
    $RowsReturned = $OleDbAdapter.Fill($DataTable);
    Write-Host $RowsReturned;




    • Edited by MikeMez Thursday, August 20, 2020 5:17 PM
    Thursday, August 20, 2020 4:37 PM