Categories

Add Access Record PtII

We have seen how to add a record to an Access table by passing in the whole SQL string.  This is OK when we want to add a single record or possibly not fill all fields in a row.

001
002
003
004
005
006
007
008
009
010
011
012
function Add-AccessRecord {
param (
    [string]$sql,
    [System.Data.OleDb.OleDbConnection]$connection,
    [string]$table,
    [string]$values
)
    $sql = "INSERT INTO $table VALUES ($values)"
   
    $cmd = New-Object System.Data.OleDb.OleDbCommand($sql, $connection)
    $cmd.ExecuteNonQuery()
}

 

If we alter our function to create the SQL as shown we can pass the table name and values into the function. 

Add-AccessRecord -connection $db -table test1 -values '"John","Smith","01/07/1981"'

This will add a record and start applying the values in sequence to the row.  it assumes that values are supplied for all fields.

At the moment the function effectively breaks our previous version because $sql is redefined in the function.  We need to test whether we should create a SQL record or use the SQL statement supplied. We could check if the $sql parameter is given a value but a better way would be to use parameter sets which we will look at after we have learned to read data from a table.

Leave a Reply