[AccessD] AutoNumbers in historical tables

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



More information about the AccessD mailing list