Change Autonumber start

 

For some reason the autonumbering has got out of synch.  Maybe you already had data in the table and it wants to start at 1 or you’ve deleted a bunch of data and want to re-use some of the values.

001
002
003
004
005
006
007
008
009
010
011
012
013
014
## reset the Autonumber function to a different
## start value
function Reset-Autonum {
[CmdletBinding(SupportsShouldProcess=$true)]
param (
    [string]$table,
    [string]$column,
[int]$newstart,
    [System.Data.OleDb.OleDbConnection]$connection
)
$sql = "INSERT INTO $table ($column) VALUES ($newstart)"
$cmd = New-Object System.Data.OleDb.OleDbCommand($sql, $connection)
if ($psCmdlet.ShouldProcess("$($connection.DataSource)", "$sql")){$cmd.ExecuteNonQuery()}
}

 

All we need to do is run Reset-Autonum which inserts the new start value into the autonum column. What we get then is a record that just has the ID column filled in. we can then delete that record and any new records will start numbering on from that record. 

 

Import-Module accessfunctions
$db = Open-AccessDatabase -name test03.mdb -path c:\test
Get-AccessData -sql "SELECT * FROM test1" -connection $db
Reset-Autonum -table test1 -column ID -newstart 100 -connection $db

Add-AccessRecord -sql "INSERT INTO test1 (Firstname, LastName, DOB) VALUES ('Minnie', 'HAHA', '26/12/1981')" -connection $db

Remove-AccessData -table test1 -connection $db -filter "ID = 100"

Next time we need to modify the Add-AccessRecord function so that we can pass a list of fields as well as the values

Technorati Tags: ,,

Leave a Reply