[AccessD] AutoNumbers in historical tables

What about missing AutoNumber values in the records you're appending? What
I'm saying is, you're not really appending the existing AutoNumber value,
you're just allowing the historical table's AN to fill in that value. So,
even though you can reset the AN seed value, how would you accommodate the
"existing" values, whatever they are, as you're appending the remaining
records?

Susan H.


> I was responding to the statement:
>
>      ...Supposing you were required to preserve the Autonumber -- how
would
> you go about appending records as you archive -- you couldn't I'm
> guessing...
>
> And now you say:
>
>      ...I wasn't talking about forcing a new AutoNumber -- I was talking
> about appending records that already have an AutoNumber...
>
> The implication in both cases is:  you can't set a record's autonumber to
an
> arbitrary value.  That is not true.  What you do is force the AN of the
> resulting archive record to the value in the originating table in the
> append.  There is no need to add and delete blank records to force AN
values
> which is the only interpretation I can place on what John said:
>
>     ... if you do a restore you MUST go get the last number, add 1 to it,
> and insert a fake record with that number, then delete that fake record
back
> out...
>
> John's suggestion could work for the situation where you were archiving
> additions to the end of a table and one or more consecutive records ending
> with the last were deleted but the approach reinforces the implication
that
> one can't simply set the value as required.
>
> Good thing this issue is moot but a casual surveyor of the archive may
pick
> up some misconceptions or may be led to believe that list members don't
> understand autonumbers.
>
> If you have a table tblArk with 10 records and a FirstName field and an
> Autonumber field named PK and you need to write a record that has an
> existing AN of 45:
>
> Dim db as Database
> Set db = CurrentDb
> db.Execute("Insert Into tblArk(PK, FirstName) Values(45, 'Joe')")
> MsgBox db.RecordsAffected
>
> If you add and delete a thousand records, you can still run the code above
> and you will have 1 record added provided that the number 45 isn't already
> taken and notwithstanding that the next automatic number would be over
1000.
>   In any case where the AN 45 is not already used, the example code above
> will insert a record and set its autonumber value.   If you have a record
> with an existing autonumber that needs to be changed, you can insert the
> field values into a new record and set the desired autonumber for the
> duplicate and then delete the old record.  While you cannot directly edit
an
> existing AN, you can readily achieve the result.
>
> There is no need to get into the question of why one would want to change
or
> set an autonumber.  I was merely attempting to prevent repetition of a too
> often repeated misconception.
>
> Hen
>
>
> > >
> > > The main point I was making was that I understood Susan to imply that
> >one
> > > can not set the value of an autonumber field to any arbitrary valid
> >value
> > > and that is not accurate.
> >
> >============== I don't remember what I said, but I'm sure that's not what
I
> >meant. :) I wasn't talking about forcing a new AutoNumber -- I was
talking
> >about appending records that already have an AutoNumber...
> >
> >Susan H.
>
> _________________________________________________________________
> Add photos to your e-mail with MSN 8. Get 2 months FREE*.
> http://join.msn.com/?page=features/featuredemail
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>