reset autonumber field

[kim]

I have a table that holds records for a year, then they
are moved to a history table, and the original table is
cleared for this year's records. The primary key is an
autonumber field, how do I reset this at the end of the
year? As it is, the first record assumes the number after
the last record that was moved.

 

[Tim Ferguson]

The primary key is an
autonumber field, how do I reset this at the end of the
year?

Empty the table and repair/ compact the mdb file.

*** BUT ***

"If you care what value an Autonumber has, then you probably should not be
using an Autonumber"

(c) microsoft.public.access.tablesdbdesign 1990-2004

*** AND ***

There is rarely any reason to junk out a year's worth of data just because
the month has turned to January. There are often advantages to keeping the
same database going: ease of access to past records; reusing data that are
already there; trend analysis; and so on. It can be a simple matter simply
to maintain a RecordYear field and filter on it in order to manipulate this
year's records.


All the best


Tim F

 

[Guest]

Is there a way to repair/ compact the mdb file through VBA
code? I am a temp and need to make this system as self
controlling as possible, as it is, a button is pressed to
do the year end clearance, there are reasons for this
decision. So in that button_click routine I would have to
include the instructions to carry out the repair/compact.

 

[John Vinson]

I have a table that holds records for a year, then they
are moved to a history table, and the original table is
cleared for this year's records. The primary key is an
autonumber field, how do I reset this at the end of the
year? As it is, the first record assumes the number after
the last record that was moved.

Compact the database.

 

[Tim Ferguson]

Is there a way to repair/ compact the mdb file through VBA
code?

If it only happens once a year, it's probably an admin job.

I am a temp and need to make this system as self
controlling as possible, as it is, a button is pressed to
do the year end clearance, there are reasons for this
decision.

If you really mean that there is nobody about who will be able to manage
this job after you have left, I am not completely sure that you have done
them a favour.

So in that button_click routine I would have to
include the instructions to carry out the repair/compact.

The two choices are

1) use a desktop shortcut, something like

c:\office\msaccess.exe d:\myolddata.mdb /compact d:\mynewdata.mdb

but you'll need to check the options in help as I did that from memory. You
can place this shortcut in the folder with the other files and leave an
instruction for someone to run it at appropriate times. Seriously, it is a
good idea to compact any Jet database regularly anyway.

2) you can use a method of the DBEngine object (DAO code; there should be
an equivalent in ADOX but I don't know what) -- the catch is that you
cannot run it in code in the same database, so you'll have to close the one
you are interested in, open another one and use it to do the end-of-year
stuff. It isn't that hard but you have to keep a clear head about what you
are doing. If you are using a split FE/BE architecture then it's not hard
at all.

As mentioned in prior post, however, I am not sure that there is often a
good reason for taking the approach of getting rid of all existing data
every twelve months.

Best wishes


Tim F