How to reset the autonumber

[Danny]

I have a db that has a table that is never deleted but just has records
cleaned out

"delete * from table"

but each time it is populated, the autonumber is not reset to 0 and picks up
where it left off even though I delete the records.

Is there a way in code i can compact the database (I know this is the way to
reset that blank tables autonumber to 0). I know I can compacdt on exit but
the table will always be populated at this point and must stay that way for
the user puprposes.

Thanks in advance

[David Schofield]

On Sat, 02 Oct 2004 13:12:02 GMT, "Danny" <da********@hotmail.com>
wrote:

I have a db that has a table that is never deleted but just has records
cleaned out

"delete * from table"

but each time it is populated, the autonumber is not reset to 0 and picks up
where it left off even though I delete the records.

Is there a way in code i can compact the database (I know this is the way to
reset that blank tables autonumber to 0). I know I can compacdt on exit but
the table will always be populated at this point and must stay that way for
the user puprposes.

Thanks in advance

Hi
If the table has no external relationships you can just delete it and
replace it with a new empty version, either by code or by copying a
template table. This can be a lot quicker than deleting a large number
of records if the table is indexed. However most tables should have
some relationships!
David

[Allen Browne]

If you want to reset the autonumber without compacting, you will need to set
a reference to the ADOX library. You can then set the Seed property of the
Column in the Table.

There is a rather involved example of setting the Seed (for a different
purpose) in this link:
http://members.iinet.net.au/~allenbrowne/ser-40.html

Note that this approach works only in JET 4 (Access 2000 and later).

--
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.

"Danny" <da********@hotmail.com> wrote in message
news:CO**********************@news4.srv.hcvlny.cv. net...

I have a db that has a table that is never deleted but just has records
cleaned out

"delete * from table"

but each time it is populated, the autonumber is not reset to 0 and picks
up
where it left off even though I delete the records.

Is there a way in code i can compact the database (I know this is the way
to
reset that blank tables autonumber to 0). I know I can compacdt on exit
but
the table will always be populated at this point and must stay that way
for
the user puprposes.

[Danny]

Thanks for all of your responses. I will take a look at both suggestions.
"Danny" <da********@hotmail.com> wrote in message
news:CO**********************@news4.srv.hcvlny.cv. net...

I have a db that has a table that is never deleted but just has records
cleaned out

"delete * from table"

but each time it is populated, the autonumber is not reset to 0 and picks up where it left off even though I delete the records.

Is there a way in code i can compact the database (I know this is the way to reset that blank tables autonumber to 0). I know I can compacdt on exit but the table will always be populated at this point and must stay that way for the user puprposes.

Thanks in advance

[Jim Allensworth]

On Sat, 02 Oct 2004 13:12:02 GMT, "Danny" <da********@hotmail.com>
wrote:

I have a db that has a table that is never deleted but just has records
cleaned out

"delete * from table"

but each time it is populated, the autonumber is not reset to 0 and picks up
where it left off even though I delete the records.

Is there a way in code i can compact the database (I know this is the way to
reset that blank tables autonumber to 0). I know I can compacdt on exit but
the table will always be populated at this point and must stay that way for
the user puprposes.

Thanks in advance

A few thoughts on this ...

First: Why do you care what the autonumbers are? It is considered best
not to rely on them for any user information - they are there only for
PKs and relationships.

Second: Is the database split? (it really should be) If so then you
could close all connected objects (forms, reports and queries) and
Name it to a different location and then Compact it back to the
original location. e.g.

Dim strPathSrce As String
Dim strPathDest As String

strPathSrce = 'where the backend data is now
strPathDest = 'a temporary location (or backup location)

'Move file to destination
Name strPathSrce As strPathDest
DoEvents
'Compact it back to the original source
DBEngine.CompactDatabase strPathDest, strPathSrce

Third: In Code create a temporary database to hold the temporary table
whenever you need it. No need to compact just destroy and recreate the
temporary database (and table) as needed. Leave the link alone.
- Jim