Archiving and autonumbers

[Salad]

I'm going to implement an archival database for my app. I have Table1
and Table2. Table2 is a duplicate structure of Table1. I appended
records with "ID between 1 and 100", then "ID between 300 and 400" then
"ID between 225 and 250". It appears they keep the same ID.

Is there anything I need to be concerned about when adding records from
one table to another table with an identical structure when using
autonumbers?

[Allen Browne]

Yes: there is the possiblity of duplicates.

If you delete the record with the ID of (say) 74 - placing it in your
archive table instead - it would be possible later to add another record
that uses the value 74. Typically this is done with an Append query that
specifies the value, but it could also occur if 74 was the last value in the
table, and you compacted after deleting. The problem arises when you try to
delete #74 again later: you can't add it to your archive table, as you
already have a 74 there.

If your database is split, you also run into the problem where executing an
append query resets the AutoNumber's Seed to a value that is too low, so you
can't add any more records. (This is not really a problem about the archive
table.) More info:
Fixing AutoNumbers when Access assigns negatives or duplicates
at:
http://allenbrowne.com/ser-40.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Salad" <oi*@vinegar.comwrote in message
news:13*************@corp.supernews.com...

I'm going to implement an archival database for my app. I have Table1 and
Table2. Table2 is a duplicate structure of Table1. I appended records
with "ID between 1 and 100", then "ID between 300 and 400" then "ID
between 225 and 250". It appears they keep the same ID.

Is there anything I need to be concerned about when adding records from
one table to another table with an identical structure when using
autonumbers?

[Salad]

Allen Browne wrote:

Yes: there is the possiblity of duplicates.

If you delete the record with the ID of (say) 74 - placing it in your
archive table instead - it would be possible later to add another record
that uses the value 74. Typically this is done with an Append query that
specifies the value, but it could also occur if 74 was the last value in
the table, and you compacted after deleting. The problem arises when you
try to delete #74 again later: you can't add it to your archive table,
as you already have a 74 there.

Hi Allen:

I have table Junk1 and Junk2. Junk 1/2 have the same structures. I
added to Junk2 the recs from Junk1 for autonumber 1-1000, 2000-3000,
1200-1300. That worked fine. Then I deleted ID 18 from Junk1 and ran
an append from Junk2 for ID 18 and junk1 got back the original record
OK. If I try to append 18 again into 2, and it now exists, it fails.

I think, since this is an archive, I won't have problems. The recs will
either exist in Junk1 or Junk2 but not in both. I'll append the recs
into Junk2 and delete them from Junk1....then I read below...

If your database is split, you also run into the problem where executing
an append query resets the AutoNumber's Seed to a value that is too low,
so you can't add any more records. (This is not really a problem about
the archive table.) More info:
Fixing AutoNumbers when Access assigns negatives or duplicates
at:
http://allenbrowne.com/ser-40.html

That's good stuff, Allen. Would you recommend running your function on
the Archive database everytime prior to running the archive (yearly)
process? Or should it be run on both current/archives tables?

[Tony Toews [MVP]]

Salad <oi*@vinegar.comwrote:

>I'm going to implement an archival database for my app.

Why archive? Why not leave all the records in the database. And give the users some
means of doing date selections into the records. Or mark inactive customers as
inactive and leave them.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[Allen Browne]

No: don't run the function unless you need it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Salad" <oi*@vinegar.comwrote in message
news:13*************@corp.supernews.com...

Allen Browne wrote:

>If your database is split, you also run into the problem where executing
an append query resets the AutoNumber's Seed to a value that is too low,
so you can't add any more records. (This is not really a problem about
the archive table.) More info:
Fixing AutoNumbers when Access assigns negatives or duplicates
at:
http://allenbrowne.com/ser-40.html

That's good stuff, Allen. Would you recommend running your function on
the Archive database everytime prior to running the archive (yearly)
process? Or should it be run on both current/archives tables?

[Salad]

Tony Toews [MVP] wrote:

Salad <oi*@vinegar.comwrote:

>>I'm going to implement an archival database for my app.

Why archive? Why not leave all the records in the database. And give the users some
means of doing date selections into the records. Or mark inactive customers as
inactive and leave them.

Tony

I've got records going back to 1996. And in my main form I present
counters on various tasks and issues in the company...about 35. So each
time the main form in activated it runs at least 35 SQL Selects and it
takes 3-4 seconds to refresh. I did a sample test of removing records
from 2003 and before and the form snaps to attention. No delay. If it
were simply marking customers inactive I wouldn't bother with archiving.
Also, were it not for the counters (a powerful representation of
what's going on) the need wouldn't be there either.

I doubt the need exists to even view a record prior to 2004. But if
there is the need, the user can simply select Archive and be working
with Archived records.

I've felt, over the years, the fastest a database app will ever be is
when it is brand new. More records, more processing, more delay.

[Tony Toews [MVP]]

Salad <oi*@vinegar.comwrote:

>Why archive? Why not leave all the records in the database. And give the users some
means of doing date selections into the records. Or mark inactive customers as
inactive and leave them.

Tony

I've got records going back to 1996. And in my main form I present
counters on various tasks and issues in the company...about 35. So each
time the main form in activated it runs at least 35 SQL Selects and it
takes 3-4 seconds to refresh.

Do have indexes on all the criteria fields? You likely do as 3 to 4 seconds over a
network is pretty decent for 35 queries.

>I did a sample test of removing records
from 2003 and before and the form snaps to attention. No delay. If it
were simply marking customers inactive I wouldn't bother with archiving.
Also, were it not for the counters (a powerful representation of
what's going on) the need wouldn't be there either.

So why not put an index on the date field of those records and pull in just records
belonging to the current or fiscal year?

>I doubt the need exists to even view a record prior to 2004.

Have you asked?

>But if
there is the need, the user can simply select Archive and be working
with Archived records.

Sure, but archiving is a PITA. As you add new fields and tables to the current MDB
you have to add them to the archive MDB. And you have to update your archiving
logic every time you run the archive just to ensure it's up to date.

>I've felt, over the years, the fastest a database app will ever be is
when it is brand new. More records, more processing, more delay.

Sure but ... <smile>

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/