Henry Simpson
hsimpson88 at hotmail.com
Mon Jun 30 13:21:44 CDT 2003
I was addressing the manner in which autonumbers work and the issues and consequences that arise as there appear to be some misapprehensions being published as list lore. I would be content to simply take a full copy of a BE database in most cases. I could imagine that there might be a situation where some malicious employee deletes the last thousand records of a table but can't compact to reset the autonumber because a wise admin stayed logged in 100% of the time when users have access. In such a situation, the next autonumber might be a useful clue indicating how many records may have been lost. I am aware that if you only mark records as deleted and can restrict the user to application interface only access to the data and use a change logging methodology that this is less of an issue. I personally use a mark for deletion flag plus launch an automation admin session that merely locks a single record in a one record table when a first user logs in plus log 100% of all form edits with a date/time/user/tableName/record PK/changed field value in a log file in the before update and before insert events. The log is the basis of a custom field level reconciliation replication system and it works nicely for data security/integrity concerns. In that log, an autonumber is merely a long. Hen >From: Lembit.Soobik at t-online.de (Lembit Soobik) >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 19:00:53 +0200 > >and what is the problem with making the autonumber fields in the archive db >long >integers and then simply copy? >Lembit > >Lembit Soobik > >----- Original Message ----- >From: "Henry Simpson" <hsimpson88 at hotmail.com> >To: <accessd at databaseadvisors.com> >Sent: Monday, June 30, 2003 6:37 PM >Subject: RE: [AccessD] AutoNumbers in historical tables > > > > If a master table had records one thru 10 and records 3, 8 and 10 were > > deleted and it were necessary to duplicate the data for archive purposes >and > > after those deletions it were necessary to record the state that the >next > > autonumber would be 11 at that point in time, insert records 1 - 2, 4 > > through 7 and 9. You'd then have to add and delete a dummy record to >the > > actual table to determine what the next autonumber would be thereby >altering > > the table and then add and delete AN 11 in the archive so that both the > > master and archive table are identical at the time of the backup, with >the > > next record for both to be AN 12. If it were possible to determine the >next > > autonumber prior to compacting the database there would be no need to do >the > > insert to determine its value. Purists might do the insert, compact and > > then insert one below in order to preserve the exact status of the table >so > > both original and archive would yeild 11 at the next insert. > > > > 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. Any attempt to merge multiple tables that >have > > differing field values for identical autonumber records can not be an > > accurate depiction of the state of all the data. It is not possible to > > retain identical table structure and reconcile duplicate autonumber >records > > that have one or more other fields that do not match. > > > > I don't get the point about a fake record with restore. > > > > Hen > > > > >From: "John Colby" <jcolby at colbyconsulting.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 10:54:13 -0400 > > > > > >Remember though that the autonumber will continue from the last >autonumber > > >copied into the table, so 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. At least that is how I handle it. > > > > > >John W. Colby > > >www.colbyconsulting.com > > > > > > > _________________________________________________________________ > > STOP MORE SPAM with the new MSN 8 and get 2 months FREE* > > 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 > > > > >_______________________________________________ >AccessD mailing list >AccessD at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/accessd >Website: http://www.databaseadvisors.com _________________________________________________________________ MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*. http://join.msn.com/?page=features/virus