Interacting with Access database

PowerShell Help
1

I am trying to delete records from a table, I can query them fine and Build a table but I don’t know how to remove a Record. I have a GUI built to query the table and to add records, but I can’t figure out how to delete them. I appreciate your help !

$entryDate = get-date -Format G
$inputXML = @"

    
        
        
            
        
        
        
        
        
        
        
            
                
                    
                    
                    
                    
                
            
        
 
 
    

 
"@       
 
$inputXML = $inputXML -replace 'mc:Ignorable="d"','' -replace "x:N",'N'  -replace '^<Win.*', '<Window'
 
 
[void][System.Reflection.Assembly]::LoadWithPartialName('presentationframework')
[xml]$XAML = $inputXML
#Read XAML
 
    $reader=(New-Object System.Xml.XmlNodeReader $xaml) 
  try{$Form=[Windows.Markup.XamlReader]::Load( $reader )}
catch{Write-Host "Unable to load Windows.Markup.XamlReader. Double-check syntax and ensure .net is installed."}
 
#===========================================================================
# Store Form Objects In PowerShell
#===========================================================================
 
$xaml.SelectNodes("//*[@Name]") | %{Set-Variable -Name "WPF$($_.Name)" -Value $Form.FindName($_.Name)}
 
Function Get-FormVariables{
  if ($global:ReadmeDisplay -ne $true){Write-host "If you need to reference this display again, run Get-FormVariables" -ForegroundColor Yellow;$global:ReadmeDisplay=$true}
  write-host "Found the following interactable elements from our form" -ForegroundColor Cyan
  get-variable WPF*
}
 
#Get-FormVariables
 
#===========================================================================
#  make the objects work
#===========================================================================
 
 
 
Function TQue{ 
  $truckque = @()


  $adOpenStatic = 3
  $adLockOptimistic = 3

  $objConnection = New-Object -comobject ADODB.Connection
  $objRecordset = New-Object -comobject ADODB.Recordset

  $objConnection.Open("Provider=Microsoft.ACE.OLEDB.12.0; Data Source = c:\Database\gen3data.mdb")
  $objRecordset.Open("Select * from QueueTrucks", $objConnection,$adOpenStatic,$adLockOptimistic)

  $objRecordset.MoveFirst()

  do  {   $a = $objRecordset.Fields.Item("Truck_ID").Value
        $b = $objRecordset.Fields.Item("Job_ID").Value
        $c = $objRecordset.Fields.Item("Amount").Value
        $d = $objRecordset.Fields.Item("LastModifieddate").Value
        $truckq    = [PSCustomobject]@{
                                      "Truck ID"          = $a
                                      "Job ID"            = $b
                                      "Amount"            = $c
                                      "date of entry"     = $d
                                      }
        $truckque += $truckq

    $objRecordset.MoveNext() } 
  until ($objRecordset.EOF -eq $True)

  $objRecordset.Close()
  $objConnection.Close()

  $truckque | Select-Object @{Name='Truck ID';Ex={$_."Truck ID"}},
                                  @{Name='Job ID';Ex={$_."Job ID"}},
                                  @{Name='Amount';Ex={$_."Amount"}},
                                  @{Name='Date of Entry';Ex={$_."Date of Entry"}}
}

Function AddQue {
param($truckid,$jobid,$amt,$proarea)


  $adOpenStatic = 3
  $adLockOptimistic = 3

  $objConnection = New-Object -comobject ADODB.Connection
  $objRecordset = New-Object -comobject ADODB.Recordset

  $objConnection.Open("Provider=Microsoft.ACE.OLEDB.12.0; Data Source = c:\Database\Gen3Data.mdb")
  $objRecordset.Open("Select * from QueueTrucks", $objConnection,$adOpenStatic,$adLockOptimistic)

  $objRecordset.MoveLast()
  $objRecordset.AddNew()
  $objRecordset.Fields.Item("Truck_ID").Value          = $truckid
  $objRecordset.Fields.Item("Job_ID").Value            = $JobID
  $objRecordset.Fields.Item("Amount").Value = $amt
  $objRecordset.Fields.Item("LastModifieddate").Value = Get-Date -Format G
  $objRecordset.Fields.Item("ProcessAreaType").Value = $proarea
  $objRecordset.Fields.Item("Area_ID").Value = 0
  $objRecordset.Fields.Item("Carrier_ID").Value = 0
  $objRecordset.Update()
  $objRecordset.Close()
  $objConnection.Close()

}

Function RemoveQue {
  $adOpenStatic = 3
  $adLockOptimistic = 3

  $objConnection = New-Object -comobject ADODB.Connection
  $objRecordset = New-Object -comobject ADODB.Recordset

  $objConnection.Open("Provider=Microsoft.ACE.OLEDB.12.0; Data Source = c:\Database\Gen3Data.mdb")
  $objRecordset.Open("Select * from QueueTrucks", $objConnection,$adOpenStatic,$adLockOptimistic)
  
  $objRecordset.
  

  $objRecordset.Close()
  $objConnection.Close()

}
                                                                  

 
$WPFbutton.Add_Click({
    $WPFlistView.Items.Clear()
    start-sleep -Milliseconds 840
    Tque | % {$WPFlistView.AddChild($_)}
})

$WPFbutton1.Add_Click({
    $WPFlistView.Items.Clear()
    start-sleep -Milliseconds 840
    AddQue -truckid $WPFtextBox.Text -jobid $WPFtextBox1.Text -amt $WPFtextBox2.Text -proarea $WPFtextBox3.Text
    Tque | % {$WPFlistView.AddChild($_)}
    $web = $truckque | ConvertTo-Html -Fragment
    ConvertTo-Html -Head $head -Body $web | Out-File\\nas\it\test.html
})

#===========================================================================
# Shows the form
#===========================================================================
#write-host "To show the form, run the following" -ForegroundColor Cyan
$Form.ShowDialog() | out-null
2

http://pastebin.com/raw/LuYwZEdn

3

RemoveQue Function is the one I intend to bind to button and a text box

Function AddQue {
param($truckid,$jobid,$amt,$proarea)


  $adOpenStatic = 3
  $adLockOptimistic = 3

  $objConnection = New-Object -comobject ADODB.Connection
  $objRecordset = New-Object -comobject ADODB.Recordset

  $objConnection.Open("Provider=Microsoft.ACE.OLEDB.12.0; Data Source = c:\Database\Gen3Data.mdb")
  $objRecordset.Open("Select * from QueueTrucks", $objConnection,$adOpenStatic,$adLockOptimistic)

  $objRecordset.MoveLast()
  $objRecordset.AddNew()
  $objRecordset.Fields.Item("Truck_ID").Value          = $truckid
  $objRecordset.Fields.Item("Job_ID").Value            = $JobID
  $objRecordset.Fields.Item("Amount").Value = $amt
  $objRecordset.Fields.Item("LastModifieddate").Value = Get-Date -Format G
  $objRecordset.Fields.Item("ProcessAreaType").Value = $proarea
  $objRecordset.Fields.Item("Area_ID").Value = 0
  $objRecordset.Fields.Item("Carrier_ID").Value = 0
  $objRecordset.Update()
  $objRecordset.Close()
  $objConnection.Close()

}

Function RemoveQue {
  $adOpenStatic = 3
  $adLockOptimistic = 3

  $objConnection = New-Object -comobject ADODB.Connection
  $objRecordset = New-Object -comobject ADODB.Recordset

  $objConnection.Open("Provider=Microsoft.ACE.OLEDB.12.0; Data Source = c:\Database\Gen3Data.mdb")
  $objRecordset.Open("Select * from QueueTrucks", $objConnection,$adOpenStatic,$adLockOptimistic)
  
  $objRecordset.
  

  $objRecordset.Close()
  $objConnection.Close()

}
4

The ADO Recordset has a delete method that can delete the current record or a group of records. (As in be careful with using with with a record set that is build from a “select * from XXX” query)
For your reading pleasure https://msdn.microsoft.com/en-us/library/ms675261(v=vs.85).aspx

5

Thanks

6

$trknum is assigned from a text box

Function RemoveQue {
param($trknum)
  $adOpenStatic = 3
  $adLockOptimistic = 3

  $objConnection = New-Object -comobject ADODB.Connection
  $objRecordset = New-Object -comobject ADODB.Recordset

  $objConnection.Open("Provider=Microsoft.ACE.OLEDB.12.0; Data Source = c:\Database\Gen3Data.mdb")
  $objRecordset.Open("Select * from QueueTrucks", $objConnection,$adOpenStatic,$adLockOptimistic)
  
  $objRecordset.Move($trknum)
  $objRecordset.Delete()
  $objRecordset.Update()

  $objRecordset.Close()
  $objConnection.Close()

}