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?

"I cannot seem to" is a very vague description. Do you get error messages? If so please post them — Mathias R. Jessen
– Mathias R. Jessen, Commented Apr 9, 2017 at 14:25
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. — PowershellPauper
– PowershellPauper, Commented Apr 10, 2017 at 17:34

Ansgar Wiechers · Accepted Answer · 2017-04-09 14:49:51Z

6

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

answered Apr 9, 2017 at 14:49

Ansgar Wiechers

202k27 gold badges286 silver badges363 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

PowershellPauper Over a year ago

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?

Ansgar Wiechers Over a year ago

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

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related