Restart Autonum in VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table with an AutoNum field. Periodically, the table gets emptied
and reloaded from external data. The AutoNum is in my table but not in the
external data. When I clear out the table, I want the Autonum to restart at
1, but it continues from the last value generated. How do I get it to restart
using ONLY programmatic means (I can do it manuallay, but that's not the
idea).
 
Glen Bodie said:
I have a table with an AutoNum field. Periodically, the table gets emptied
and reloaded from external data. The AutoNum is in my table but not in the
external data. When I clear out the table, I want the Autonum to restart
at
1, but it continues from the last value generated. How do I get it to
restart
using ONLY programmatic means (I can do it manuallay, but that's not the
idea).
Click to expand...


If you are committed to using an autonumber for this field, then your
options are somewhat limited. As you seem to have found out, you need to
delete all records and compact the database to reset the autonumber. This
gets tricky to programmatically compact the database you are working on, but
if the database was split into front end / back end parts it would be easier
to close and compact the back end (hence re-set the autonumber) from a
separate front end. However there would be complications if this were a
multi-user scenario where you could not close and compact the database if
other users were active. You don't say if this is the case.
Another possibility would be to use a non-autonumber field and let the value
get allocated on import - but you don't say how the data is re-loaded and
whether you could modify this.
A third thing to bear in mind is you really need a field to hold a
meaningful sequential number or whether you could have a primary key, but
generate the 1,2,3,... series using sql.
 
Thanks, Eric - kind of confirms my thoughts. The appl is single user but the
front-/back-end design is overkill. My options at the moment:
1 - I could just make it a Long Int and increment my own counter as the data
import proceeds (every record is getting massaged anyway)
2 - I could create a new table containing only the structure of the original
table and insert into that instead of deleting and reloading. I havent tried,
but I bet that new table restarts the counter at 1.
 
Back
Top