Interacting with Access database

This topic contains 5 replies, has 2 voices, and was last updated by Profile photo of 37mm 37mm 4 months, 2 weeks ago.

Viewing 6 posts - 1 through 6 (of 6 total)
  • Author
    Posts
  • #39195
    Profile photo of 37mm
    37mm
    Participant

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

    #39196
    Profile photo of 37mm
    37mm
    Participant
    #39197
    Profile photo of 37mm
    37mm
    Participant

    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()
    
    }
       
    
    #39198
    Profile photo of Jonathan Warnken
    Jonathan Warnken
    Participant

    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

    #39200
    Profile photo of 37mm
    37mm
    Participant

    Thanks

    #39201
    Profile photo of 37mm
    37mm
    Participant

    $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()
    
    }
    
Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic.