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.
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.
$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