Arthur Fuller
artful at rogers.com
Mon Jun 30 19:14:30 CDT 2003
If I follow this thread correctly, I detect a confusion between OLTP and OLAP systems. In a sense, the difference lies in whether you can insert rows under normal (i.e. interactive) circumstances. In the latter, the answer is No. Rows are inserted only programmatically. Therefore there is not only no need for autonumbers, but they intrude upon the design. IMO there should be no occurrences of autonumber in an OLAP database. On the other hand, if you're talking about backup/restore systems rather than archives, you have to preserve the autonumbers, but it's no problem because you're snapshotting the entire database, not its individual rows. At current prices, virtually any company can afford a CD burner, and few Access DBs are > 650MB, so it's a dollar a day to burn CD backups. My $.02, Arthur -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Henry Simpson Sent: June 30, 2003 12:38 PM To: accessd at databaseadvisors.com 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