[AccessD] AutoNumbers in historical tables

Henry Simpson hsimpson88 at hotmail.com
Tue Jul 1 23:28:44 CDT 2003


Susan:  I can't make head or tail out of your last post and John hasn't 
offered any clarification on his earlier comments.  Arthur's comment about a 
confusion between OLTP and OLAP systems may have some bearing on the why of 
autonumbers in archiving but has no bearing on how autonumbers may be 
manipulated which is the matter I was addressing.

What is it you mean by not really appending the existing Autonumber value 
being different from allowing the historical AN to fill in that value?  
Where is it that 'missing' autonumbers are an issue and I'm not sure what 
you mean about existing values.  Clearly you cannot have duplicate values in 
an autonumber field but you can insert records from any table into an 
archive table in any order you wish and in doing so set the archive 
autonumber to any value that is valid in the source table.  I was not 
talking about setting any seed value.  The previous example posted involved 
setting a table record with two fields, one of which was an autonumber, and 
inserting that record into another table at any arbitrary value without 
resetting any seed value.  I'll try another example that assumes any table 
of records with an autonumber field and a table with duplicate structure but 
no records.  You can add an Order By clause for any arbitrary field and the 
table will fill in any autonumber sequence that that field sort is and all 
the records identified by an autonumber in the original table will be 
identically identified in the archive table.  Just substitute any table 
names to try this out:

Sub archiveTable()
Dim rstSrc As Recordset
Dim rstDst As Recordset
Dim db As Database
Dim lngI As Long
Dim lngCount As Long

Set db = CurrentDb
Set rstSrc = db.OpenRecordset("tblCompany")
Set rstDst = db.OpenRecordset("tblCompanyArchive")
lngCount = rstSrc.Fields.Count - 1
Do While Not rstSrc.EOF
    rstDst.AddNew
    For lngI = 0 To lngCount
        rstDst(lngI) = rstSrc(lngI)
    Next
    rstDst.Update
    rstSrc.MoveNext
Loop
rstSrc.Close
rstDst.Close
Set rstSrc = Nothing
Set rstDst = Nothing
Set db = Nothing
End Sub

Hen

>From: "Susan Harkins" <harkins at iglou.com>
>Reply-To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>To: "Access Developers discussion and problem 
>solving"<accessd at databaseadvisors.com>
>Subject: Re: [AccessD] AutoNumbers in historical tables
>Date: Mon, 30 Jun 2003 17:43:05 -0400
>
>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
> >
>
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com

_________________________________________________________________
Tired of spam? Get advanced junk mail protection with MSN 8.  
http://join.msn.com/?page=features/junkmail



More information about the AccessD mailing list