Is it possible to update a row in MS Access from PowerShell?

Question

I have the following PowerShell code which adds a new line of data into a MS Access database (based on user input) and works perfectly.

if ($NewAccounts ="Y") {
  $cursor = 3
  $lock = 3

  $Ado = New-Object -ComObject ADODB.Connection 
  $recordset = New-Object -ComObject ADODB.Recordset

  $Ado.Open("Provider = Microsoft.ACE.OLEDB.12.0;Data Source=$Source")

  $query = "Select * from [Sheet1]"

  $recordset.Open($query, $ado, $cursor, $lock)

  $recordset.AddNew() 

  $recordset.Fields.Item("Account") = $AccName
  $recordset.Fields.Item("Serial") = $CGBSerial
  $recordset.Fields.Item("SAExpiry") = $SAEDate.ToString("dd/MM/yyyy")
  $recordset.Fields.Item("SAValidatedPerson") = $SAPerson
  $recordset.Fields.Item("DataCollection") = $DCRun
  $recordset.Fields.Item("DataCollectionDate") = $DCRunDate
  $recordset.Fields.Item("DataCollectionPerson") = $DCPerson
  $recordset.Fields.Item("Version") = $Version
  $recordset.Fields.Item("VersionDateValidated") = Get-Date -Format d
  $recordset.Fields.Item("VersionValidatedPerson") = $logontrim

  $recordset.Update()

  $recordset.Close() 
  $ado.Close()
}

However, I cannot seem to update a row in the database that already exists. Is it possible to update a row, rather than creating an entirely new row?

Stow

"I cannot seem to" is a very vague description. Do you get error messages? If so please post them – Jacquetta

I should have specified that I could not work out the syntax at all - it was more about understanding how the command should be overall. – Stow

Answer 1

$recordset.AddNew() appends a new empty record to the recordset. To update an existing record you need to navigate to the record you want to modify first, and then change the values of that record.

$recordset.Open($query, $ado, $cursor, $lock)

while ($recordset.Fields.Item('Account').Value -ne $AccName) {
  $recordset.MoveNext()
}

$recordset.Fields.Item('Serial') = $CGBSerial
...

$recordset.Update()
$recordset.Close()

However, you can't use MoveNext() with a static cursor, so you need to change the cursor type to adOpenForwardOnly ($cursor = 0).

Alternatively you could use a prepared statement:

$cn = New-Object -ComObject 'ADODB.Connection'
$cn.ConnectionString = "..."
$cn.Open()

$cmd = New-Object -ComObject 'ADODB.Command'
$cmd.CommandText = 'UPDATE [Sheet1] SET Serial=?, SAExpiry=?, ... WHERE Account=?'
$cmd.Parameters.Append($cmd.CreateParameter('@p1', 200, 1, 50, $CGBSerial))
$cmd.Parameters.Append($cmd.CreateParameter('@p2', 7, 1, $null, $SAEDate))
...

$cmd.Execute()

$cn.Close()

Eruptive

That works absolutely perfectly - I used your first example with $recordset.Open... Thank you for such a rapid response. would there be an easy way to delete a record using the same method? – Stow

@Stow Please check the documentation. $recordset.Delete(1) might work, but I haven't tested it. – Eruptive