Author Posts

May 17, 2016 at 8:43 am

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 '^

May 17, 2016 at 8:45 am

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

}
   

May 17, 2016 at 9:23 am

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

May 17, 2016 at 9:32 am

$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()

}