Categories

Access – Autonumbering columns

One thing we often want in our database table is a column that will automatically increment itself when a new record is added.  This makes a good primary key for the table as it is guaranteed to be unique.

In this post http://msmvps.com/blogs/richardsiddaway/archive/2009/12/10/add-a-column-to-an-access-table.aspx I showed how to add columns to an access table.  One thing I missed out was adding an autonumbering column.

its just a few lines of code that we need to add to our existing function.

We add a new parameter

    [parameter(ParameterSetName="autonum")] 
    [string]$autoname,

and we add another line to the switch statement

autonum         {$sql = "ALTER TABLE $table ADD COLUMN $autoname COUNTER(1,1)"}

this will create a field that starts counting at 1 and increments by 1.  These values can be reset.  See the next post

This field is used as follows

$db = Open-AccessDatabase -name test03.mdb -path c:\test
New-AccessColumn -connection $db -table test1 -autoname ID

Technorati Tags: ,,

Leave a Reply