Automate Access Integration Tasks from PowerShell

The CData ADO.NET Provider for Access is a standard ADO.NET Provider that make it easy to accomplish data cleansing, normalization, backup, and other integration tasks by enabling real-time and bidirectional access to Access.

ADO.NET Provider

The ADO.NET Provider provides a SQL interface for Access; this tutorial shows how to use the Provider to create, retrieve, update, and delete Access data.

Once you have acquired the necessary connection properties, accessing Access data in PowerShell can be enabled in three steps.

To connect, set the DataSource property to the path to the Access database.

1. Load the provider's assembly:

   
   [Reflection.Assembly]::LoadFile("C:\Program Files\CData\CData ADO.NET Provider for Access\lib\System.Data.CData.Access.dll")
       

2. Connect to Access:

    
   $conn= New-Object System.Data.CData.Access.AccessConnection("DataSource=C:/MyDB.accdb;")
   $conn.Open()
   

3. Instantiate the AccessDataAdapter, execute an SQL query, and output the results:

   
   $sql="SELECT OrderName, Freight from Orders"
   
   $da= New-Object System.Data.CData.Access.AccessDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.ordername $_.freight
   }
     

Update Access Data

$cmd =  New-Object System.Data.CData.Access.AccessCommand("UPDATE Orders SET ShipCity='New York' WHERE Id = @myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.Access.AccessParameter("@myId","10456255-0015501366")))
$cmd.ExecuteNonQuery()

Insert Access Data

$cmd =  New-Object System.Data.CData.Access.AccessCommand("INSERT INTO Orders (ShipCity) VALUES (@myShipCity)", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.Access.AccessParameter("@myShipCity","New York")))
$cmd.ExecuteNonQuery()

Delete Access Data

$cmd =  New-Object System.Data.CData.Access.AccessCommand("DELETE FROM Orders WHERE Id=@myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.Access.AccessParameter("@myId","001d000000YBRseAAH")))
$cmd.ExecuteNonQuery()