Susan Harkins
harkins at iglou.com
Wed Jul 2 07:16:04 CDT 2003
Nevermind, but thank you.
Susan H.
> 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
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>