Re: New database squence number & locking info

Tech-Archive recommends: Speed Up your PC by fixing your registry

[posted and mailed]

"JoeO" <jp3BlessNoSpam@xxxxxxxxxxx> wrote in
news:eOsMuHdeFHA.2984@xxxxxxxxxxxxxxxxxxxx:

> Hi Bob
> The original post was... the poster increments the primary key by
> adding 1 to the last primary key when adding a new record. I did
> assume then the primary is non autonumber field.
> He/she was doing
> "Select * from tblX"
> then Movelast
> then increment primary key by 1
> then add new
> He/she was opening the whole table just to a new record.
>
> Then I suggested
> Getting the top1 (sorted by Primary Key and the increment by 1)
> Then add New
>
> Select * where from YourTable where primary < 1 will not work... how
> then will he increment the primary key by adding 1
> Unless of course there is some kind of StoredProcedure that will
> handle incrementing the non autonumber primary key field...
> Please refere to the original post...


Hi Joe,

Yes, I should have read the OP's post but I derived something obviously
erroneous from not reading it; that the primary key was an autonumber.

One of the things this opens is the concept of keys, specifically, what
really is the purpose of a primary key, and should the user ever even
see it. In my opinion, the entire purpose of a "primary key" is for a
very fast look up. As such, the programmer has neither the need nor the
inclination to fool with it. The most flagrant example I can think of is
the typical billing program which uses the invoice number as a primary
key (again, remember I am referring to an ACCESS database) and the
programmer controls it by adding one to the current invoice number.
Which, I guess is fine, if a second user doesn't update his simultaneous
to the first user. To me, the ideal situation is to let ACCESS control
the primary key which frees the OP to control other keys, such as
invoice number.

More importantly, and more perhaps to the OP's question, he can maintain
a separate table containing the last valid key.
.

Relevant Pages

  • Re: Identity Key problem
    ... In lot cases the primary key will be a value that is used just to keep the ... If so I would drop the identity increment and use a control table that ... > where I validate with VBA coding). ... > prevent the the autonumber getting incremented for invalid record entries. ...
    (microsoft.public.access.forms)
  • Re: Reg: auto_increment issue.
    ... >>auto_increment and this field is the primary key to this tabel. ... >>record and insert the next record it increment the value from the deleted ... > as surrogate keys. ... It is entirely possible to have holes in the sequence. ...
    (perl.dbi.users)
  • Re: Form Save
    ... You calculate the new value for this Primary Key in the Before Update event ... they get the same calculation. ... Auto Number ... Increment number ...
    (microsoft.public.access.modulesdaovba)
  • Re: Auto Number
    ... I found a similar problem and made a procedure to increment the primary key ... The field refno is my primary key and needs to be incremented by 1 each time ... > auto number 1, and sequentially up to about 20,000. ... > Mike from Moriches ...
    (comp.databases.ms-access)
  • Re: New database squence number & locking info
    ... The original post was... ... to the last primary key when adding a new record. ... primary is non autonumber field. ... will he increment the primary key by adding 1 ...
    (microsoft.public.vb.general.discussion)