How do I set up a sequence/generator in Access? 1

[Sonix]

I don't want Access to automatically populate the value of the primary key (numeric) column of a table i.e. use autoincrement columns. Instead I want to call the function (as you would use a sequence in databases like Oracle) directly so that I can set the value of the column manually in my app code. Does anyone know how I can set up a sequence generator of this kind in Access or how I can call the underlying autoincrement function? Also how does Access resolve concurrency issues. i.e. if two records were inserted at the same time? Is Access able to assign two different values to these columns if they were defined as autoincrement?

 

[JimHorton]

You set up a table, say, tblSeq. ID column, which is primaryKey, set = 1, set Validation Rule = 1. This prevents anyone from adding records to this table. Second field is say, LastNumber.

Now wrap a read/write into a function, say, GetNum() as Long. Open Recordset (dbPessimistic) on this, call .edit method--trapping lock errors with a doevents delay/retry in the error trap--then when edit is available, read last number, increment by 1, update. Return the read number + 1.
--Jim

 

[Sonix]

So you have use your own table to do this. Hmm, I thought it might come to that. You sure you can't call the autoincrement operation manually by some API or SQL?

 

[JimHorton]

Sonix,
I'm not aware of any such thing you could 'apply' to your own field, only the autonumber. But if there was, I wouldn't use it. With you're own table you have control--total control.

There are lots of stories of people who had autonumber fields as primary key. That field was foreign key if other tables, with no ref. integrity. Importing, exporting to new tables after corruption or something, now it's a brand new autonumber sequence--but it no longer matches the foriegn key and you cant make it match because you can't update it. Stay away from autonumber if you can. Sequence in Oracle is different because it's not 'tied' to the table--you can update the field all you want, the sequence is just a 'service'--much like my example--that guarantees a unique number.
--Jim